如何移动 MySQL 表中的列?

2021-11-20 00:00:00 sql mysql alter

目前我有以下 MySQL 表:Employees (empID, empName, Department);

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

我想把表格改成如下:Employees (empID, Department, empName);

如何使用 ALTER 语句完成此操作?

How can this be done using ALTER statements?

注意:我只想更改列位置.

Note: I want to change only column positions.

推荐答案

如果 empName 是一个 VARCHAR(50) 列:

If empName is a VARCHAR(50) column:

ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;

编辑

根据评论，您也可以这样做:

Per the comments, you can also do this:

ALTER TABLE Employees CHANGE COLUMN empName empName VARCHAR(50) AFTER department;

请注意，empName 的重复是有意的.你必须告诉 MySQL 你要保持相同的列名.

Note that the repetition of empName is deliberate. You have to tell MySQL that you want to keep the same column name.

您应该知道这两个语法版本都特定于 MySQL.例如，它们在 PostgreSQL 或许多其他 DBMS 中不起作用.

You should be aware that both syntax versions are specific to MySQL. They won't work, for example, in PostgreSQL or many other DBMSs.

另一个正如@Luis Rossi 在评论中指出的那样，您需要在 AFTER 修饰符之前完全指定更改的列定义.上面的例子只有 VARCHAR(50)，但如果你需要其他特征(比如 NOT NULL 或默认值)，你也需要包括这些.有关详细信息，请参阅 ALTER TABLE 上的文档.

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.

相关文章