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

Adding multiple columns AFTER a specific column in MySQL

提问by Koala

I need to add multiple columns to a table but position the columns aftera column called 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; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AFTER lastname' at line 7

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解lastname在第 7 行的“) AFTER ”附近使用的正确语法

How can I use AFTER in a query like this?

如何在这样的查询中使用 AFTER？

回答by Ayyappan Sekar

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

检查语法

回答by user3106476

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. Here is the MySQL query for this:

如果要添加多列，则每次需要对一列使用“ADD”命令。这是用于此的 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 COLUMNcolumnshould actually be the first column you want to append to the table.

在第二种方法中，最后一ADD COLUMN列实际上应该是您要附加到表中的第一列。

E.g: if you want to add count, log, statusin the exact order after lastname, then the syntax would actually be:

例如：如果你想添加count，log，status之后的准确顺序lastname，那么语法实际上是：

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

回答by Piyush Saxena

You cannot mention multiple column names with commas using ADD COLUMN. You need to mention ADD COLUMNevery time you define a new column.

您不能使用ADD COLUMN. ADD COLUMN每次定义新列时都需要提及。

回答by Denys Popov

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

回答by Gaurav Singh

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

You can do it with this, working fine for me.

你可以用这个来做，对我来说很好。

回答by Ahmed

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.

一种可能性是不关心重新排序表中的列，只需通过添加列来修改它。然后，创建一个视图，其中的列按您想要的顺序排列——假设顺序真的很重要。可以轻松更改视图以反映您想要的任何排序。由于我无法想象顺序对于程序化应用程序很重要，因此视图应该足以满足那些可能很重要的手动查询。

回答by TechyFlick

ALTER TABLE listingADD countINT(5), ADD logVARCHAR(200), ADD statusVARCHAR(20) AFTER stat

ALTER TABLE listingADD countINT(5), ADD logVARCHAR(200), ADD statusVARCHAR(20) AFTER stat

It will give good results.

它会产生很好的结果。

回答by WestAce

Alternatively:

或者：

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

Will put them in the order you want while streamlining the AFTER statement.

将它们按您想要的顺序排列，同时简化 AFTER 语句。

回答by Aiswarya T S

This works fine for me:

这对我来说很好用：

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

回答by Jorge Santos Neill

The solution that worked for me with default value 0 is the following

默认值 0 对我有用的解决方案如下

ALTER TABLE reservations ADD COLUMN isGuest BIT DEFAULT 0