mysql execute into_如何从MySQL中的EXECUTE语句插入数据？

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.

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)

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...

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;

)

What's the right syntax for that?

解决方案

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;