Adding multiple columns AFTER a specific column in MySQL

mysql ddl
Koala picture Koala · Jul 9, 2013 · Viewed 570.4k times · Source

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?

Answer

Ayyappan Sekar picture Ayyappan Sekar · Jul 9, 2013

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

Related questions

How to delete a column from a table in MySQL

Given the table created using: CREATE TABLE tbl_Country ( CountryId INT NOT NULL AUTO_INCREMENT, IsDeleted bit, PRIMARY KEY (CountryId) ) How can I delete the column IsDeleted?

Read More
There can be only one auto column

How do I correct the error from MySQL 'you can only have one auto increment column'. CREATE TABLE book ( id INT AUTO_INCREMENT NOT NULL, accepted_terms BIT(1) NOT NULL, accepted_privacy BIT(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Read More
MySQL: ALTER TABLE if column not exists

I have this code: ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1 And I want to alter this table only if this column doesn't exist. I'm trying a lot of different ways, but nothing works: ALTER TABLE `…

Read More