How to Add Multiple Columns After a Specific Column in a MySQL Table?

Adding Multiple Columns After a Specific Column in a MySQL Table

Adding multiple columns to a table is a common task in database management. In some cases, you may want to add these new columns after a specific existing column. However, using the ALTER TABLE statement to accomplish this can lead to errors.

Consider the following example:

ALTER TABLE `users` ADD COLUMN
(
    `count` smallint(6) NOT NULL,
    `log` varchar(12) NOT NULL,
    `status` int(10) unsigned NOT NULL
) 
AFTER `lastname`;

This query results in the following 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

To resolve this error and properly add the new columns after the lastname column, you should execute separate ALTER TABLE statements for each column:

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 following this approach, you can successfully add the desired columns to the users table after the lastname column.

The above is the detailed content of How to Add Multiple Columns After a Specific Column in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!