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.

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