MySQL 从另一个表动态 INSERT INTO 非规范化表

Question

假设我有一个包含 30 万行的表 A

+---------+---------+---------+--------+--------
|   id    | Col1    |  Col2   |  Col 3 | Col n..
+---------+---------+---------+--------+--------
| id val  | Value 1 | Value 2 |  Val 3 | n..
+---------+---------+---------+--------+--------

我想创建一个非规范化的表 B，如下所示

+---------+---------+---------+
|tab_a_id |  Key    |  Val    |  
+---------+---------+---------+
| id val  | Col 1   | Value 1 |  
+---------+---------+---------+
| id val  | Col 2   | Value 2 |  
+---------+---------+---------+
| id val  | Col 3   | Val 3   |  
+---------+---------+---------+
| id val  | Col n.. | n..     |  

是否有一种方法可以动态运行表 A 的列，将其插入到表 B 中，其中包含表 A 中的 id、列名作为键以及每列的值？

Answer 1

将 TableA 逆透视到 TableB 的一种方法

INSERT INTO tableb (tab_a_id, `key`, val)
SELECT a.id, 
       CASE n.n 
          WHEN 1 THEN 'Col1'
          WHEN 2 THEN 'Col2'
          WHEN 3 THEN 'Col3'
          ...
       END,
       CASE n.n 
          WHEN 1 THEN Col1
          WHEN 2 THEN Col2
          WHEN 3 THEN Col3
          ...
       END
  FROM tablea a CROSS JOIN
(
  SELECT 1 n UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 
  ...
) n
 ORDER BY a.id, n.n;

结果:

| TAB_A_ID |  KEY |     VAL ||----------|------|---------||   id val | Col1 | Value 1 ||   id val | Col2 | Value 2 ||   id val | Col3 | Value 3 |

Here is a SQLFiddle demo

---

Now if you want to make it totally dynamic you can leverage dynamic sql and wrap it up into stored procedure for convenience

DELIMITER //
CREATE PROCEDURE unpivot()
BEGIN
  SET @sql = NULL, @c = NULL, @v = NULL, @n = NULL;

  SELECT GROUP_CONCAT(CONCAT('WHEN ', ordinal_position, ' THEN ''', column_name, '''') SEPARATOR ' '),
         GROUP_CONCAT(CONCAT('WHEN ', ordinal_position, ' THEN `', column_name, '`') SEPARATOR ' '),
         GROUP_CONCAT(CONCAT('SELECT ', ordinal_position, ' n') SEPARATOR ' UNION ALL ')
    INTO @c, @v, @n
    FROM INFORMATION_SCHEMA.columns
   WHERE table_schema = SCHEMA()
     AND table_name = 'TableA'
     AND column_name NOT IN ('id');

  SET @sql = CONCAT(
    'INSERT INTO tableb (tab_a_id, `key`, val) ',
    'SELECT a.id, CASE n.n ', @c, ' END, CASE n.n ', @v, ' END ',
    'FROM tablea a CROSS JOIN (', @n, ') n ORDER BY a.id, n.n'
  );

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

使用它:

CALL unpivot();

这是一个 SQLFiddle 演示