在MySQL中的特定列之后添加多个列

本文翻译自：Adding multiple columns AFTER a specific column in MySQL

I need to add multiple columns to a table but position the columns after a column called lastname . 我需要向一个表中添加多个列，但将这些列lastname 在名为lastname的列之后 。

I have tried this: 我已经试过了：

ALTER TABLE `users` ADD COLUMN
(
    `count` smallint(6) NOT NULL,
    `log` varchar(12) NOT NULL,
    `status` int(10) unsigned NOT NULL
) 
AFTER `lastname`;

I get this error: 我收到此错误：

You have an error in your SQL syntax; 您的SQL语法有误； check the manual that corresponds to your MySQL server version for the right syntax to use near ') AFTER lastname ' at line 7 检查与您的MySQL服务器版本相对应的手册，以在第7行的'）AFTER lastname '附近使用正确的语法

How can I use AFTER in a query like this? 如何在这样的查询中使用AFTER？

---

#1楼

参考：https://stackoom.com/question/1BbIO/在MySQL中的特定列之后添加多个列

---

#2楼

One possibility would be to not bother about reordering the columns in the table and simply modify it by add the columns. 一种可能性是不用担心重新排序表中的列，而只需通过添加列来对其进行修改。 Then, create a view which has the columns in the order you want -- assuming that the order is truly important. 然后，创建一个具有所需顺序的列的视图-假设顺序确实很重要。 The view can be easily changed to reflect any ordering that you want. 可以轻松更改视图以反映所需的任何顺序。 Since I can't imagine that the order would be important for programmatic applications, the view should suffice for those manual queries where it might be important. 由于我无法想象顺序对于编程应用程序将很重要，因此对于那些可能很重要的手动查询，该视图就足够了。

---

#3楼

Try this 尝试这个

ALTER TABLE users
ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`,
ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`,
ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`;

check the syntax 检查语法

---

#4楼

If you want to add a single column after a specific field, then the following MySQL query should work: 如果要在特定字段后添加一列，则以下MySQL查询应该起作用：

ALTER TABLE users
    ADD COLUMN count SMALLINT(6) NOT NULL
    AFTER lastname

If you want to add multiple columns, then you need to use 'ADD' command each time for a column. 如果要添加多个列，则每次需要为列使用“ ADD”命令。 Here is the MySQL query for this: 这是对此的MySQL查询：

ALTER TABLE users
    ADD COLUMN count SMALLINT(6) NOT NULL,
    ADD COLUMN log VARCHAR(12) NOT NULL,
    ADD COLUMN status INT(10) UNSIGNED NOT NULL
    AFTER lastname

---

Point to note 注意事项

In the second method, the last ADD COLUMN column should actually be the first column you want to append to the table. 在第二种方法中，最后一个ADD COLUMN 列实际上应该是要添加到表中的第一列。

Eg: if you want to add count , log , status in the exact order after lastname , then the syntax would actually be: 例如：如果您lastname后的确切顺序添加count ， log ， status ，则语法实际上是：

ALTER TABLE users
    ADD COLUMN log VARCHAR(12) NOT NULL AFTER lastname,
    ADD COLUMN status INT(10) UNSIGNED NOT NULL AFTER lastname,
    ADD COLUMN count SMALLINT(6) NOT NULL AFTER lastname

---

#5楼

ALTER TABLE `users` ADD COLUMN
`COLUMN NAME` DATATYPE(SIZE) AFTER `EXISTING COLUMN NAME`;

You can do it with this, working fine for me. 您可以这样做，对我来说很好。

---

#6楼

This one is correct: 这是正确的：

ALTER TABLE `users`
    ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`,
    ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`,
    ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`;