MySQL 排列现有的表列

MySQL arrange existing table columns

提问by Barry

How can I change the position of a certain existing column in MySQLtable?

如何更改MySQL表中某个现有列的位置？

Ex: I want to move the column usernamefrom its current position to instead be after all the columns or I want it before any certain column in my table.

例如：我想将列用户名从其当前位置移动到所有列之后，或者我希望它在表中的任何特定列之前。

回答by ceteras

You can change the order of columns if you like.

如果您愿意，可以更改列的顺序。

If your username column is varchar(255) then:

如果您的用户名列是 varchar(255) 则：

alter table `mytable` 
change column username username varchar(255) after `somecolumn`;

If it helps to better read a table definition, then why not?

如果它有助于更​​好地阅读表定义，那为什么不呢？

回答by Barry

Thanks guys for all of your response, I'm already done with it.

谢谢大家的回复，我已经搞定了。

ALTER TABLE tbl_user MODIFY gender char(1) AFTER username;

Well it's just like organizing your table right? you don't want your primary key field to be on the last order of your table, at least I know how to coonfigure it if ever I encounter that problem, I'm trying to get used in text based database and not using gui for now.

嗯，这就像整理你的桌子一样对吗？你不希望你的主键字段在你的表的最后一个顺序上，至少我知道如果我遇到那个问题如何配置它，我试图在基于文本的数据库中使用而不是使用 gui现在。

Thanks again guys :)

再次感谢各位:)

回答by pradeep

Simple use this Query

简单使用这个查询

alter table `admin` modify id int(11) first;

or

或者

alter table `admin` modify id int(11) after `some_column`;

回答by Omar

Your SQL should look like this:

您的 SQL 应如下所示：

ALTER TABLE `Customers` MODIFY `UserName` INT (11) AFTER `Orders`

Done! One line changes position and there's nothing else to do.

完毕！一行改变位置，没有其他事情可做。

---

---

I advise against @rahim-asgarirecommendation of ALTER TABLE MYTABLE ADD MYFILED INT( 5 ) NOT NULL AFTER POSITION, since you'll to:

我不建议@rahim-asgari推荐ALTER TABLE MYTABLE ADD MYFILED INT( 5 ) NOT NULL AFTER POSITION，因为你会：

1. Add a new field
2. Copy old field's data into the new field
3. Modify any constraints/Indexes
4. Delete old field

1. 添加新字段
2. 将旧字段的数据复制到新字段中
3. 修改任何约束/索引
4. 删除旧字段

---

---

Syntax:

句法：

ALTER TABLE `TableName` MODIFY `FieldToBeMoved` [SAME FIELD SETTINGS] [ACTION] `TargetPosition`

• [SAME FIELD SETTINGS]
  Refers to the configuration of your field. TINYINT, VARCHAR, TEXT, etc. Remember to include the size. Ej. varchar (255)

• [ACTION]
  You can move a field BEFOREor AFTERa specific field.
  Replace for BEFOREor AFTERaccordingly.

• [SAME FIELD SETTINGS]
  指的是你的字段的配置。TINYINT、VARCHAR、TEXT 等。记住包括大小。Ej。varchar (255)

• [ACTION]
  您可以移动字段BEFORE或AFTER特定字段。
  替换为BEFORE或AFTER相应地。

---

---

EXAMPLE

例子

If your...

如果你的...

• Table name: Customers
• Field to move: UserName
• UserNamesettings: int(11)
• Target Position (Last field of the table): Orders

• 表名： Customers
• 要移动的字段： UserName
• UserName设置： int(11)
• 目标位置（表格的最后一个字段）： Orders

回答by Devy

ALTER TABLE [tbl_name] MODIFY|CHANGE [column definition] [AFTER|BEFORE] [a_column]both would work. MODIFYwould be preferable if you only want to change column order but not renaming. Also, you CANNOTcombine multiple columns reordering in a single ALTER TABLE statement. I.E. to rearrange integer columns col1, col2, col3 order to be in the order of col3, col2, col1, you will have to do

ALTER TABLE [tbl_name] MODIFY|CHANGE [column definition] [AFTER|BEFORE] [a_column]两者都可以。MODIFY如果您只想更改列顺序而不是重命名，那就更可取了。此外，您不能在单个 ALTER TABLE 语句中组合多列重新排序。IE 重新排列整数列 col1、col2、col3 的顺序为 col3、col2、col1，您将不得不这样做

ALTER TABLE tbl_name MODIFY col3 int FIRST; ALTER TABLE tbl_name MODIFY col2 int AFTER col3;

ALTER TABLE tbl_name MODIFY col3 int FIRST; ALTER TABLE tbl_name MODIFY col2 int AFTER col3;

回答by rahim asgari

you cant change the order of existing columns.

您不能更改现有列的顺序。

but u can delete the column u want to change the position and use this command to add it in your desired position.

但是您可以删除要更改位置的列并使用此命令将其添加到您想要的位置。

ALTER TABLE `MYTABLE` ADD `MYFILED` INT( 5 ) NOT NULL AFTER `POSITION` 

or

或者

to add a field at the beginning of table

在表的开头添加一个字段

ALTER TABLE `MYTABLE` ADD `MYFIELD` INT( 5 ) NOT NULL FIRST