在mySQL中对表数据进行程序化规范化

Question

我有一个反规范化格式的大型数据集。以下是列名的示例：

foreign_key_ID、P1、P2、P3、P4、P5....D1，D2，D3....等等。

这些字段都包含相似类型的数据。

我需要将其规范化到我现有的表结构中：

insert into new_table (id, name, index)
select foreign_key_id, P1, 1
from denormalized_table;

但这意味着我需要为我的反规范化表中的每个字段运行单独的查询，只需更改一些内容：

insert into new_table (id, name, index)
select foreign_key_id, P2, 2
from denormalized_table;

考虑到我有这么多这样的字段，这变得越来越乏味。

有没有一种方法可以将其自动化到一个单一的操作中？即:遍历字段(我不介意在某处创建一个符合条件的字段列表)，使用字段名和子选择中提取的索引#来提取字段名的最后一个数字(即"P1“中的"1”和“P2”中的"2“)。

Answer 1

这是一个开始：

SELECT column_name, substr(column_name,2) AS `index`
  FROM information_schema.columns
 WHERE table_schema = 'mydatabasename'
   AND table_name = 'denormalized_table'
   AND column_name REGEXP '^[PD][0-9]+$'
 ORDER BY column_name

您可以修改该语句中的选择列表，让MySQL为您生成语句：

SELECT CONCAT('INSERT INTO new_table (id, name, `index`) SELECT foreign_key_id, '
         ,column_name,', ',substr(column_name,2)
         ,' FROM denormalized_table ;') AS stmt
  FROM information_schema.columns
 WHERE table_schema = 'mydatabasename'
   AND table_name = 'denormalized_table'
   AND column_name REGEXP '^[PD][0-9]+$'
 ORDER BY column_name

它的输出将是一组MySQL INSERT语句，您随后可以执行这些语句。

如果要插入的数据的行数和总大小不是太大，您可以并且希望在“一次操作”中完成整个转换，那么您可以生成单个INSERT INTO ...SELECT语句，使用UNION ALL运算符。我会得到如下语句的大部分：

SELECT CONCAT('UNION ALL SELECT foreign_key_id, '
         ,column_name,', ',substr(column_name,2)
         ,' FROM denormalized_table ') AS stmt
  FROM information_schema.columns
 WHERE table_schema = 'mydatabasename'
   AND table_name = 'denormalized_table'
   AND column_name REGEXP '^[PD][0-9]+$'
 ORDER BY column_name

我将从中获得输出，并将第一个UNION ALL替换为INSERT INTO ...。这样我就可以运行一条语句来完成整个转换。

Answer 2

您正在寻找的是动态SQL。这是执行SQL语句的地方，您可以通过编程方式对这些语句进行汇编。只要在存储过程中，就可以运行字符串中的任意SQL代码。请参阅此链接：How To have Dynamic SQL in MySQL Stored Procedure

基本上，您可以使用mySQL语句通过迭代一组列来构建字符串。您可以使用SHOW COLUMNS语法(参见http://dev.mysql.com/doc/refman/5.0/en/show-columns.html)返回一个集合，然后循环遍历该结果集并构建动态查询字符串并以这种方式执行。

SHOW COLUMNS FROM myTable WHERE Field NOT IN (pkey, otherFieldIDontWantToInclude)