MYSQL 批量插入数据之 LOAD DATA INFILE 使用

官方地址: 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 //可以使用文件导入
]