如何移动 MySQL 表中的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6805426/
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 move columns in a MySQL table?
提问by Sumit Gupta
Currently I am having the following MySQL table: Employees (empID, empName, department);
目前我有以下 MySQL 表： Employees (empID, empName, department);
I want to change the table to the following: Employees (empID, department, empName);
我想将表更改为以下内容： Employees (empID, department, empName);
How can this be done using ALTERstatements?
这如何使用ALTER语句来完成？
Note: I want to change only column positions.
注意：我只想更改列位置。
回答by Ted Hopp
If empName is a VARCHAR(50) column:
如果 empName 是 VARCHAR(50) 列：
ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;
EDIT
编辑
Per the comments, you can also do this:
根据评论，您也可以这样做：
ALTER TABLE Employees CHANGE COLUMN empName empName VARCHAR(50) AFTER department;
Note that the repetition of empNameis deliberate. You have to tell MySQL that you want to keep the same column name.
请注意，重复empName是故意的。你必须告诉 MySQL 你想要保持相同的列名。
You should be aware that both syntax versions are specific to MySQL. They won't work, for example, in PostgreSQL or many other DBMSs.
您应该知道这两个语法版本都特定于 MySQL。例如，它们在 PostgreSQL 或许多其他 DBMS 中不起作用。
Another edit: As pointed out by @Luis Rossi in a comment, you need to completely specify the altered column definition just before the AFTERmodifier. The above examples just have VARCHAR(50), but if you need other characteristics (such as NOT NULLor a default value) you need to include those as well. Consult the docs on ALTER TABLEfor more info.
另一个编辑：正如@Luis Rossi 在评论中指出的那样，您需要在AFTER修饰符之前完全指定更改的列定义。上面的例子只有VARCHAR(50)，但是如果您需要其他特性（例如NOT NULL或 默认值），您也需要包含这些特性。有关更多信息，请查阅文档ALTER TABLE。
回答by Igor Kostin
Change column position:
更改列位置：
ALTER TABLE Employees
CHANGE empName empName VARCHAR(50) NOT NULL AFTER department;
If you need to move it to the first position you have to use term FIRST at the end of ALTER TABLE CHANGE [COLUMN] query:
如果您需要将其移动到第一个位置，您必须在 ALTER TABLE CHANGE [COLUMN] 查询的末尾使用术语 FIRST：
ALTER TABLE UserOrder
CHANGE order_id order_id INT(11) NOT NULL FIRST;
回答by Igor Kostin
phpMyAdmin provides a GUI for this within the structure view of a table. Check to select the column you want to move and click the change action at the bottom of the column list. You can then change all of the column properties and you'll find the 'move column' function at the far right of the screen.
phpMyAdmin 在表格的结构视图中为此提供了一个 GUI。选中要移动的列，然后单击列列表底部的更改操作。然后，您可以更改所有列属性，您会在屏幕最右侧找到“移动列”功能。
Of course this is all just building the queries in the perfectly good top answer but GUI fans might appreciate the alternative.
当然，这只是在完美的最佳答案中构建查询，但 GUI 爱好者可能会欣赏替代方案。
my phpMyAdmin version is 4.1.7
我的 phpMyAdmin 版本是 4.1.7
回答by uchamp
I had to run this for a column introduced in the later stages of a product, on 10+ tables. So wrote this quick untidy script to generate the alter command for all 'relevant' tables.
我不得不在 10 多个表上为产品后期引入的列运行此程序。所以写了这个快速的不整洁的脚本来为所有“相关”表生成alter命令。
SET @NeighboringColumn = '<YOUR COLUMN SHOULD COME AFTER THIS COLUMN>';
SELECT CONCAT("ALTER TABLE `",t.TABLE_NAME,"` CHANGE COLUMN `",COLUMN_NAME,"`
`",COLUMN_NAME,"` ", c.DATA_TYPE, CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT
NULL THEN CONCAT("(", c.CHARACTER_MAXIMUM_LENGTH, ")") ELSE "" END ," AFTER
`",@NeighboringColumn,"`;")
FROM information_schema.COLUMNS c, information_schema.TABLES t
WHERE c.TABLE_SCHEMA = '<YOUR SCHEMA NAME>'
AND c.COLUMN_NAME = '<COLUMN TO MOVE>'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_TYPE = 'BASE TABLE'
AND @NeighboringColumn IN (SELECT COLUMN_NAME
FROM information_schema.COLUMNS c2
WHERE c2.TABLE_NAME = t.TABLE_NAME);
