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

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

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

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

Особенности работы LAST_INSERT_ID() и AUTO_INCREMENT в MySQL

Март11

Всем привет.

Причина по которому я написал эту статью — это неожиданное поведение ф-ции LAST_INSERT_ID() при выполнении запроса в котором я в поле с атрибутом AUTO_INCREMENT передавал значение.

LAST_INSERT_ID()

И так имеем таблички

Таблица авторов:

CREATE TABLE `author` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`birthday` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1;

Таблица книг авторов:

CREATE TABLE `books` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_author` INT(10) NULL DEFAULT NULL,
	`book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`),
	INDEX `id_author` (`id_author`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1;

Добавляем данные, обратите внимание на запросы тут :

INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Николай Николаевич', 'Носов', '2008-11-23');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка учится');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка-путешественник');

и тут :

INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (5, 'Артур Конан', 'Дойль', '1859-05-22');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');

Ну разница не только в значениях полей, а тут главная фишка, что при добавлении автора в первой тройке запросов, автоинкремент делал сама БД, а во второй тройке я принудительно указал номер в поле автоикремент, вот что получилось:

mysql> SELECT * FROM author;
+----+-------------------------------------+------------+------------+
| id | name                                | fam        | birthday   |
+----+-------------------------------------+------------+------------+
|  1 | Николай Николаевич               | Носов     | 2008-11-23 |
|  5 | Артур Конан                           | Дойль     | 1859-05-22 |
+----+-------------------------------------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM books;
+----+-----------+-----------------------------------------------+
| id | id_author | book                                          |
+----+-----------+-----------------------------------------------+
|  1 |         1 | Незнайка учится                 |
|  2 |         1 | Незнайка-путешественник |
|  3 |         2 | Затерянный мир                   |
|  4 |         2 | Шерлок Холмс                       |
+----+-----------+-----------------------------------------------+
4 rows in set (0.00 sec)

Как вы заметили, с таблицей авторов все окей, 1 и 5 запись добавились с теми номерами ключей которые указала БД и я соот.
Во второй таблице — книги, все хуже. У книг с ИД 1 и 2 ИД_автора все ок, привязаны к автору с ИД1 в таблице авторов, а вот книги с ИД 3 и 4 привязаны к автору ИД которого в таблице авторов просто нет?!

Почему произошла такая ситуация. Разбираем.

Оказывается эта функция LAST_INSERT_ID() возвращает ИД поля с атрибутом автоинкремент если запись была вставлена успешно и автоинкремент работал!!!! Что у нас и было в первой тройке запросов, а вот во второй тройке запросов увы такого не было, т.к. я указал ИД, а значение 2 взялось, т.к. последний успешный запрос был при добавлении второй книги первого автора!

Какие еще приколы скрывает в себе эта функция, оказывается если вы делаете многократное добавление в рамках одного запроса, то ф-ция LAST_INSERT_ID() выдаст ИД только первой успешно добавленной записи!!! Пруфлинк.

Вот пример

TRUNCATE `author`;
TRUNCATE `books`;
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) 
VALUES (NULL, @lastID, 'Затерянный мир'),
(NULL, @lastID, 'Шерлок Холмс');

Результат

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

А если сделать так

TRUNCATE `author`;
TRUNCATE `books`;
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');
SELECT LAST_INSERT_ID();

Результат

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Век живи — век учись (© Народная мудрость).

В заключении еще решил разобрать поведение атрибута AUTO_INCREMENT.

AUTO_INCREMENT

Это такой атрибут который генерирует порядковый номер к указанному полю. Полей должно быть только с типом integer или float (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT) и имеющий индекс типа (UNIQUE, PRIMARY, KEY). Причем у каждого из типа поля есть свой предел автоинкремента, например для поля TINYINT это значение 127 или 255 для TINYINT UNSIGNED и тд смотрите документацию. Каждая последующая запись будет +1 (по-дефолту) к максимальному числу до добавления в этом поле, наверное не так лучше сказать.
В MySQL есть вспомогательная БД information_schema в которой хранится необходимая информация о всех таблицах всех БД сервера. И вот именно там храниться следующий номер для автоинкремента, т.е. перед добавление записи в таблицу БД не ищет максимальный номер записи, на это ведь будет тратиться время, которое дорого.
В таблице может быть только одно поле которое имеет автоинкремент, и причем это поле не должно иметь дефолтного значения.
Если мы хотим указать какой-то свой номер для поля с автоинкрементом, то мы должны просто передать этому полю значение. Если значение уже есть в таблице, то будет ошибка если тип индекса в поле UNIQUE или PRIMARY, но есть тип индекса KEY то запись спокойно добавится.
Если по каким-то причинам нам надо указывать поле с автоинкрементом в запросе, но мы хотим чтобы этому полю было присвоено значение автоинкремента автоматом, то в это поле надо передать 0 (ноль) или NULL:

INSERT INTO`tablename`VALUE (0);
или
INSERT INTO`tablename`VALUE (NULL);
или
INSERT INTO`tablename`VALUE (NULL), (0); // два запроса на добавления

Если по каким-то причинам вы хотите добавить значение 0 в поля автоинкремента, для этого необходимо прописать в файле-настроке (my.ini / my.cnf) следующую строчку

sql-mode = NO_AUTO_VALUE_ON_ZERO

но делать это не рекомендуется, т.к. если сделать дамп БД и потом развернуть его на другом сервере, где нет такой настройки, то данные будут искажены.

Если по каким-то причинам вам необходимо сделать чтобы следующая запись начиналась с какого-нибудь друго числа, просто напишите команду:

ALTER TABLE `tablename` AUTO_INCREMENT = тут_пишем_нужное_число;

не забывайте об используемом типе поля, чтобы ваше число не вышло за рамки.

А например возникла задача делать автоинкремент не ++1 а через 5, т.е. 1,6,11 т.д. как быть в таком?
Для этого имеются две переменные auto_increment_increment и auto_increment_offset. auto_increment_increment — указывает шаг приращения, а auto_increment_offset указывает стартовую точку.
Для просмотра что у вас используется команда, которая показывает как у вас настроен автоинкремент:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

Поменять можно 2 путями — только на время работы сервера БД (до перезагрузки) и навсегда — смена конфигурационного файла

1 путь : 
SET @@auto_increment_increment=5; SET @@auto_increment_offset=1;

2 путь в файле my.ini / my.cnf пишем строки :
auto_increment_increment=5; 
auto_increment_offset=1;

Более детальней об изменении AUTO_INCREMENT читать тут.

Обнаружил в интернете интересный сайт с подкастами. Советую скачать podcast, подкасты на различные темы.Также советую послушать подкасты на сайте dev.mysql.com по тематике данной статьи.

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

Работа с функцией GROUP_CONCAT()

Июнь15

Всем привет!

Хочу рассказать о работу с функцией GROUP_CONCAT.

Помнится меня на собеседовании в одну компанию спросили, как получить данные при группировке в строку…. ответа я не знал, конечно в тот же вечер я все узнал и понял как это легко))

Недавно мне надо было написать запрос как раз с использованием этой функции, и решил написать об этом статью.

И так, представим две таблицы, author(авторы) и books(книги авторов).

Заполним данные, ниже представлены две эти таблицы:

Таблица авторов:

CREATE TABLE `author` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`birthday` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=3;

Заполняем таблицу:

INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (1, 'Николай Николаевич', 'Носов', '2008-11-23');
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (2, 'Артур Конан', 'Дойль', '1859-05-22');

Таблица книг авторов:

CREATE TABLE `books` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_author` INT(10) NULL DEFAULT NULL,
	`book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`),
	INDEX `id_author` (`id_author`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=8;

Заполняем таблицу:

INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (1, 1, 'Незнайка учится');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (2, 1, 'Незнайка-путешественник');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (3, 1, 'Винтик, Шпунтик и пылесос');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (4, 2, 'Затерянный мир');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (5, 2, 'Шерлок Холмс');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (6, 2, 'Шерлок Холмс');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (7, 2, NULL);

Сделаем выборки :

mysql> select * from author;
+----+-------------------------------------+------------+------------+
| id | name                                | fam        | birthday   |
+----+-------------------------------------+------------+------------+
|  1 | Николай Николаевич                  | Носов      | 2008-11-23 |
|  2 | Артур Конан                         | Дойль      | 1859-05-22 |
+----+-------------------------------------+------------+------------+
2 rows in set (0.00 sec)

mysql> select * from books;
+----+-----------+------------------------------------------------+
| id | id_author | book                                           |
+----+-----------+------------------------------------------------+
|  1 |         1 | Незнайка учится                                |
|  2 |         1 | Незнайка-путешественник                        |
|  3 |         1 | Винтик, Шпунтик и пылесос                      |
|  4 |         2 | Затерянный мир                                 |
|  5 |         2 | Шерлок Холмс                                   |
|  6 |         2 | Шерлок Холмс                                   |
|  7 |         2 | NULL                                           |
+----+-----------+------------------------------------------------+
7 rows in set (0.00 sec)

Обратите внимание, во второй выборке я специально добавил дублирующееся поле (Шерлок Холмс) и также поле с NULL записью, для чего это я сделал объясню позже.

Теперь нам надо выбрать всех авторов и их книги, если я напишу запрос

SELECT a.id, a.name, a.fam,  b.book  as books
FROM `author` a
LEFT JOIN `books` b ON a.id=b.id_author;

В итоге мы получил все книги по всем авторам:

+----+-----------------------+----------+----------------------------+
| id | name                  | fam      | books                      |
+----+-----------------------+----------+----------------------------+
|  1 | Николай Николаевич    | Носов    | Незнайка учится            |
|  1 | Николай Николаевич    | Носов    | Незнайка-путешественник    |
|  1 | Николай Николаевич    | Носов    | Винтик, Шпунтик и пылесос  |
|  2 | Артур Конан           | Дойль    | Затерянный мир             |
|  2 | Артур Конан           | Дойль    | Шерлок Холмс               |
|  2 | Артур Конан           | Дойль    | Шерлок Холмс               |
|  2 | Артур Конан           | Дойль    | NULL                       |
+----+-----------------------+----------+----------------------------+
7 rows in set (0.00 sec)

А мне бы получить записи авторов, т.е. две записи, в нашем случае, и чтобы в поле books были все книги автора…

Это можно сделать применив как раз ф-цию GROUP_CONCAT().
Схема ф-ции:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Для того, чтобы расписать каждую из команд, я напишу пример:

mysql> SELECT a.id, a.name, a.fam, GROUP_CONCAT(DISTINCT b.book ORDER BY b.book ASC SEPARATOR ', ') AS books
    -> FROM `author` a
    -> LEFT JOIN `books` b ON a.id=b.id_author
    -> GROUP BY a.id;
+----+--------------------+-------+---------------------------------------------------------------------+
| id | name               | fam   | books                                                               |
+----+--------------------+-------+---------------------------------------------------------------------+
|  1 | Николай Николаевич | Носов | Винтик, Шпунтик и пылесос, Незнайка учится, Незнайка-путешественник |
|  2 | Артур Конан        | Дойль | Затерянный мир, Шерлок Холмс                                        |
+----+--------------------+-------+---------------------------------------------------------------------+
2 rows in set (0.05 sec)

Как видно после использование этой ф-ции я получил то, что хотел — дву строчки и в поле books все книги автора.

Теперь распишем каждую команду.
Начну с того, что группировке поддается только поле отличные от NULL, т.е. записи с содержимыми типа NULL не будет в поле books.

DISTINCT — позволяет выбрать не повторяющиеся значения, т.е. книга с дублирующим названием будет откинута, для этого я ввел дважды название книги Шерлок Холмс;
ORDER BY col_name ASC | DESC — позволяет сортировать данные;
SEPARATOR — позволяет разделить данные через нужный вам делитель, по-умолчанию это запятая «,» (без кавычек)

Также эта функция относится к групповым функциям, поэтому использование её без оператора GROUP BY не желательно, иначе в нашем примере получим одну строку с первым автором в таблице и всеми книгами которые есть в таблице книг.

Есть еще одно ограничение. Кол-во символов которые будут отображены в сгруппированном поле по-умолчанию 1024, если надо больше, то использую переменную group_concat_max_len устанавливаем своё значение:

синатксис:
SET [GLOBAL | SESSION] group_concat_max_len = val;

пример:
SET group_concat_max_len = 2048;

Всем спасибо, в пользу тебе %username%.

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

Как из 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;

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

Создание csv файла средствами mysql

Февраль13

csv mysql средствами

Есть задача — получить данные из таблицы или результат запроса и сохранить в виде CSV файла. Т.е. получить Файл CSV MySQL средствами.

Содержимое запроса:

<pre>mysql> SELECT* FROM user;
+----+-------+----------------------------------+------------+
| id | login | pass                             | date       |
+----+-------+----------------------------------+------------+
|  1 | maxx  | 8ffc4674e44d1dffafe02b58603a9621 | 2009-01-09 |
|  5 | admin | 651e6a3959b7c09ed603071db34b238a | 2009-01-11 |
|  6 | tzmk  | 651e6a3959b7c09ed603071db34b238a | 2009-01-19 |
+----+-------+----------------------------------+------------+
3 rows in set (0.00 sec)

Оператор SELECT позволяет сохранить результат в файл. Скажу сразу, файл не должен существовать, БД его не переписывает, дабы случайно не переписать нужные системные файлы.

Вот полный запрос для данных действий:

SELECT * INTO OUTFILE 'd:\\test.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n'
FROM user

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

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

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

В итоге получится файл с таким содержимым:

"1";"maxx";"8ffc4674e44d1dffafe02b58603a9621";"2009-01-09"
"5";"admin";"651e6a3959b7c09ed603071db34b238a";"2009-01-11"
"6";"tzmk";"651e6a3959b7c09ed603071db34b238a";"2009-01-19"

Но этого для полноценного CSV файла мало. Необходимо указать название столбцов, это можно сделать так:

SELECT 'id', 'login', 'pass','date'
UNION
(
SELECT * INTO OUTFILE 'd:\\test1.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n'
FROM user
ORDER BY id ASC
)

тогда результирующий файл будет такого содержания:

"id";"login";"pass";"date"
"1";"maxx";"8ffc4674e44d1dffafe02b58603a9621";"2009-01-09"
"5";"admin";"651e6a3959b7c09ed603071db34b238a";"2009-01-11"
"6";"tzmk";"651e6a3959b7c09ed603071db34b238a";"2009-01-19"

Обратите внимание!!! Если Вам необходимо результирующие данные как-то отсортировать, то сортируемый запрос оберните в скобки, иначе MySQL сначала совместит 2 запроса, а потом сделает общую сортировку, вот цитата из документации:

To apply ORDER BY or LIMIT  to an individual SELECT,
place the clause inside the parentheses that enclose the:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Не забываем поддерживать проект кликая на рекламу гугля))

Всем удачи!

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

Дамп таблиц. Операторы BACKUP TABLE и RESTORE TABLE в MySQL

Февраль11
Сольвентный принтер для печати широкого формата.

Ковыряясь в документации по MySQL я наткнулся на два оператора RESTORE TABLE(S) и BACKUP TABLE(S). Буква S на конце говорит, что можно писать и TABLE, и TABLES — это синонимы. Эти операторы позволяют делать копирование файлов данных таблиц. Применяются ТОЛЬКО к таблицам типа MyISAM.

Как же оно работает?

Оператор BACKUP TABLE(S) делает копирование файлов .MYD (данные таблицы) и файл описание структуры таблицы .FRM, файл индексов .MYI не копируется, т.к. может быть построен на основании двух вышеперечисленных файлов.

Синтаксис оператора BACKUP TABLE(S):

BACKUP TABLE tbl_name [, tbl_name] ... TO '/путь/к/резервному/каталогу';

Через запятую перечисляем копируемые таблицы. Путь, куда будет копироваться дамп таблиц, должен существовать, иначе БД вернет ошибку:

LOCK TABLE users READ;
BACKUP TABLE users TO 'd:\\1\\'\G;

конструкция \G — говорит о том, чтобы данные в консоли выводились не по горизонтали, а по вертикали, удобно, если итоговые данные содержат много полей или в поле длинное значение.

*************************** 1. row ***************************
   Table: test.users
      Op: backup
Msg_type: error
Msg_text: Failed copying .frm file (errno: 2)
*************************** 2. row ***************************
   Table: test.users
      Op: backup
Msg_type: Warning
Msg_text: The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead
*************************** 3. row ***************************
   Table: test.users
      Op: backup
Msg_type: Error
Msg_text: Can't create/write to file 'd:\1\users.frm' (Errcode: 2)
*************************** 4. row ***************************
   Table: test.users
      Op: backup
Msg_type: status
Msg_text: Operation failed
4 rows in set (0.00 sec)

Как видно, БД предупреждает, что данные оператор будет опущен в 6-й версии (пока доживем, многие еще юзают 5.0).

Как еще было указано перед создание копии была сделана блокировка таблицы на чтение, оператором:

LOCK TABLE users READ;

делается это для того, чтобы не было возможности менять данные во время копии.

Если все сделано верно, то получаем такое сообщение:

mysql>
LOCK TABLE users READ;
BACKUP TABLE users TO 'd:\\'\G;
*************************** 1. row ***************************
   Table: test.users
      Op: backup
Msg_type: Warning
Msg_text: The syntax 'BACKUP TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead
*************************** 2. row ***************************
   Table: test.users
      Op: backup
Msg_type: status
Msg_text: OK
2 rows in set (0.16 sec)

Копия сделана, теперь её можно восстановить, на помощь придет оператор RESTORE TABLE(S), он имеет следующий синтаксис:

RESTORE TABLE tbl_name [, tbl_name] ... FROM  '/путь/к/резервному/каталогу';

При восстановлении, также необходимо, чтобы путь существовал и указание пути задаётся абсолютным. Еще одной особенностью восстановления является то, что восстанавливаемая таблица должна отсутствовать в БД. Иначе будет ошибка:

mysql> RESTORE TABLE users FROM 'd:\\';
+-------+---------+----------+---------------------------------------------+
| Table | Op      | Msg_type | Msg_text                                    |
+-------+---------+----------+---------------------------------------------+
| users | restore | error    | table exists, will not overwrite on restore |
+-------+---------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

При успешном восстановлении на экране будет такая надпись:

mysql> RESTORE TABLE users FROM 'd:\\'\G;
*************************** 1. row ***************************
   Table: test.users
      Op: restore
Msg_type: Warning
Msg_text: The syntax 'RESTORE TABLE' is deprecated and will be removed in MySQL 6.0. Please use MySQL Administrator (mysqldump, mysql) instead
*************************** 2. row ***************************
   Table: test.users
      Op: restore
Msg_type: status
Msg_text: OK
2 rows in set (0.14 sec)

Восстановление длится дольше, т.к. необходимо построить индексы.

Всем удачи.

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

Название БД и её таблиц (влияние регистра букв)

Январь13
Воспользуйтесь смс рассылкой для Вашего бизнеса.

Всем привет!

Могу поспорить о том, что многие программисты PHP ведут свои локальные разработки на компьютерах под управлением операционной системы семейства Windows (я сам такой, но об этом сильно жалею). И наверное никто и не задумывался написать название БД или её таблиц с большой буквы, сколько я встречал БД все они имеют формат слово_слово, по правилам MySQL они должны начинаться с буквы, могут иметь цифры и знак подчеркивания «_» и прочие знаки. Даже если обратится к документации MySQL, то видно, что и там также применяется подобный формат.

И мало ли кто задумывался о создании таблицы или БД с Большой буквы!!!! А надо было.

Если написать в Windows, что что таблица будет с большой буквы

CREATE TABLE `Test` (
	`id` INT(10) NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
) COLLATE=utf8_general_ci ENGINE=MyISAM ROW_FORMAT=DEFAULT

то в файловой системе, где хранятся данные о таблице, сами файлы имеют такое же написание, как и название таблицы, т.е. НАЗВАНИЕ ТАБЛИЦЫ ВЛИЯЕТ НА НАЗВАНИЕ ФАЙЛА и ессно зависят от РЕГИСТРА. Получается, что при обращении к таблице из ОС Windows

SELECT * FROM `Test`;

или

SELECT * FROM `test`;

ответ будет правильным, нам MySQL возвратит какие-то данные, но не дай Боже написать второй запрос в ОС *nix подобных системах — сразу будет исключение! Догадались чего???? Конечно в ОС *nix создание файлов регистрозависимое, и когда мы в запросе пишем название таблицы отличное от созданного, то не находится такой файл и вылетает ОШИБКА!!!! Иногда приходится часы тратить не понимая какого у меня локально все зашибись, а на серваке по *nix все х…во. Да и вообще, если мы юзаем РНР, то и должны писать под ОС на которой будет выполняться код!!

А вот название полей пофиг какие будут буквы, также как и название операторов.

Изначально под виндой запрещено вводить большие буквы в название таблиц))), а вот иногда ну надо!!! Как же быть??

Воть! Все легко и просто — lower_case_table_names настраиваем и можем делать, что угодно.

Удачного, правильного и чистого подхода.

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

Извлечение из дампа нужной таблицы

Январь4
Большой выбор бытовой техники! Магазин где Вы можете купить холодильник!

Иногда необходимо из большой БД получить данные по одной или несколько таблицам, а не грузит огромные дампы ради одной маленькой таблицы. Как это сделать и пойдет речь в данной статье.

Иногда требуется взять дамп только скелета таблиц, т.е. без данных:

mysqldump -uroot -proot name_of_db -d  > dump.sql

Запись дампа без некоторых таблиц:

mysqldump -uroot -proot name_of_db --ignore-table=name_of_db.table1  --ignore-table=name_of_db.table2 > dump.sql

Перенос определенных таблиц из одной в другую БД:

mysqldump -uroot -proot name_of_db --tables table1 table2 | mysql -uroot -proot another_db

Дамп определенной (нужной) таблицы:

mysqldump -uroot -proot name_of_db --tables  table1 table2  > dump.sql

Я думаю этого набора команд с лихвой хватит для работы.

Если вы не имеете доступ к консоли БД, то тогда воспользуйтесь программами phpMyAdmin или Sypex

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

Dump MySQL, создание резервной копии БД

Декабрь12
Вашему вниманию качественная раскрутка сайта петербург.

Предисловие

Рано или поздно у всех стоит вопрос о создании копии БД. В сети есть очень много разных программ скриптов и прочего, но они хороши тогда, когда БД маленькая, ну как сейчас у нас, но если БД весит 1-2 ГГб, что делать???? Тут путь один, использовать родную командную утилиту mysqldump — тут столько букв, что я решил описать, по моему мнению, значимые параметры.

Синтаксис

В чем её преимущество — она быстрая, с кучей параметров, скорость её работы всегда будет выше, чем у phpmyadmin и им подобным.

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

где
[options] — это опции которые нужны для разных случаев, об этом ниже
db_name — база(ы) данных

Создание дампа

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev > maxnag_dev.sql

где -uroot — это параметр u означает юзер root, название этого юзера.

-ppassword — это параметр p означает пароль пользователя root

maxnag_dev — название БД, которая дампируетса.

> maxnag_dev.sql — знак больше говорит что весь поток дампа будет записан в файл maxnag_dev.sql по пути ~/maxnag, можно прописать любой путь.

Это самый простой способ получения дампа, для большинства случаев он подходит.

Восстановление дампа

maxnag@svn:~/maxnag> mysql -uroot -ppassword maxnag_dev < maxnag_dev.sql
или
maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev < maxnag_dev.sql

параметры теже, команда восстанавливает БД из файла maxnag_dev.sql в БД maxnag_dev. Обратите внимание, что первая команда не выводит на экран содержимое дампа, а вторая выводит (я думаю это тоже регулируется в настройках)

Параметры и примеры

Теперь самое интересное. Это параметры и примеры.

Дамп только «скелета» таблиц

Для получение дампа только скелета таблиц используется параметр —no-data

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword --no-data maxnag_dev > maxnag_dev.sql

Создание дампа нескольких БД

—databases, -B Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword -B maxnag_dev mybloog > twoDB.sql

Создание дампа с исключением некоторых таблиц

—ignore-table=db_name.tbl_name Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.

Создание дампа с выборочными таблицами

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev --tables user user_data > page.sql

Обратите внимание, что сначала идет название таблицы, а потом перечисляться нужные таблицы.

Создание дампа триггеров

—triggers Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user --triggers > user.sql

Кроме таблицы user в дампе содержится еще и триггеры, кусок дампа ниже

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass2` BEFORE INSERT ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass
END */;;

/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass);
END */;;

DELIMITER ;

Создание дампа процедур и ф-ций

—routines, -R — с этим ключем идет их создание.

Создание дампа планировщика заданий

—events, -E с этим ключем идет их создание.

Создание дампа в виде XML

—xml, -X Представляет дамп базы данных в виде XML

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user -X> user.xml

вот часть содержимого

<table_data name="user">
    <row>
	<field name="id">1</field>
	<field name="fam">Нагайченко</field>
	<field name="name">М</field>
	<field name="otch">В</field>
	<field name="pass">5f4dcc3b5aa765d61d8327deb882cf99</field>
	<field name="login">maxnag</field>
    </row>
</table_data>

Создание дампа с разбиением его на части

Ну это нужно, если у вас дамп несколько ГГб и естественно вытащить такой, даже в архиве проблематично, то его можно разбить на части

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | split -b 300K - maxnag.sql

Обратите внимаение, что -b, означает что мы указываем на какой объем следует делить, после этого параметра идет размер, он может быть

SIZE may have a multiplier suffix:<br>
b 512, kB 1000, K 1024, MB 1000*1000, M 1024*1024,<br>
GB 1000*1000*1000, G 1024*1024*1024, and so on for T, P, E, Z, Y.<br>

потом идет ТИРЕ, а не знак больше. И в файловой системе получается файл разбитый по указанному объему, для объединения используется команда линукса cat

Ну и напоследок, создание дампа с архивацией

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | gzip > maxnag.sql.gz

B получаем дамп уже в архиве.

————————

Все параметры можно чередовать. Это наиболее часто используемые мной параметры.

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

Синхронизация БД с помощью триггеров (trigger MySQL)

Декабрь3

На одном из проектов, над которым я работаю по-ночам )), стала необходимость в перестройке БД (она была денормализована), приведение её к нормальным формам, а также переписыванию скриптов. Останавливать работающий проект низя — он коммерческий.

Как же быть? Точнее — необходимо было держать новую БД актуальной, чтобы когда, все будет готово и оттестировано просто сделать переключение на новую БД и радоваться своей работе.

Я принял такое решение, создать копию БД продакшена, поработать с ней, привести к нормализованному виду и потом синхронизировать. Так я и сделал.

Теперь пришло время подумать как же я буду синхронизировать БД старую и новую? Ведь хочется, что бы при добавлении/редактировании/удалении контента в старой БД, такие же действия происходили с ним в новой БД.

На помощь пришли триггеры MySQL, краткий экскурс что это такое я дал на странице своего блог Работа trigger (триггерами) в MySQL.

И так, есть таблица новостей, в ней есть поля:

mysql> DESCRIBE lib_news;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| dt            | int(11)      | NO   |     | 0       |                |
| title         | varchar(250) | NO   |     |         |                |
| descr         | text         | NO   |     | NULL    |                |
| text          | text         | NO   |     | NULL    |                |
| source        | varchar(250) | NO   |     |         |                |
| source_url    | varchar(250) | NO   |     |         |                |
| keywords      | text         | NO   |     | NULL    |                |
| img1          | varchar(20)  | NO   |     |         |                |
| img2          | varchar(20)  | NO   |     |         |                |
| img3          | varchar(20)  | NO   |     |         |                |
| img4          | varchar(20)  | NO   |     |         |                |
| img5          | varchar(20)  | NO   |     |         |                |
| first         | tinyint(1)   | NO   |     | 0       |                |
| fl            | tinyint(1)   | NO   | MUL | 0       |                |
| kol           | int(11)      | NO   |     | 0       |                |
| f_yandex      | tinyint(1)   | NO   |     | 0       |                |
| f_lenta       | tinyint(1)   | YES  |     | 0       |                |
| f_lenta_email | tinyint(1)   | YES  |     | 0       |                |
| comp_tp       | int(11)      | YES  |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+
20 rows in set (0.02 sec)

А вот поля из аналогичной таблицы в новой БД, разница в 2-х полях.

поле дата `dt` в старой это INT(11), в новой TIMESTAMP;
поле ИД компании comp_tp INT(11), в новой id_comp_tp SMALLINT(4);

mysql> DESCRIBE urbanus_lib_news;
+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment |
| id_comp_tp    | smallint(4)  | NO   |     | 0                 |                |
| dt            | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| title         | varchar(250) | YES  |     | NULL              |                |
| descr         | text         | YES  |     | NULL              |                |
| text          | text         | YES  |     | NULL              |                |
| source        | varchar(250) | YES  |     | NULL              |                |
| source_url    | varchar(250) | YES  |     | NULL              |                |
| keywords      | text         | YES  |     | NULL              |                |
| img1          | varchar(20)  | YES  |     | NULL              |                |
| img2          | varchar(20)  | YES  |     | NULL              |                |
| img3          | varchar(20)  | YES  |     | NULL              |                |
| img4          | varchar(20)  | YES  |     | NULL              |                |
| img5          | varchar(20)  | YES  |     | NULL              |                |
| first         | tinyint(1)   | NO   |     | 0                 |                |
| fl            | tinyint(1)   | NO   | MUL | 0                 |                |
| kol           | int(11)      | NO   |     | 0                 |                |
| f_yandex      | tinyint(1)   | NO   |     | 0                 |                |
| f_lenta       | tinyint(1)   | NO   |     | 0                 |                |
| f_lenta_email | tinyint(1)   | NO   |     | 0                 |                |
+---------------+--------------+------+-----+-------------------+----------------+
20 rows in set (0.02 sec)

Получается, что при синхронизации таблиц, необходимо еще и приводить поля к нужному виду.

Ниже привожу триггеры которые и делают синхронизацию.

DROP TRIGGER IF EXISTS `lib_news_ai`;;
CREATE TRIGGER `lib_news_ai` AFTER INSERT ON `lib_news`
FOR EACH ROW
BEGIN
	INSERT INTO `newdb`.`new_lib_news` SET
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`
	ON DUPLICATE KEY UPDATE
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`;
END;;

DROP TRIGGER IF EXISTS `lib_news_au`;;
CREATE TRIGGER `lib_news_au` AFTER UPDATE ON `lib_news`
FOR EACH ROW
BEGIN
	INSERT INTO `newbd`.`new_lib_news` SET
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`
	ON DUPLICATE KEY UPDATE
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`;
END;;

DROP TRIGGER IF EXISTS `lib_news_ad`;;
CREATE TRIGGER `lib_news_ad` AFTER DELETE ON `lib_news`
FOR EACH ROW
BEGIN
	DELETE FROM `newbd`.`new_lib_news` WHERE `id` = OLD.id;
END;;

Как видно из св-в триггеров, любая новая запись в старой БД таблицы новостей будет записана в новую БД с изменениями которые необходимы. При редактировании также происходит обновление записей в 2-х БД. При удалении — удаление, соответственно.

Может возникнуть вопрос, почему я не использую простые запросы INSERT INTO или UPDATE. Ответ прост, вдруг по каким-то загадочным причинам в новой БД будет существовать запись с таким же ИД, который будет добавляться со старой БД, возникнет ошибка и ничего не будет, а используя конструкцию INSERT INTO ….. ON DUPLICATE KEY UPDATE (можно об этом почитать тут) я избегаю ошибки, даже если запись с таким же ИД есть, она будет обновлена. Или наоборот записи в новой БД нет, а в старой сделали исправление записи, следовательно эта запись добавится в новую БД.

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

Всем удачи.

`newdb`.`new_lib_news`

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

 « Older Entries 

Облако тегов

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

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

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

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

Календарь

Ноябрь 2017
Пн Вт Ср Чт Пт Сб Вс
« Июл    
 12345
6789101112
13141516171819
20212223242526
27282930  

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