如何从MySQL中的EXECUTE语句插入数据? [英] How to insert data from an EXECUTE statement in mySql?

问题描述

我在wp_users表中有数据，我想将该表中的数据(ID列除外)复制到另一个表中，称为wp_users2.

I have data in a wp_users table, and I want to duplicate the data from that table (except for the ID column) into another table, called wp_users2.

如果我不关心要自动递增的id列，则可以执行以下操作:

If I didn't care about the id column, which I want to auto-increment, I could just do this:

insert into wp_users2 (select *, NULL as ID from wp_users)

所以我知道我可以通过键入除ID以外的所有列标题并手动将其选择为NULL来完成此操作，

So I know I could do this by typing out all of the column headers except for ID and manually selecting that one as NULL,

SELECT NULL as id, col2, col3...

但是我想动态地做到这一点.我读了这个很棒的S.O.发布有关如何执行此操作的方法，并且有效，但是我无法弄清楚如何获取它提供给我的数据并将其放入插入语句中.

but I'd like to do it dynamically. I read this great S.O. post about how to do that, and it works, however I can't figure out how to take the data it gives me and put it into an insert statement.

INSERT INTO wp_users2 (
  SET @sql = CONCAT('SELECT NULL as ID,', 
     (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'ID,', '') 
       FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_NAME = 'wp_users' 
       AND TABLE_SCHEMA = 'wp1'),
     ' FROM wp_users');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
)

正确的语法是什么?

推荐答案

据我了解-id是AUTO_INCREMENT字段.

As I understand - id is AUTO_INCREMENT field.

因此，请尝试使用此脚本作为任务的示例-

So, try to use this script as an example for your task -

CREATE TABLE table1(
  id INT(11) NOT NULL AUTO_INCREMENT,
  column1 VARCHAR(255) DEFAULT NULL,
  column2 VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE table2(
  id INT(11) NOT NULL AUTO_INCREMENT,
  column1 VARCHAR(255) DEFAULT NULL,
  column2 VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO table1 VALUES 
  (1, 'c1', 'c2'),
  (2, 'c3', 'c4');

SET @source_table = 'table1';
SET @target_table = 'table2';
SET @id = 'id';

SET @columns = NULL;
SELECT group_concat(column_name) INTO @columns FROM information_schema.columns
WHERE
  table_schema = 'database_name' -- Set your database name here
  AND table_name = @source_table
  AND column_name != @id;

SET @insert = concat('INSERT INTO ', @target_table, '(', @id, ',', @columns, ') SELECT NULL, ', @columns, ' FROM ', @source_table);
PREPARE stmt1 FROM @insert;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

这篇关于如何从MySQL中的EXECUTE语句插入数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！