MySQL 如何在现有表的特定位置插入列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21330264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert columns at a specific position in existing table?
提问by PathmanKIP
I created a table with 85 columns but I missed one column. The missed column should be the 57th one. I don't want to drop that table and create it again. I'm looking to edit that table and add a column in the 57th index.
我创建了一个包含 85 列的表格，但我错过了一列。错过的列应该是第 57 列。我不想删除该表并再次创建它。我正在寻找编辑该表并在第 57 个索引中添加一列。
I tried the following query but it added a column at the end of the table.
我尝试了以下查询，但它在表的末尾添加了一列。
ALTER table table_name
Add column column_name57 integer
How can I insert columns into a specific position?
如何将列插入特定位置？
回答by AmazingDreams
ALTER TABLEby default adds new columns at the end of the table. Use the AFTERdirective to place it in a certain position within the table:
ALTER TABLE默认情况下，在表的末尾添加新列。使用AFTER指令将其放置在表格中的某个位置：
ALTER table table_name
Add column column_name57 integer AFTER column_name56
From mysql doc
来自 mysql 文档
To add a column at a specific position within a table row, use
FIRSTorAFTERcol_name. The default is to add the column last. You can also useFIRSTandAFTERinCHANGEorMODIFYoperations to reorder columns within a table.
要在表格行内的特定位置添加列，请使用
FIRST或AFTERcol_name。默认是最后添加列。您还可以使用FIRSTandAFTERinCHANGE或MODIFY操作对表中的列重新排序。
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
I googled for this for PostgreSQL but it seems to be impossible.
我为 PostgreSQL 搜索了这个，但似乎不可能。
回答by Nagaraj S
回答by java seeker
ALTER TABLE table_name ADD COLUMN column_name57 INTEGER AFTER column_name56
回答by andrey_sz
ALTER TABLE table_name ADD COLUMN column_name integer
回答by Deepak Sharma
if you are saying ADD COLUMN column_name then it will throw error
如果你说 ADD COLUMN column_name 那么它会抛出错误
u have to try
你得试试
ALTER TABLE temp_name ADD My_Coumn INT(1) NOT NULL DEFAULT 1
remember if table already has few record and u have to create new column then either u have to make it nullable or u have to define the default value as I did in my query
请记住，如果表已经很少有记录并且您必须创建新列，那么您必须将其设置为可以为空，或者您必须像我在查询中所做的那样定义默认值
回答by Anand agrawal
SET
@column_name =(
SELECT COLUMN_NAME
FROM
information_schema.columns
WHERE
table_schema = 'database_name' AND TABLE_NAME = 'table_name' AND ordinal_position = 56
);
SET
@query = CONCAT(
'ALTER TABLE `table_name` ADD `new_column_name` int(5) AFTER ',
@column_name
);
PREPARE
stmt
FROM
@query;
EXECUTE
stmt;
DEALLOCATE
stmt;
回答by Bob
As workaround one could consider the use of column renaming. I.e. add the new column at the end, and then until the new column is at the right position, add a temporary column for each column whose position is after the new column, copy the value from the old column to the temporary one, drop the old column and finally rename the temporary column.
作为解决方法，可以考虑使用列重命名。即在最后添加新列，然后直到新列在正确位置，为位置在新列之后的每一列添加一个临时列，将旧列的值复制到临时列，删除旧列，最后重命名临时列。
see also: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1770086700346491686
另见：https: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 1770086700346491686
回答by user6382558
I tried to alter the table like so:
我试图像这样改变表格：
table_name add column column_name after column column_name;
The first column_nameis the new column name, the second column_nameis the existing column where you plan to insert into after.
第一个column_name是新列名，第二个column_name是您计划插入的现有列。
