LOAD DATA local INFILE 批量导入文件时 mysql 可以使用 “@”过滤某些字段，tidb现在是否支持
LOAD DATA local INFILE 导入文件如何使用“set” 来更新文件中未出现的字段
sql如下：
LOAD DATA LOCAL INFILE #{localPath}
INTO TABLE ctop_kuaishou_report_daily_campaign FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\
’ IGNORE 1 LINES (
campaign_id,
campaign_name,
STATUS,
@ad_scene,
@stat_date
)
SET account_id = #{accountId}
已经上线使用了，文件数据目前为止还未入库！
在 v4.0.8 版本上测试了一下，通过 @ 过滤某些字段可以使用，但是似乎还有一些问题
mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.8
Edition: Community
Git Commit Hash: 66ac9fc31f1733e5eb8d11891ec1b38f9c422817
Git Branch: heads/refs/tags/v4.0.8
UTC Build Time: 2020-10-30 08:21:16
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> create table test_load(id int,name varchar(20),address varchar(20),age int);
Query OK, 0 rows affected (0.16 sec)
mysql> system cat '/Users/shengang/Documents/sgload.txt';
1 zhangsan HangZhou 1
2 lisi BeiJing 2
3 wangwu GuangZhou 3
mysql> load data local infile '/Users/shengang/Documents/sgload.txt' into table test_load(id,@name,address,age) ;
Query OK, 3 rows affected, 3 warnings (0.21 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 3
mysql> show warnings;
+---------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------+
| Warning | 1366 | Incorrect unspecified value: 'HangZhou' for column '' at row 1 |
| Warning | 1366 | Incorrect unspecified value: 'BeiJing' for column '' at row 1 |
| Warning | 1366 | Incorrect unspecified value: 'GuangZhou' for column '' at row 1 |
+---------+------+-----------------------------------------------------------------+
3 rows in set (0.04 sec)
mysql> select * from test_load;
+------+------+----------+------+
| id | name | address | age |
+------+------+----------+------+
| 1 | NULL | zhangsan | 0 |
| 2 | NULL | lisi | 0 |
| 3 | NULL | wangwu | 0 |
+------+------+----------+------+
3 rows in set (0.09 sec)
另外对于 set 更新字段这个，根据官方文档，目前只做了语法兼容，没有实际作用，测试也验证了
mysql> load data local infile '/Users/shengang/Documents/sgload.txt' into table test_load(id,@name) set address='sss',age=5 ;
Query OK, 3 rows affected (0.27 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test_load;
+------+------+---------+------+
| id | name | address | age |
+------+------+---------+------+
| 1 | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL |
+------+------+---------+------+
3 rows in set (0.11 sec)
https://docs.pingcap.com/zh/tidb/stable/sql-statement-load-data#示例
