如何将无规范列值的MySQL数据库迁移至新库并排序整理？

嘿，这个场景我经常碰到！你当前的表是典型的宽表结构，把多组键值对（IDn/Valuen）塞进了同一行，要迁移到新库并整理成规范结构，最简的方式是把它转换成长表（一维键值结构），用MySQL原生的SQL就能搞定，不用额外工具，步骤如下：

1. 创建新数据库与目标规范表 #

首先在MySQL里创建你的新数据库，然后建立结构规范的目标表——一行存储一组ID对应的键值对：

-- 创建新数据库
CREATE DATABASE IF NOT EXISTS new_normalized_db;

-- 切换到新数据库
USE new_normalized_db;

-- 创建规范的长表结构
CREATE TABLE IF NOT EXISTS normalized_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    main_id INT NOT NULL, -- 对应原表的ID列，关联原数据的主标识
    sub_id INT, -- 对应原表的ID1/ID2/ID3/ID4
    value VARCHAR(255), -- 对应原表的Value1/Value2/Value3/Value4
    INDEX idx_main_id (main_id),
    INDEX idx_sub_id (sub_id)
);

2. 迁移并转换原表数据 #

用UNION ALL把原宽表的每一组IDn/Valuen拆成单独的行，插入到新表中——这是转换宽表到长表的核心操作，效率也很高：

-- 假设原数据库是old_db，原表是raw_data
INSERT INTO new_normalized_db.normalized_data (main_id, sub_id, value)
SELECT ID, ID1, Value1 FROM old_db.raw_data
UNION ALL
SELECT ID, ID2, Value2 FROM old_db.raw_data
UNION ALL
SELECT ID, ID3, Value3 FROM old_db.raw_data
UNION ALL
SELECT ID, ID4, Value4 FROM old_db.raw_data
-- 可选：过滤掉sub_id或value为空的无效行
WHERE sub_id IS NOT NULL AND value IS NOT NULL
-- 可选：按主ID和子ID排序插入
ORDER BY main_id, sub_id;

提示：如果原表还有更多的IDn/Valuen列，继续追加UNION ALL SELECT ID, IDn, Valuen FROM ...即可。

3. （可选）优化排序与查询 #

如果需要让新表的数据物理排序更规整，可以执行：

ALTER TABLE new_normalized_db.normalized_data ORDER BY main_id, sub_id;

不过要注意：MySQL的ALTER TABLE ... ORDER BY只是临时整理物理顺序，后续插入新数据不会自动维持。如果需要长期高效的排序查询，依赖我们之前建的索引idx_main_id和idx_sub_id就足够了。

特殊情况处理 #

• 如果原数据库和新数据库不在同一MySQL实例：先把原表导出（用mysqldump old_db raw_data > raw_data.sql），再导入到新实例的new_normalized_db中，然后执行上面的转换SQL。
• 如果原表的Value列是不同数据类型：可以把目标表的value字段改成TEXT或者根据实际情况调整类型。

这样操作下来，你的数据就完全变成结构规范的长表了，后续做统计、关联查询都会方便很多！

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