Создать динамический запрос с использованием PREPARE и EXECUTE в MySQL
Возможно в этой статье я буду выглядеть как Капитан Очевидность, но пофиг, хочу поделиться информацией.
Иногда в процессе кодинга бывает необходимость менять SQL запрос в зависимости от обстоятельств, в php обычно такое происходитс помощью простого изменения содержимого самого запроса и отправляется на выполнение, ждем результат. Что делать, если нам надо сделать хранимую процедуру (stored runtime, procedure) или функцию, в которой запрос меняется в зависимости от условий?! Вот и предлагаю с этим ознакомиться.
Пример я придумал такой.
У нас есть две таблицы country_ru и country_en, где хранятся данные о странах на русском и английском языках соответственно.
CREATE TABLE `country_ru` ( `code` CHAR(2) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', UNIQUE INDEX `code` (`code`) ) COMMENT='страны на русском' COLLATE='utf8_unicode_ci' ENGINE=InnoDB; INSERT INTO `country_ru` (`code`, `name`) VALUES ('ru', 'Российская Федерация'); INSERT INTO `country_ru` (`code`, `name`) VALUES ('ua', 'Украина'); ################################## CREATE TABLE `country_en` ( `code` CHAR(2) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', UNIQUE INDEX `code` (`code`) ) COMMENT='страны на английском' COLLATE='utf8_unicode_ci' ENGINE=InnoDB; INSERT INTO `country_en` (`code`, `name`) VALUES ('ru', 'Russian Federation'); INSERT INTO `country_en` (`code`, `name`) VALUES ('ua', 'Ukraine');
и стоит задача выдавать страны в зависимости от локали. Т.е. подается локаль ru или en и данные должны быть на соответственном языке.
Можно конечно было сделать условие и в зависимости от него выполнять один или второй запрос, что-то типа
DELIMITER // CREATE DEFINER=`root`@`%` PROCEDURE `getCountry`(IN `locale` char(2)) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN IF locale = 'ru' THEN SELECT * FROM country_ru; END IF; IF locale = 'en' THEN SELECT * FROM country_en; END IF; END// DELIMITER ;
Но выглядит как-то не кошерно, а если добавить новая локаль (конечно пример по реализации ужасен, нормализация хромает), то необходимо переписывать процедуру.
Вот тут лучше сделать динамический запрос, который меняет своё тело в зависимости от локали. Если сделать вот так
CONCAT('SELECT * FROM country_', locale);
, будет ошибка, вы даже не сможете сохранить процедуру.
Для решения этой задачи в MySQL есть 3 команды подробнее тут.
PREPARE — подготавливает запрос для выполнения.
EXECUTE — выполняет запрос
DEALLOCATE PREPARE — очищает запрос
Вот так выглядит процедура с этими командами
DELIMITER // CREATE DEFINER=`root`@`%` PROCEDURE `getCountry`(IN `locale` char(2)) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN SET @sql = CONCAT('SELECT * FROM country_', locale); PREPARE getCountrySql FROM @sql; EXECUTE getCountrySql; DEALLOCATE PREPARE getCountrySql; END DELIMITER ;
В теле процедуры просто создаем переменную в которой формируем запрос, далее запрос подготавливаем для выполнения, выполняем, очищаем.
Для вызова используем
CALL getCountry('en'); результат +------+--------------------+ | code | name | +------+--------------------+ | ru | Russian Federation | | ua | Ukraine | +------+--------------------+ 2 rows in set (0.01 sec) и на русском CALL getCountry('ru'); результат +------+-----------------------------------------+ | code | name | +------+-----------------------------------------+ | ru | Российская Федерация | | ua | Украина | +------+-----------------------------------------+ 2 rows in set (0.00 sec)
Вот и все, конечно команды могут быть расширены, об этом подробно написано в мануале.
В помощь %username%.
Статья просмотренна 672350 раз, зашло посетителей 74374