alter table在column1之后添加多个列。

I need to add multiple columns to a table but position the columns after a 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服务器版本对应的手册，以便在第7行中使用lastname之后使用的正确语法。

How can I use AFTER in a query like this?

在这样的查询中，我该如何使用呢?

7 个解决方案

#1

504

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

检查语法

#2

If you want to add single column after specific field, then the mysql query is:

如果您想在特定字段后添加单个列，那么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. The mysql query as follows as:

如果您想添加多个列，那么您需要每次都使用“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 COLUMN column should actually be the first column you want to append to the table.

注意:在第二种方法中，最后一个添加列应该是您想要添加到表的第一个列。

e.g: if you want to add count, log, status in order after lastname, then the syntax would actually be:

e。g:如果您想要在lastname之后添加count、log、status，那么语法实际上就是:

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

#3

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

#4

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

您不能使用添加列的逗号来提及多个列名。每次定义新列时，都需要提到添加列。

#5

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.

一种可能是不必费心重新排序表中的列，只需通过添加列来修改它。然后，创建一个视图，该视图具有您想要的顺序的列——假定该顺序非常重要。视图可以很容易地更改，以反映您想要的任何排序。由于我无法想象顺序对编程应用程序来说很重要，所以视图应该满足那些可能重要的手动查询。

#6

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

更改表用户在现有列名之后添加列列名称DATATYPE(SIZE);

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

你可以这样做，对我来说没问题。

#7

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

#1

504