alter table add MULTIPLE columns AFTER column1
I need to add multiple columns to a table but position the columns after a column called 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
How can I use AFTER in a query like this?
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
If you want to add single column after specific field, then the mysql query is:
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:
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.
eg: if you want to add count , log , status in order after lastname , then the syntax would actually be:
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
这是正确的：
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`;
上一篇: 用户设置，使用EAV模式或仅添加列