如何移动MySQL表中的列?

Currently I am having the following MySQL table: Employees (empID, empName, department);

目前我有以下MySQL表:员工(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 ALTER statements?

如何使用ALTER语句来实现这一点呢?

Note: I want to change only column positions.

注意:我只想更改列的位置。

4 个解决方案

#1

260

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 empName is 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或许多其他DBMSs中，它们将不起作用。

Another edit: As pointed out by @Luis Rossi in a comment, you need to completely specify the altered column definition just before the AFTER modifier. The above examples just have VARCHAR(50), but if you need other characteristics (such as NOT NULL or a default value) you need to include those as well. Consult the docs on ALTER TABLE for more info.

另一个编辑:正如@Luis Rossi在评论中指出的，您需要在AFTER修饰符之前完全指定修改后的列定义。上面的示例只有VARCHAR(50)，但是如果您需要其他特征(比如NOT NULL或一个默认值)，那么也需要包含这些特征。更多信息请咨询修改表上的文档。

#2

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]查询结束时首先使用term:

ALTER TABLE UserOrder 
   CHANGE order_id order_id INT(11) NOT NULL FIRST;

#3

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。选中要移动的列，并单击列列表底部的更改操作。然后，您可以更改所有列属性，并在屏幕最右边找到“move column”函数。

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

我的phpadmin版本是4.1.7

#4

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命令。

SELECT CONCAT("ALTER TABLE `",TABLE_NAME,"` CHANGE COLUMN `",COLUMN_NAME,"` `",COLUMN_NAME,"` BIGINT AFTER `id`;")
FROM information_schema.COLUMNS c
WHERE TABLE_SCHEMA = '<YOUR SCHEMA NAME>'
AND COLUMN_NAME = '<COLUMN TO MOVE>'
AND TABLE_TYPE = 'BASE TABLE'
AND '<COLUMN TO MOVE AFTER>' 
        IN (SELECT COLUMN_NAME 
            FROM information_schema.COLUMNS c2 
            WHERE c.TABLE_NAME = c2.TABLE_NAME);

#1

260