How to insert data from an EXECUTE statement in my

问题:

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?

回答1:

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;

回答2:

set @sql =  (select concat('insert into wp_users2 SELECT NULL,',
             group_concat(column_name),' from ',table_name) from information_schema.columns
         where table_name = 'wp_users' and table_schema = 'wp1' and column_name != 'id'
             order by ordinal_position);

prepare stmt1 from @sql;
execute stmt1;
deallocate prepare stmt1;