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

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

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

Как из csv занести данные в mysql

Апрель29
Спонсор статьи: Электронная книга azbooka n516 black - читалка на основе электронных чернил. Экран идентичен обычной бумаге.

Всем привет!

Сегодня я хочу рассказать как можно данные из CSV-файла загрузить в БД MySQL.

Для сего это нужно???? Да разные бывают ситуации, иногда клиенты из экселя дают нам данные и просят их занести в БД, а как это сделать???? Не будем же мы подключать массивные скрипт для обработки этого файла и потом еще наступим на грабли с кодировкой… Есть пути куда проще.

В предыдущей своей статье я рассказывал, как записать данные из БД в CSV-файл.

Имеется содержимое файла

"id";"login";"pass"
"3";"superadmin";"da1c42eb9cec3336fa8e996832fcbc90"
"4";"admin";"21232f297a57a5a743894a0e4a801fc3"
"5";"Fabian_Admin";"60f8ffb1f0d1d30a3d4a5909452da58d"

Первое что делаем, убираем первую строку — аля название поля в БД. Именно тут эта строка не влияет ни на что.
Далее создаем таблицу в БД с соот полями:

CREATE TABLE `user` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`login` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`pass` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

Теперь просто используя запрос:

LOAD DATA INFILE 'd:\\user.csv'
INTO TABLE `user`
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n';

Конструкция LOAD DATA INFILE ‘d:\\user.csv’ говорит о том, помещен файл с данными по указанному пути, путь абсолютный.

Конструкция FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘»‘ ESCAPED BY ‘\r\n’ говорит о том, что каждое поля в файле будут разделены знаком ;, также что каждое поле будет обернуто в знак «, если внутри поле будет соот знак, то БД позаботится об экранирования (ESCAPED BY) таких знаков (правила построения CSV файлов).

Конструкция LINES STARTING BY » TERMINATED BY ‘\r\n’ говорит о том, что каждая строка начинается с указаного занчения в LINES STARTING BY, а заканчивается строка значением в TERMINATED BY, в данном примере, я ничего не присваиваю началу строки, просто написал чтобы был виден общий синтаксис. Т.к. я юзаю Windows, которому в качестве переноса строки необходимо указать \r\n, поэтому я это значение и поставил в TERMINATED BY, если Вы юзаете *nix подобные системы, то там значение конца строки \n

В итоге выполнения запроса в таблицы БД будет следующее:

mysql> select * from user;
+----+--------------+----------------------------------+
| id | login        | pass                             |
+----+--------------+----------------------------------+
|  3 | superadmin   | da1c42eb9cec3336fa8e996832fcbc90 |
|  4 | admin        | 21232f297a57a5a743894a0e4a801fc3 |
|  5 | Fabian_Admin | 60f8ffb1f0d1d30a3d4a5909452da58d |
+----+--------------+----------------------------------+
3 rows in set (0.00 sec)

Пожалуйста пользуйтесь!!! Есть вопросы — через комментарии.
UPD:

Вчера я допустил неточность, сказав что из CSV файла необходимо вручную убрать первую строчку. Её можно убрать с помощью запроса. Строка IGNORE num LINES, гду num это кол-во строк которые надо пропустить.

Вот полный запрос:

LOAD DATA INFILE 'd:\\user.csv'
INTO TABLE `user`
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n'
IGNORE 1 LINES;

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

  

Облако тегов

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

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

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

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

Календарь

Март 2024
Пн Вт Ср Чт Пт Сб Вс
« Июл    
 123
45678910
11121314151617
18192021222324
25262728293031

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