mysql中 Load data infile 功能

查看功能是否打开

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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’为一条数据插入到对应的字段

?

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

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

?

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

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

?

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

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

?

1	IGNORE number LINES

错误解决：

?

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

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

?

1	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

 

 

 

 

?

1