mysql中 Load data infile 功能

查看功能是否打开

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global local_infile=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

以换行‘\n’为一条数据插入到对应的字段

LOAD DATA LOCAL INFILE 'data_name' INTO TABLE table_name 
FIELDS TERMINATED BY '\n' (`colunm1`,`column2`,`column3`);

　以逗号','为一条数据插入到对应的字段

LOAD DATA LOCAL INFILE 'data_name' INTO TABLE table_name 
FIELDS TERMINATED BY ',' (`colunm1`,`column2`,`column3`);

　以逗号分隔为一个字段的数据，以换行为一条数据，插入批量数据到相应的字段

LOAD DATA LOCAL INFILE 'data_name' INTO TABLE table_name 
FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'  (`colunm1`,`column2`,`column3`);

忽略文件中的前 number 行，通常情况下，我们生成的文件可能有列名，那么要忽略的放在，这儿的值设置为１即可。需要注意的是这里是行的数量，而不是行号。

IGNORE number LINES

错误解决：

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

解决办法：登录数据库时候使用命令

mysql -u root -p --local-infile=1

错误解决：

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

解决办法：

set global local_infile = 1