Mysql文件对比

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

对比文件差异

-- 安装 MySQL 服务器（在 Ubuntu 22.04 系统中）
-- apt install mysql-server -y

-- 进入 MySQL 命令行
-- mysql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS files;
USE files;

-- 创建基表，用于存储旧文件内容
CREATE TABLE IF NOT EXISTS f202401 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL DEFAULT ' ',
    PRIMARY KEY (id)
);

-- 查看 MySQL 允许加载文件的路径
SHOW GLOBAL VARIABLES LIKE '%secure_file_priv%';

-- 注意：需要将文件放到 /var/lib/mysql-files/ 下面
-- 从文件中加载数据到基表
LOAD DATA INFILE '/var/lib/mysql-files/f202401.txt' INTO TABLE f202401 (name);

-- 创建新表，用于存储新文件内容
CREATE TABLE IF NOT EXISTS f20240104 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL DEFAULT ' ',
    PRIMARY KEY (id)
);

-- 从文件中加载数据到新表
LOAD DATA INFILE '/var/lib/mysql-files/f20240104.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;

-- 还可以统计基表和新表中相同的行
SELECT t1.*
FROM f202401 AS t1
JOIN f20240104 AS t2 ON t1.name = t2.name;

-- 统计基表中减少的行数
SELECT COUNT(*) AS rows_decreased
FROM f202401 AS t1
LEFT JOIN f20240104 AS t2 ON t1.name = t2.name
WHERE t2.name IS NULL;

-- 统计新表中增加的行数
SELECT COUNT(*) AS rows_increased
FROM f20240104 AS t2
LEFT JOIN f202401 AS t1 ON t2.name = t1.name
WHERE t1.name IS NULL;