За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Создать динамический запрос с использованием PREPARE и EXECUTE в MySQL

Январь8
Нужно место в сети? Тогда ваш нужно разработать интернет сайт, милости просим.

Возможно в этой статье я буду выглядеть как Капитан Очевидность, но пофиг, хочу поделиться информацией.

Иногда в процессе кодинга бывает необходимость менять 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%.

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

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Январь 2013
Пн Вт Ср Чт Пт Сб Вс
« Янв   Мар »
 123456
78910111213
14151617181920
21222324252627
28293031  

Сейчас на сайте