What Happens When Using LOAD DATA
mysql
database
Publish Date:   2019-12-18
Word Count:   1.4k
Read Times:   5 Min

在之前的文章Files to MySQL中简单介绍了mysqlimport和LOAD DATA的使用，也知道了mysqlimport其实就是使用了LOAD DATA语句。在这篇文章中详细介绍一下LOAD DATA这个语句，来看看当使用这个语句导入数据时到底发生了什么以及一些需要注意的地方。

1. 从Manual开始

在MySQL手册中详细介绍了LOAD DATA这个语句。下面是这个语句的格式：

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_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
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

参数还是挺多的，可以在手册里看到详细的介绍。不管怎么样，可以使用这个语句来导入数据了。

2. 文件放在哪

像之前一样，创建一个测试表和测试文件：one_piece表和one_piece.txt文件。

然后，这个文件放在哪呢？

毕竟现在涉及到两台机器：mysql client机器和server机器。

在手册上写着：

If LOCAL is specified, the file is read by the client program on the client host and sent to the server.

就是说MySQL根据LOCAL这个参数来标识在哪里去找这个文件。如果指定了LOCAL就在客户端上找，否则就在服务器上找。

3. 在server上

首先现在服务器上尝试。先看看MySQL存放数据的地方：

SELECT @@datadir;

结果：

+------------------------+
| @@datadir              |
+------------------------+
| /usr/local/mysql/data/ |
+------------------------+

在我的机器上，目录是这个。

然后把文件放在这个目录下的test目录下，这个test就是对应的数据库。

然后导入数据：

LOAD DATA INFILE 'one_piece.txt' 
INTO TABLE one_piece 
FIELDS TERMINATED BY '\t' 
IGNORE 1 LINES 
(no,name);

结果：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

好的，那看看secure_file_priv是什么：

SELECT @@secure_file_priv;

结果：

+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+

结果是NULL，这意味着不允许MySQL从文件系统里读取文件，当然是为了安全。

可以在启动mysql服务的时候设置这个值来允许mysql读取文件：

/usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--plugin-dir=/usr/local/mysql/lib/plugin \
--log-error=/usr/local/mysql/data/mysqld.local.err \
--pid-file=/usr/local/mysql/data/mysqld.local.pid \
--secure-file-priv=/usr/local/mysql/data

这样查看secure_file_priv就是设置的值了：

+------------------------+
| @@secure_file_priv     |
+------------------------+
| /usr/local/mysql/data/ |
+------------------------+

然后导入数据就可以了：

Query OK, 10 rows affected (0.08 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

MySQL的默认设置是不允许这么操作的，这也可以理解。上面这么操作主要是为了演示从服务器里导入数据的过程，实际不建议这么操作，都是在客户端导入文件到MySQL服务器。

4. 在client上

由于一些原因我们不能在MySQL服务器上导入文件，这个时候需要在客户端上来导入文件了，这也是LOCAL选项的含义。从客户端导入数据到MySQL服务器的过程中涉及到一些步骤，如下图：

简单来说涉及到下面的几个步骤：

  1. 客户端发送COM_QUERY到服务端来执行命令，在这个例子中就是LOAD DATA LOCAL INFILE xxx；
  2. 服务端发送一个特殊的响应OxFB和文件名给客户端，来告诉客户端发送的文件名；
  3. 客户端开始将文件内容发送给服务端；
  4. 客户端发送完文件内容后，发送一个空包来通知服务端文件发文完毕；
  5. 服务端开始执行命令，执行完毕后返回一个结果（OK或者错误）。

可是为什么服务端要把发送的文件名给客户端呢？客户端不知道应该发送哪个文件吗？

是这样的，SQL语句分析起来比较麻烦，所以MySQL把SQL的解析都放在了服务端，服务端解析完了告诉客户端，这样客户端就不需要解析复杂的SQL语句了。

好的，开始使用LOCAL导入数据：

LOAD DATA INFILE 'one_piece.txt' 
LOCAL
INTO TABLE one_piece 
FIELDS TERMINATED BY '\t' 
IGNORE 1 LINES 
(no,name);

结果：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

失败了，不过这个错误信息不好看到底是什么错误。其实这个错误是有关安全的一些设置。

5. 为了安全

在手册上，有关于LOAD DATA的一些安全设置：

The local_infile system variable controls server-side LOCAL capability. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. By default, local_infile is disabled.

这个是MySQL 8.0版本的说明，local_infile默认是不开启的，在之前的版本中都是默认开启的：

SELECT @@local_infile;

结果：

+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+

了解了，设置local_infile=1试试：

SET GLOBAL local_infile=1;

然后重试导入数据。结果：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

怎么还错了呢？

在上面客户端和服务端数据交换的过程中可以知道，这个过程涉及到客户端和服务端两个机器，上面的设置只是设置了服务端允许客户端发送文件，但是客户端也需要允许服务端来读取这个文件：

For the mysql client, local data loading is disabled by default. To disable or enable it explicitly, use the --local-infile=0 or --local-infile[=1\] option.

也就是说，对于mysql客户端，启动的时候需要设置一下--local-infile：

mysql -u root -p --local_infile

然后再试一次。结果：

Query OK, 10 rows affected (0.05 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

终于成功了。

mysql
 Previous
Go Web (Part 1): Handler & ServeMux Go Web (Part 1): Handler & ServeMux
1. Begin使用Go语言的标准库net/http可以快速构建一个简单的web服务。这篇文章从零开始构建一个简单的web服务器，并主要聚焦于处理器Handler和多路复用器ServeMux。 在开始之前，MindMap里一无所有： 2
2019-12-20 programming
go web
Next 
Files to MySQL Files to MySQL
问题：把一个本地的文件（比如.txt或.csv文件）导入到指定的一张MySQL表中。可以使用mysqlimport或者LOAD DATA命令来完成。这篇文章仅仅是简单介绍一下mysqlimport和LOAD DATA的简单用法。关于LOAD
2019-12-17 database
mysql tips
  You Will See...