使用mysql对txt文件内容进行差异对比

使用mysql对文件内容进行差异对比
文件内容按行存储到mysql字段，比diff好用，能快速统计出来差异，出报告。

环境

ubuntu22.04
apt install mysql-server -y

准备数据

mysql
create database files;
use files
#基表
create table f202401(id int NOT NULL AUTO_INCREMENT,name varchar(200) not null default ' ',PRIMARY KEY (id));
show global variables like '%secure_file_priv%';
把文件放/var/lib/mysql-files/下面
load data infile '/var/lib/mysql-files/gaojinbo.com.s1.txt' into table f202401 (name);

新表

create table f20240104(id int NOT NULL AUTO_INCREMENT,name varchar(200) not null default ' ',PRIMARY KEY (id));
load data infile '/var/lib/mysql-files/gaojinbo.com.s2.txt' into table f20240104 (name);

统计基表中减少的行

SELECT t1.*
FROM f202401 as t1
LEFT JOIN f20240104 as t2 ON t1.name = t2.name
WHERE t2.name IS NULL;

统计新表中增加的行

SELECT t2.*
FROM f20240104 as t2 
LEFT JOIN f202401 as t1 ON t2.name = t1.name
WHERE t1.name IS NULL;