LOAD DATA INFILE

Синтаксис

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Описание

С высокой скоростью считывает строки из текстового файла в указанную таблицу базы данных. Имя файла должно быть задано как строковая константа.

Файлы записываются на диск с помощью оператора SELECT INTO OUTFILE. Затем вы можете считать файлы обратно в таблицу с помощью оператора LOAD DATA INFILE. Операторы FIELDS и LINES одинаковы в обоих операторах. Эти операторы необязательны, но если оба указаны, то оператор FIELDS должен предшествовать оператору LINES.

Выполнение этого оператора активирует INSERT триггеры.

Для выполнения LOAD DATA INFILE необходимо иметь привилегию FILE. Это делается для предотвращения доступа обычных пользователей к системным файлам. LOAD DATA LOCAL INFILE не имеет этого требования.

Если переменная системы secure_file_priv установлена (по умолчанию она не установлена), загружаемый файл должен присутствовать в указанном каталоге.

Обратите внимание, что файл конфигурации MariaDB systemd по умолчанию ограничивает доступ к /home, /root, и /run/user. См. Настройка доступа к домашним каталогам.

LOAD DATA LOCAL INFILE

При выполнении оператора LOAD DATA INFILE, MariaDB Server пытается прочитать входной файл из своей файловой системы. В отличие от этого, при выполнении оператора LOAD DATA LOCAL INFILE, клиент пытается прочитать входной файл из своей файловой системы и отправляет содержимое входного файла MariaDB Server. Это позволяет загружать файлы из локальной файловой системы клиента в базу данных.

Если вы не хотите разрешать эту операцию (возможно, по соображениям безопасности), можно отключить оператор LOAD DATA LOCAL INFILE на сервере или клиенте.

• Оператор LOAD DATA LOCAL INFILE можно отключить на сервере, установив переменную системы local_infile в значение 0.
• Оператор LOAD DATA LOCAL INFILE можно отключить на клиенте. Если вы используете MariaDB Connector/C, это можно сделать, сбросив флаг возможности CLIENT_LOCAL_FILES с помощью функции mysql_real_connect или сбросив опцию MYSQL_OPT_LOCAL_INFILE с помощью функции mysql_optionsv. Если вы используете другой клиент или библиотеку клиента, см. документацию для вашего конкретного клиента или библиотеки клиента, чтобы определить, как он обрабатывает оператор LOAD DATA LOCAL INFILE.

Если оператор LOAD DATA LOCAL INFILE отключен на сервере или клиенте, и пользователь пытается его выполнить, сервер приведет к ошибке оператора с сообщением об ошибке:

The used command is not allowed with this MariaDB version

Обратите внимание, что не совсем корректно сказать, что версия MariaDB не поддерживает команду. Более точно было бы сказать, что конфигурация MariaDB не поддерживает команду. См. MDEV-20500 для получения дополнительной информации.

Начиная с MariaDB 10.5.2, сообщение об ошибке более точно:

The used command is not allowed because the MariaDB server or client 
  has disabled the local infile capability

REPLACE и IGNORE

Если вы загружаете данные из файла в таблицу, которая уже содержит данные и имеет первичный ключ, могут возникнуть проблемы, когда оператор пытается вставить строку с первичным ключом, который уже существует. В этом случае оператор завершается с ошибкой 1064, защищая данные, уже имеющиеся в таблице. Если вы хотите, чтобы MariaDB перезаписывала дубликаты, используйте ключевое слово REPLACE.

Ключевое слово REPLACE работает как оператор REPLACE. Здесь оператор пытается загрузить данные из файла. Если строка не существует, она добавляется в таблицу. Если строка содержит существующий первичный ключ, данные таблицы заменяются. То есть, в случае конфликта, предполагается, что файл содержит желаемую строку.

Эта операция может привести к снижению скорости загрузки в 20 и более раз, если уже загруженная часть больше, чем емкость пула буферов InnoDB. Это происходит, потому что это вызывает много обращений к пулу буферов.

Используйте ключевое слово IGNORE, когда вы хотите пропустить строки, содержащие конфликтующий первичный ключ. Здесь оператор пытается загрузить данные из файла. Если строка не существует, она добавляется в таблицу. Если строка содержит существующий первичный ключ, запрос на добавление игнорируется, и оператор переходит к следующей строке. То есть, в случае конфликта, предполагается, что таблица содержит желаемую строку.

Наборы символов

При открытии файла оператор пытается прочитать содержимое, используя набор символов по умолчанию, определенный переменной системы character_set_database.

В случаях, когда файл был записан с набором символов, отличным от набора символов по умолчанию, вы можете указать используемый набор символов с помощью оператора CHARACTER SET в операторе. Он игнорирует наборы символов, заданные оператором SET NAMES и переменной системы character_set_client. Установка оператора CHARACTER SET в значение binary означает «без преобразования».

Оператор интерпретирует все поля в файле как имеющие один и тот же набор символов, независимо от типа данных столбца. Для правильной интерпретации содержимого файла необходимо убедиться, что оно было записано с правильным набором символов. Если вы записываете файл данных с помощью mariadb-dump -T или оператора SELECT INTO OUTFILE с клиентом mariadb, убедитесь, что используется опция --default-character-set , чтобы вывод был записан с желаемым набором символов.

При использовании смешанных наборов символов используйте оператор CHARACTER SET как в SELECT INTO OUTFILE, так и в операторе LOAD DATA INFILE для обеспечения правильной интерпретации MariaDB последовательностей экранирования.

Предварительная обработка входных данных

col_name_or_user_var может быть именем столбца или переменной пользователя. В случае переменной оператор SET может использоваться для предварительной обработки значения перед загрузкой в таблицу.

Приоритет и одновременность

В движках хранения, которые выполняют блокировку на уровне таблицы (MyISAM, MEMORY и MERGE), использование ключевого слова LOW_PRIORITY задерживает вставки до тех пор, пока другие клиенты не прекратят чтение из таблицы. В качестве альтернативы, при использовании движка хранения MyISAM, вы можете использовать ключевое слово CONCURRENT для одновременной вставки.

Отчет о прогрессе

Оператор LOAD DATA INFILE поддерживает отчет о прогрессе. Это может быть полезно при работе с длительными операциями. С помощью другого клиента можно выполнить запрос SHOW PROCESSLIST для проверки хода загрузки данных.

Использование mariadb-import

MariaDB поставляется с отдельной утилитой для загрузки данных из файлов: mariadb-import (или mysqlimport до MariaDB 10.5). Она работает, отправляя операторы LOAD DATA INFILE на сервер.

Используя mariadb-import, можно сжать файл с помощью опции --compress для повышения производительности при медленных сетях, если клиент и сервер поддерживают сжатый протокол. Используйте опцию --local для загрузки из локальной файловой системы.

Индексирование

В тех случаях, когда движок хранения поддерживает операторы ALTER TABLE... DISABLE KEYS (MyISAM и Aria), оператор LOAD DATA INFILE автоматически отключает индексы во время выполнения.

Примеры

У вас есть файл с таким содержимым (обратите внимание, что разделителем является ',', а не табуляция, которая является значением по умолчанию):

2,2
3,3
4,4
5,5
6,8

CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY (a));
LOAD DATA LOCAL INFILE 
 '/tmp/loaddata7.dat' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b) SET c=a+b;
SELECT * FROM t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    4 |
|    3 |    3 |    6 |
|    4 |    4 |    8 |
|    5 |    5 |   10 |
|    6 |    8 |   14 |
+------+------+------+

Другой пример, с данными (разделитель - табуляция):

1       a
2       b

Значение первого столбца удваивается перед загрузкой:

LOAD DATA INFILE 'ld.txt' INTO TABLE ld (@i,v) SET i=@i*2;

SELECT * FROM ld;
+------+------+
| i    | v    |
+------+------+
|    2 | a    |
|    4 | b    |
+------+------+

См. также

• Как быстро вставить данные в MariaDB
• Наборы символов и сопоставления
• SELECT ... INTO OUTFILE
• mariadb-import