Adding multiple columns AFTER a specific column in MySQL

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


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 a single column after a specific field, then the following MySQL query should work:

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:

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 the exact order after lastname, then the syntax would actually be:

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
    

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

Tags:

Mysql

Ddl