表结构相同的表,获取列值不同的列名

表结构相同的表,获取列值不同的列名

背景

现有两个表表结构相同,两个表的数据id是一一对应的,由于一些原因导致了两个表部分列的值不一样,现在想最小化程度的更新,去保证两个表的列数据统一.

解决思路

• 先关联两个表,根据id字段使两张表的数据一一对应,对应的数据做一个伪表
• 利用伪表和switch case 语句完成列值的判断和获取相应列名

基础表结构和SQL

• 基础表结构

create table table1
(
    id   int auto_increment comment '唯一id'
        primary key,
    name varchar(32),
    age  int,
    sex  varchar(8)
); 

create table table2
(
    id   int auto_increment comment '唯一id'
        primary key,
    name varchar(32),
    age  int,
    sex  varchar(8)
); 

• 存储过程初始化数据

delimiter $$
CREATE PROCEDURE GenerateRandomData()
BEGIN
    DECLARE i int default 0;
    while i < 100
        do
            insert into table1(name, age, sex)
                values (CONCAT('Person', 1), FLOOR(RAND() * 80 + 18),
                        IF(FLOOR(RAND() * 2) = 0, 'Male', 'Female'));
        set i = i +1;
    end while;
end $$;
delimiter ;

call GenerateRandomData();

• 具体sql

WITH CTE AS (
  SELECT DISTINCT t.id AS tid, t.name AS tname, t.age AS tage, t.sex AS tsex,
                  t1.name AS t1name, t1.age AS t1age, t1.sex AS t1sex
  FROM table1 t
  JOIN table2 t1 ON t.id = t1.id
)
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1'
  AND COLUMN_NAME IN ('name', 'age', 'sex')
  AND (
    SELECT COUNT(*)
    FROM CTE
    WHERE
      CASE
        WHEN COLUMN_NAME = 'name' THEN tname != t1name
        WHEN COLUMN_NAME = 'age' THEN tage != t1age
        WHEN COLUMN_NAME = 'sex' THEN tsex != t1sex
      END
  ) > 0;