官方地址: https://dev.mysql.com/doc/refman/8.0/en/load-data.html
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}] ...]
其中关键词
1、LOW_PRIORITY
添加了此修饰词,执行的 LOAD DATA是延迟,直到没有其他客户端从表中阅读。这个仅影响仅使用表级锁定的存储引擎如MyISAM, MEMORY, MERGE(自己表示没用过…)
2、CONCURRENT
添加此修饰词会对性能有一些影响,即使没有其他线程在同一时间使用该操作的表
3、LOCAL
LOAD DATA 语句读取是从一个文本文件到一个表中。文件可以 从服务器主机或客户端主机读取,具体取决于是否给出LOCAL修饰符,指定在客户端,不指定则在服务器
4、REPLACE | IGNORE
如果插入的数据会导致字段UNIQUE 索引或PRIMARY KEY发生冲突/重复,REPLACE 则先删除旧数据再插入最新的数据,IGNORE则忽略此次操作/不插入数据,如果没有UNIQUE 索引或PRIMARY KEY则REPLACE | IGNORE不起作用会正常插入数据会产生重复数据
5、CHARACTER SET
指定编码集(无法加载使用 UCS2,UTF16,utf16le 或 utf32 字符集),如果文件的编码跟数据库的编码不一致,可能会出现乱码的问题。所以要注意的是,这里指定的是文件的编码集,而不是数据库的编码集
6、其他
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
比如导入的文本数据为:
“导入数据“,1,“1680633743”
“导入数据1”,2,“1680633743”
“导入数据2”,3,“1680633743”
FIELDS TERMINATED BY ‘string’:描述字段间的分隔符,其中string 就是字符逗号(,)
ENCLOSED BY ‘char’ :描述字段值由什么符号包围,其中char就是字符引号(")
LINES TERMINATED BY ‘string’ :描述以什么分隔符为一条数据,其中char就是斜杠(\n),对于在Windows系统上生成的文本文件,可能需要LINES TERMINATED BY “\r\n”,因为Windows程序通常使用两个 字符作为行终止符。一些程序,可能使用 \r作为 行结束符。若要读取此类文件,请使用 ‘\r’
IGNORE number {LINES | ROWS}:用来忽略导入文件前多少行。如:number=2,则忽略导入文件的前2行数据。实际只插入了 **“导入数据2”,“1680633743”**这行数据
7、[(col_name_or_user_var,…)]
有的时候我们不需要给所有的字段都填充值,这个时候就可以指定列名,以()将列名括起来
8、[SET col_name = expr,…]
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (`nickname`,@var1,`create_time`) SET age= @var1+20
可以对字段进行预处理,比如导入的数据库字段顺序为nickname,age,create_time,那么上面实际插入的数据是
“导入数据“,21,“1680633743”
案例:
我是在TP中执行的
$sql ="LOAD DATA LOCAL INFILE 'uploads/excel/1.txt' INTO TABLE ls_users CHARACTER SET utf8
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (`nickname`,@var1,`create_time`) set age =@var1+10";
Db::execute($sql);
很多用法需要去官方文档阅读,此文章只是很简单的进行描述如有不对或不足的地方请您指出,我会第一时间做成改正。
补充:
1、查看该功能是否开启show global variables like 'local_infile';
如果是ON即为开启,如果没有开启,可以通过如下命令进行开启
set global local_infile=1;
2、在使用TP导入时提示LOAD DATA LOCAL INFILE forbidden错误,需要修改config/database.php
文件,增加
'params' => [
PDO::MYSQL_ATTR_LOCAL_INFILE => true //可以使用文件导入
]