在不详细列出所有列的情况下，使用 MySQL 的 INSERT INTO … SELECT

在不详细列出所有列的情况下，使用 MySQL 的 INSERT INTO … SELECT

在MySQL中，当我们需要将一张表的数据插入到另一张表中时，我们通常会使用INSERT INTO … SELECT的语句。这个语句允许我们一次性将一张表的数据导入到另一张表中。通常在执行这个语句的时候，我们需要明确所有要插入的列名和顺序。但是这样会非常繁琐，尤其是当我们遇到大表时，手动列举所有列名将变得非常麻烦。那么有没有一种方法可以在不指定所有列名的情况下将数据插入到另一张表中呢？答案是有的！

阅读更多：MySQL 教程

方法1：使用INSERT INTO … SELECT *

我们可以使用通配符 * 来代替所有列名。例如，我们有以下两个表：

Table1:

Table2:

现在我们想将Table1的所有数据插入到Table2中。我们可以使用以下语句：

INSERT INTO Table2
SELECT * FROM Table1;

这个语句将Table1中的所有数据插入到Table2中，而不需要手动列举所有列名。

方法2：使用信息模式

我们也可以使用信息模式来生成要插入的列名。MySQL中的信息模式可以返回数据库中的元数据，包括表、列、索引等等。

如果我们想要将Table1中的所有数据插入到Table2中，我们可以使用以下语句：

INSERT INTO Table2
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Table1' 
ORDER BY ORDINAL_POSITION;

这个语句将返回Table1中的所有列名，并按照它们在表中的顺序排序。然后我们可以将这些列名用于INSERT INTO … SELECT语句，如下所示：

INSERT INTO Table2 (id, name, age, gender)
SELECT id, name, age, gender 
FROM Table1;

这个语句将Table1中的所有数据插入到Table2中，但是我们不需要手动列举所有列名。

方法3：使用子查询

我们还可以使用一个子查询来提取要插入的所有列名。例如，我们可以使用以下语句：

INSERT INTO Table2 
SELECT (
  SELECT GROUP_CONCAT(COLUMN_NAME) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE  TABLE_NAME = 'Table1' 
  ORDER BY ORDINAL_POSITION)
FROM Table1;

这个语句将返回Table1中的所有列名，并将它们作为一个字符串插入到Table2中的一行记录中。然后，我们可以使用以下语句将Table1中的所有数据插入到Table2中：

SET @query = CONCAT('INSERT INTO Table2 (', 
  (SELECT GROUP_CONCAT(COLUMN_NAME) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'Table1' 
  ORDER BY ORDINAL_POSITION), 
  ') SELECT ', 
  (SELECT GROUP_CONCAT(COLUMN_NAME) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'Table1' 
  ORDER BY ORDINAL_POSITION), 
  ' FROM Table1');

PREPARE stmt FROM @query;
EXECUTE stmt;

这个语句将使用MySQL的动态SQL功能，将列名插入到INSERT INTO … SELECT语句中，并将完整的查询作为一个字符串存储在变量@query中。然后，我们使用PREPARE和EXECUTE语句执行这个查询。

总结

MySQL INSERT INTO … SELECT语句允许我们将一张表的数据插入到另一张表中。虽然手动列举所有列名可以解决问题，但是也非常繁琐。我们可以使用通配符 * 来代替所有列名，使用信息模式获取要插入的所有列名，或者使用子查询来动态生成完整的SQL查询语句，从而提交MySQL INSERT INTO … SELECT查询并向目标表中插入数据。这几种方法都可以在不手动列举所有要插入的列名的情况下完成INSERT INTO … SELECT操作。在实际开发中，我们可以根据不同的需求选择不同的方法，从而提高开发效率。