我正在使用herehere提供的建议在表中添加新列

本质上,我希望自动填充(fields_in_orig_table),而不必手动输入(有很多列,并且表与表之间的变化):

CREATE TABLE games_new LIKE games_orig;
ALTER TABLE games_new ADD COLUMN location varchar(256);
INSERT INTO games_new (fields_in_orig_table) SELECT * FROM games_orig;
RENAME TABLE games_orig TO games_old, games_new TO games_orig;
DROP TABLE games_old;


我的想法是这样的:

CREATE TABLE games_new LIKE games_orig;
ALTER TABLE  games_new ADD COLUMN version varchar(256);
INSERT INTO  games_new

          (SELECT CONCAT(GROUP_CONCAT(column_name ORDER BY ordinal_position
           SEPARATOR " ,  "), " ") AS columns
           FROM information_schema.columns
           WHERE table_schema = 'games' AND table_name = 'games_orig' )

SELECT * FROM games_orig;
RENAME TABLE games_orig TO games_old, games_new TO games_orig;
DROP TABLE games_old;


这给了我语法错误(在Select concat ....附近)。

获取以逗号分隔的列列表的原始语法为:

SELECT CONCAT("'", GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR "', '"), "'") AS columns
FROM information_schema.columns
WHERE table_schema = 'db_name' AND table_name = 'tbl_name'


在查询中,我删除了多余的引号,因为我认为查询不需要引号作为列列表的一部分。

我在这里做错了什么?请问谁能帮忙?

最佳答案

INSERT INTO  games_new

VALUES (SELECT CONCAT....

关于mysql - ALTER TABLE ADD COLUMN和自动列列表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28425755/

10-11 03:32