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 //可以使用文件导入
]
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值