当前位置：文江博客话题详情

如何移动 MySQL 表中的列？

发布于 2024-11-26 10:30:17 字数 216 浏览 3 评论 0原文

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

我想将该表更改为以下内容：Employees (empID, Department, empName); >

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

注意：我只想更改列位置。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

べ繥欢鉨o。 2024-12-03 10:30:17

如果 empName 是 VARCHAR(50) 列：

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

EDIT

根据注释，您还可以执行以下操作：

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

请注意 的重复empName 是故意的。你必须告诉 MySQL 你想要保留相同的列名。

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

另一个编辑：正如 @Luis Rossi 在评论中指出的，您需要在 AFTER 修饰符之前完全指定更改的列定义。上面的示例仅包含 VARCHAR(50)，但如果您需要其他特征（例如 NOT NULL 或默认值），则还需要包含这些特征。请参阅有关ALTER TABLE的文档了解更多信息。

If empName is a VARCHAR(50) column:

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.

You should be aware that both syntax versions are specific to MySQL. They won't work, for example, in PostgreSQL or many other 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.

回复收藏 0 原文

盛夏尉蓝 2024-12-03 10:30:17

更改列位置：

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

如果需要将其移动到第一个位置，则必须在 ALTER TABLE CHANGE [COLUMN] 查询末尾使用术语 FIRST：

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

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 UserOrder 
   CHANGE order_id order_id INT(11) NOT NULL FIRST;

回复收藏 0 原文

踏月而来 2024-12-03 10:30:17

phpMyAdmin 在表的结构视图中为此提供了一个 GUI。
选中要移动的列，然后单击列列表底部的更改操作。
然后，您可以更改所有列属性，并且您会在屏幕最右侧找到“移动列”功能。

当然，这只是在完美的最佳答案中构建查询，但 GUI 粉丝可能会喜欢替代方案。

我的 phpMyAdmin 版本是 4.1.7

回复收藏 0 原文

GRAY°灰色天空 2024-12-03 10:30:17

我必须对产品后期引入的列在 10 多个表上运行此操作。因此编写了这个快速的凌乱脚本来为所有“相关”表生成更改命令。

这将为架构中包含您尝试移动到不同位置的列的所有表生成 ALTER TABLE 命令。

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);

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.

This will generate an ALTER TABLE command for all TABLES in the schema that contain the COLUMN that you're trying to move to a different position.

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);

回复收藏 0 原文

涫野音 2024-12-03 10:30:17

在 SQL 中：

如果您想将 id 列移动到第一位，我们有一个查询，如下所示：

ALTER TABLE `mydatabase` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT FIRST;

在此查询中，信息如下：

mydatabase ：您的表名称。

但是如果您想将一列移动到另一列之后，则意味着您的 A 列可能位于第二列，并且您希望将其移动到 B 列之后的表的最后一个位置，因此请使用以下查询

ALTER TABLE `mydatabase` CHANGE `title` `title` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `img_name`;

：该查询的信息如下：

mydatabase：您的数据库名称在这里。
标题：是您的专栏，即
你想要移动（A 列）。
img_name：第二列（B 列）。
标题类型为：VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL（也许你的类型不同）

在 PHPMYADMIN 中：

从侧边栏中，单击 + 在表格内，单击 COLUMNS。
它打开一个包含所有列名称的表。单击下面的更改
列action（您要移动的列）。现在您看到另一个页面，
最后一项是移动列。这是选择选项并选择地方
你想移动该列。
选择并单击保存按钮。

In SQL :

If you want to move id column to the first place, we have a query for that, is like below:

ALTER TABLE `mydatabase` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT FIRST;

In this query, information is like below:

mydatabase : your table name.

But if you want to move a column after another column, mean maybe your A column is at the secound and you want to move it to the last place of your table after B column so use this query:

ALTER TABLE `mydatabase` CHANGE `title` `title` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `img_name`;

The information of this query is like below:

mydatabase: your database name is here.
title: is your column, that
you want to move (A column).
img_name: the secound column (B column).
The title type is : VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL ( maybe yours is different type)

In PHPMYADMIN :

From sidebar, click on + inside of your table, click on COLUMNS.
It open a table with all name of columns. Click on change under
column action (column you want to move). Now you see another page,
the last item is Move column. It is select option and choose place
you want to move that column.
Choose and click on save button.