Mysql关联删除CSV中的相关数据

问题描述：提供一个csv文件，记录的是一些不同数据库的不同表中的共同字段account_id数据，需要在A库的account表中做关联删除

 

解决思路：csv文件中储存的都是account_id，六位纯数字id。可以建立一张临时表，将csv数据导入临时表中，最后做关联删除。

1.备份原表
CREATE table A.account_0220 as select * from A.account;
alter table A.account_0220 comment = 'This is a backup table. Please drop it after 20230320';

2.导入数据到临时表
创建临时表
create table test.transit_tmp(account_id int);

导入数据到临时表
load data local infile '/tmp/0220/202302201526.csv' 
into table test.transit_tmp
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';


查询临时表数据量
select count(*) from transit_tmp;
+----------+
| count(*) |
+----------+
|    10369 |
+----------+
1 row in set (0.003 sec)

3.查询数据是否匹配
查询account表中与tmp关联数据行数,查询到匹配数据与实际csv数据相差一行
MariaDB [(none)]> select count(*) from A.account  where account_id in (select * from test.transit_tmp);
+----------+
| count(*) |
+----------+
|    10368 |
+----------+
1 row in set (42.539 sec)

MariaDB [test]> select * from transit_tmp order by account_id asc limit 1;
+------------+
| account_id |
+------------+
|          0 |
+------------+
1 row in set (0.003 sec)

MariaDB [test]> select * from transit_tmp order by account_id asc limit 2;
+------------+
| account_id |
+------------+
|          0 |
|     261607 |
+------------+
2 rows in set (0.003 sec)

查询到数据不匹配，csv文件中有一行是字段名,load data把account_id这一行当成0插入到了临时表中

 

删除test.transit_tmp表中数据为0的一行

delete from test.transit_tmp where account_id='0';

 

4.清理目标表相关数据

set autocommit=0; 

delete from A.account where account_id in (select * from test.transit_tmp); 

commit;