Создать динамический запрос с использованием 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%.

Статья просмотренна 673666 раз, зашло посетителей 74731