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

Статьи на тему программирования под веб, используя 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%.

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

рубрика: MySQL

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

  1. Avatar
    Геннадий пишет:

    Подскажите, пожалуйста, при параллельном вызове процедуры не будет ли коллизий имени стейтмента из разных сессий, ведь в мануале написано, что подготовленные стейтменты МОГУТ ЖИТЬ ПОСЛЕ ОТРАБОТКИ хранимой процедуры.
    То есть в момент между созданием и удалением стетмента, сможет ли другая сессия также его спокойно создавать и удалять независимо от текущей?


  2. Avatar
    maxnag пишет:

    Добрый вечер! К сожалению таких экспериментов я не ставил (


  3. Avatar
    Анатолий пишет:

    Почему-то ВЕЗДЕ во ВСЕХ приводится использование только SELECT.

    А вот мне в хранимой процедуре нужно использовать
    INSERT … ON DUPLICATE KEY UPDATE

    В принципе ВСЕ нормально работает — запрос запускается, данные вставляются куда нужно.
    НО! Есть одна проблема — как после execute получить affected_rows . Нужно это для того чтобы определить сколько строк вставилось и сколько обновилось.


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

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  

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