Изучаем SQL. №3. Импорт данных из текстового файла в MySQL.

Изучаем SQL
Сегодня мы научимся импортировать данные о пользователях из текстового файла в нашу базу данных, которую мы создали в прошлой статье «Изучаем SQL. №2. Создаем базу данных MySQL и наполняем ее из консоли.«. Для этого используе команду INFILE.

По умолчанию эта функция отключена, поэтому ее необходимо включить. В Linux это делается командой в консоли:

$sudo mysql --local-infile=1 -u root

В Windows, чтобы включить INFILE необходимо внести изменения в конфигурацию mysql. Если вы, как и я, используете OpenServer, тогда это делается так:
Переходим по адресу C:\OpenServer\userdata\config и выбираем конфигурационный файл MySQL вашей версии. Я выбираю MySQL-5.5_my.ini.
Открываем и в строке local-infile=0 меняем 0 на 1.
Должно получиться так local-infile=1. Сохраняем.
Перезапускаем сервер и проверяем статус INFILE, для этого в консоли пишем:

>SHOW VARIABLES LIKE «local_infile»;

Ответ будет таким, если вам удалось все сделать правильно:
local_infile

Теперь переходим в корень OpenServer C:\OpenServer и создаем там файл humans.txt. Теперь нам нужно заполнить его вручную, все отступы ставятся табом.

local_infile

Итак, приступаем к работе, выберем нашу базу :
>USE people;

Очистим таблицу human от всех записей, чтобы не путаться потом, для этого пишем команду в консоли:

>TRUNCATE human;

И заполним ее данными из файла:

>LOAD DATA LOCAL INFILE «humans.txt» INTO TABLE human;

Смотрим, что же из этого вышло:

>SELECT * FROM human;

Изучаем SQL. №3. Импорт данных из текстового файла в MySQL.

Теперь, чтобы добавить данные в тот же файл, предварительно удалив предыдущие. Так как все данные из файла добавляются в конец таблицы, а не перезаписывают ее.
Отредактируем наш файл, изменив в нем имена и возраст:
local_infile

И снова загрузим в базу данных:
>LOAD DATA LOCAL INFILE «humans.txt» INTO TABLE human;
>SELECT * FROM human;

Вот какая таблица у вас получилась:
Изучаем SQL. №3. Импорт данных из текстового файла в MySQL.

На сегодня достаточно. Потренируйтесь в создании таблиц и их наполнении. В следующих статьях я более детально опишу механизмы наполнения и выборки из таблиц.

16 идей о “Изучаем SQL. №3. Импорт данных из текстового файла в MySQL.

  1. Maksim

    Ошибка в самой первой команде для Linux. Не «$sudo mysql —local-infile=1 -u root», а «$sudo mysql —local-infile=1 -u root».

    1. Maksim

      Хм, странно. Похоже в комментариях два знака тире не проходят. Перед параметром «local-infile» должны быть два знака тире.

      1. admin Автор поста

        Спасибо, что указали, я сразу и не заметил. Там и было 2 тире. Только редактор wordpress решил, что достаточно и одно. Так, что пришлось вывести эту строку через скрипт.

  2. Wolodymyr

    В Ubuntu кодировка utf-8, насколько понимаю. При добавлении записей в таблицу из текстового файла получаю при просмотре таблицы «крякозябры».
    mysql> SELECT * FROM human;
    +—-+—————————+——+
    | id | name | age |
    +—-+—————————+——+
    | 1 | Сергей | 22 |
    | 2 | Паша | 33 |
    | 3 | Оксана | 20 |
    | 4 | Света | 22 |
    | 5 | Андрей | 19 |
    +—-+—————————+——+
    5 rows in set (0.00 sec)

    Как указать, чтобы нормально отображались данные из таблицы?

  3. Wolodymyr

    Что ни делал — пока не вышло толком просмотреть данные, добавленные кириллицей в таблицу.
    Вот что делал:
    mysql> TRUNCATE human;
    Query OK, 0 rows affected (0.06 sec)

    mysql> LOAD DATA LOCAL INFILE «/home/vladimir/mysql/humans.txt» INTO TABLE human;
    Query OK, 5 rows affected, 5 warnings (0.03 sec)
    Records: 5 Deleted: 0 Skipped: 0 Warnings: 5

    mysql> SELECT * FROM human;
    +—-+—————————+——+
    | id | name | age |
    +—-+—————————+——+
    | 1 | Сергей | 22 |
    | 2 | Паша | 33 |
    | 3 | Оксана | 20 |
    | 4 | Света | 22 |
    | 5 | Андрей | 19 |
    +—-+—————————+——+
    5 rows in set (0.00 sec)

    Проверяю состояние среды по кодировкам:

    mysql> SHOW VARIABLES LIKE ‘char%’;
    +—————————+—————————-+
    | Variable_name | Value |
    +—————————+—————————-+
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    +—————————+—————————-+
    8 rows in set (0.02 sec)

    mysql> SHOW VARIABLES LIKE ‘collation%’;
    +———————-+——————-+
    | Variable_name | Value |
    +———————-+——————-+
    | collation_connection | utf8_general_ci |
    | collation_database | latin1_swedish_ci |
    | collation_server | latin1_swedish_ci |
    +———————-+——————-+
    3 rows in set (0.00 sec)

    Делаю изменения среды для базы данных:

    mysql> ALTER DATABASE vohman CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    Query OK, 1 row affected (0.06 sec)

    mysql> ALTER DATABASE vohman CHARACTER SET utf8 COLLATE utf8_general_ci;
    Query OK, 1 row affected (0.00 sec)

    Соответственно каждой команде выше можно просмотреть изменения:
    mysql> SHOW VARIABLES LIKE ‘collation%’;
    +———————-+——————-+
    | Variable_name | Value |
    +———————-+——————-+
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_unicode_ci | ИЗМЕНЕНО
    | collation_server | latin1_swedish_ci |
    +———————-+——————-+
    3 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘char%’
    -> ;
    +—————————+—————————-+
    | Variable_name | Value |
    +—————————+—————————-+
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 | ИЗМЕНЕНО
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    +—————————+—————————-+
    8 rows in set (0.00 sec)

    Очищаем на всякий случай таблицу, втягиваем данные из файла и смотрим результат:

    mysql> TRUNCATE human;
    Query OK, 0 rows affected (0.08 sec)

    mysql> LOAD DATA LOCAL INFILE «/home/vladimir/mysql/humans.txt» INTO TABLE human;
    Query OK, 5 rows affected, 10 warnings (0.03 sec)
    Records: 5 Deleted: 0 Skipped: 0 Warnings: 10

    mysql> SELECT * FROM human;
    +—-+———+——+
    | id | name | age |
    +—-+———+——+
    | 1 | ?????? | 22 |
    | 2 | ???? | 33 |
    | 3 | ?????? | 20 |
    | 4 | ????? | 22 |
    | 5 | ?????? | 19 |
    +—-+———+——+
    5 rows in set (0.00 sec)

    Как видно, результат изменился, но нужного не добился. Что не так и как добиться нормального отображения кириллических символов??

  4. Wolodymyr

    Добавлю о кодировках: в gnome-terminal так же изменял кодировки с последующей выборкой из таблицы. Результат тот же, разве что «крокозябры» другие появляются.
    Информация в сети очень не конкретная по этому вопросу.
    Можно ли получить хороший ответ с примерами, в том же доходчивом и «для чайников» стиле как и эти лекции? Очень хотелось бы 🙂

  5. Wolodymyr

    Интересно, почему пока я не зайду вот так как Вы показали
    $sudo mysql —local-infile=1 -u root
    я не могу втянуть данные из файла в таблицу? Ошибка без таких вот параметров.
    Хотя, если захожу просто как обычно под рутом, или даже c sudo
    $sudo mysql -u root -p #можно и без sudo
    то имею нормальную картину при проверке
    SHOW VARIABLES LIKE ‘local_infile’;
    ——————-
    local_infile ON
    но данные из текстового файла не втягиваются, ошибка.
    Ну это так, для интереса. Надо например будет в процессе выполнения какой-то задачи импортировать данные из текстовых документов в таблицы, а как же это сделать? Как задать такую команду (скажем, включение/выключение) этого режима, не выходя из консоли самого mysql?

  6. Romaha

    Почему так?!))
    я Ввожу в human.txt
    1 NULL Rety 32
    2 NULL Nyuh 76
    3 NULL Bhji 53
    а в строке вижу
    | id | name | age |
    +—-+——+——+
    | 1 | NULL | 0 |
    | 2 | NULL | 0 |
    | 3 | NULL | 0 |

      1. Romaha

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

          1. Romaha

            Да прикол втом что первый раз как делал точ в точ, то все было ок. Потом вышел, зашел снова, чтобы повторить еще раз. Выбрал базу, очистил и т. Д. И результат три столбца. Повторял неоднократно.

  7. Gosit

    При загрузке файла выдает ERROR 2 (HY000): File ‘humans.txt’ not found (Errcode: 2 — No such file or directory). Способов исправить не нашел. В чем причина?

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

×