Add column in specific position of table. MySql

Question

I have a table AA which it's definition is:

CREATE TABLE `AA` (
   `a` int(11) NOT NULL,
   `b` int(11) NOT NULL,
   `c` int(11) NOT NULL,
   PRIMARY KEY  (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

I would like to add a column to the table however I want this column to be between b and `c, If I would create the table now it would have been like:

CREATE TABLE `AA` (
   `a` int(11) NOT NULL,
   `b` int(11) NOT NULL,
   `ba` int(11) NOT NULL,
   `c` int(11) NOT NULL,
   PRIMARY KEY  (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

The thing is that the table already contains data so I can not drop and recreate it. When I use alter table add column it adds the column to the end.

How do I insert a new column in specific position?

score 4 · Accepted Answer · answered Sep 13 '15 at 05:09

4

You can use AFTER. See the mysql ALTER TABLE syntax for more details

ALTER TABLE AA ADD COLUMN c int AFTER b

If you wanted to add it at the beginning you would use FIRST.

answered Sep 13 '15 at 05:09

Explosion Pills

183,406
48
308
385

score 0 · Answer 2 · answered Sep 13 '15 at 05:09

0

Easy with alter table

ALTER table table_name
    Add column columnName integer AFTER columnOtherName

answered Sep 13 '15 at 05:09

Juan Carlos Oropeza

45,789
11
74
113

score 0 · Answer 3 · answered Sep 13 '15 at 05:10

0

ALTER TABLE `AA` ADD `ba` INT NOT NULL AFTER `b`

answered Sep 13 '15 at 05:10

M0rtiis

3,665
1
13
22

Add column in specific position of table. MySql

3 Answers3