遗留PLC设备输出的多列MySQL表转精简表的技术实现咨询

嘿，我之前正好处理过几乎一模一样的遗留PLC数据迁移问题——那种几十列的宽表确实让人头疼，查询慢到离谱，写SQL都要疯。针对你卡在获取列名填充Result Number的问题，给你几个实用的解决方案，都是我实际落地过的：

方案1：用MySQL系统表自动生成映射（最省心） #

MySQL自带的INFORMATION_SCHEMA.COLUMNS表可以直接读取原表的所有列信息，包括列的顺序位置，刚好可以用来填充你的Result Number。

假设你的原表叫legacy_plc_data，精简后的目标表结构大概是(result_number INT, column_name VARCHAR(100), value DECIMAL(10,2), record_time DATETIME)（你可以根据实际数据类型调整）：

第一步：先确认列名和对应的Result Number #

先跑这条SQL看看原表的列顺序和名称，排除不需要迁移的列（比如主键、时间戳）：

SELECT 
  ORDINAL_POSITION AS result_number,
  COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
  AND TABLE_NAME = 'legacy_plc_data'
  AND COLUMN_NAME NOT IN ('record_time', 'id'); -- 替换成你要排除的列

第二步：自动生成迁移用的INSERT语句 #

如果要直接生成把宽表转成窄表的INSERT语句，用动态SQL拼接就行，完全不用手动列50多列：

SET @sql = '';
SELECT 
  CONCAT(
    'INSERT INTO normalized_plc_data (result_number, column_name, value, record_time) ',
    GROUP_CONCAT(
      CONCAT(
        'SELECT ', ORDINAL_POSITION, ' AS result_number, ',
        QUOTE(COLUMN_NAME), ' AS column_name, ',
        COLUMN_NAME, ' AS value, record_time ',
        'FROM legacy_plc_data'
      )
      SEPARATOR ' UNION ALL '
    )
  ) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
  AND TABLE_NAME = 'legacy_plc_data'
  AND COLUMN_NAME NOT IN ('record_time', 'id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这条语句会自动把原表的每一列数据转成精简表的一行，Result Number直接用列在原表中的顺序位置，完美解决手动列名的问题。

方案2：手动维护映射表（适合列固定的场景） #

如果原表的列不会随便变动，你可以创建一个映射表来自定义Result Number（不一定和原表列顺序一致）：

CREATE TABLE plc_column_mapping (
  result_number INT PRIMARY KEY,
  column_name VARCHAR(100) UNIQUE NOT NULL
);

-- 手动插入你的列名和对应Result Number
INSERT INTO plc_column_mapping VALUES
(1, 'temperature_sensor_1'),
(2, 'pressure_sensor_1'),
-- ... 把剩下的50多列都加进来
(55, 'status_flag_8');

然后用这个映射表生成迁移SQL：

SET @sql = '';
SELECT 
  CONCAT(
    'INSERT INTO normalized_plc_data (result_number, column_name, value, record_time) ',
    GROUP_CONCAT(
      CONCAT(
        'SELECT ', result_number, ', ', QUOTE(column_name), ', ', column_name, ', record_time ',
        'FROM legacy_plc_data'
      )
      SEPARATOR ' UNION ALL '
    )
  ) INTO @sql
FROM plc_column_mapping;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个方案的好处是Result Number完全由你控制，比如可以按照传感器类型分组编号。

方案3：做成自动定时迁移 #

既然你要设置自动的SELECT->INSERT，把上面的逻辑封装成存储过程，再用MySQL的事件调度器定期执行就行：

DELIMITER //
CREATE PROCEDURE migrate_plc_data()
BEGIN
  SET @sql = '';
  SELECT 
    CONCAT(
      'INSERT INTO normalized_plc_data (result_number, column_name, value, record_time) ',
      GROUP_CONCAT(
        CONCAT(
          'SELECT ', ORDINAL_POSITION, ', ', QUOTE(COLUMN_NAME), ', ', COLUMN_NAME, ', record_time ',
          'FROM legacy_plc_data ',
          'WHERE record_time > (SELECT COALESCE(MAX(record_time), ''1970-01-01'') FROM normalized_plc_data)'
        )
        SEPARATOR ' UNION ALL '
      )
    ) INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = '你的数据库名'
    AND TABLE_NAME = 'legacy_plc_data'
    AND COLUMN_NAME NOT IN ('record_time', 'id');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 开启MySQL事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建每5分钟执行一次的定时任务（时间可以自己调）
CREATE EVENT migrate_plc_event
ON SCHEDULE EVERY 5 MINUTE
DO CALL migrate_plc_data();

这样新产生的PLC数据就会自动同步到精简表，完全不用手动操作。

几个小提醒 #

• 记得替换SQL里的你的数据库名、legacy_plc_data、normalized_plc_data为你实际的名称
• 如果原表有大量历史数据，第一次迁移时可以加LIMIT分批跑，避免锁表影响业务
• 测试的时候可以先把EXECUTE stmt;注释掉，先打印@sql看看生成的语句对不对

内容的提问来源于stack exchange，提问作者roffster