如何从 mySql 中的 EXECUTE 语句插入数据？

发布于 2024-11-02 17:43:51 字数 876 浏览 0 评论 0原文

我的 wp_users 表中有数据，我想将该表中的数据（ID 列除外）复制到另一个名为 wp_users2 的表中。

如果我不关心我想要自动递增的 id 列，我可以这样做：

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

所以我知道我可以通过键入除 ID 之外的所有列标题来做到这一点并手动将其选择为 NULL，

SELECT NULL as id, col2, col3...

但我想动态地执行此操作。我读了这篇很棒的帖子关于如何做到这一点，它有效，但是我不知道如何获取它给我的数据并将其放入插入语句中。

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;
)

正确的语法是什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无言温柔 2024-11-09 17:43:51

据我了解 - id 是 AUTO_INCRMENT 字段。

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

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;

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;

回复收藏 0 原文

凉墨 2024-11-09 17:43:51

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;

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;

回复收藏 0 原文

~没有更多了~