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

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

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

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

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

Январь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

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

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 получаем дамп уже в архиве.

————————

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

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

  

Облако тегов

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

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

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

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

Календарь

Сентябрь 2017
Пн Вт Ср Чт Пт Сб Вс
« Июл    
 123
45678910
11121314151617
18192021222324
252627282930  

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