How to move columns in a MySQL table

Question

Currently I am having the following MySQL table  Employees  empID  empName  department    I want to change the table to the following  Employees  empID  department  empName    How can this be done using ALTER statements   Note  I want to change only column positions

User · Answer

phpMyAdmin provides a GUI for this within the structure view of a table. Check to select the column you want to move and click the change action at the bottom of the column list. You can then change all of the column properties and you'll find the 'move column' function at the far right of the screen.

Of course this is all just building the queries in the perfectly good top answer but GUI fans might appreciate the alternative.

my phpMyAdmin version is 4.1.7

User · Answer

If empName is a VARCHAR 50  column   ALTER TABLE Employees MODIFY COLUMN empName VARCHAR 50  AFTER department    EDIT  Per the comments  you can also do this   ALTER TABLE Employees CHANGE COLUMN empName empName VARCHAR 50  AFTER department    Note that the repetition of empName is deliberate  You have to tell MySQL that you want to keep the same column name   You should be aware that both syntax versions are specific to MySQL  They won t work  for example  in PostgreSQL or many other DBMSs   Another edit  As pointed out by  Luis Rossi in a comment  you need to completely specify the altered column definition just before the AFTER modifier  The above examples just have VARCHAR 50   but if you need other characteristics  such as NOT NULL or a default value  you need to include those as well  Consult the docs on ALTER TABLE for more info

User · Answer

Change column position   ALTER TABLE Employees     CHANGE empName empName VARCHAR 50  NOT NULL AFTER department    If you need to move it to the first position you have to use term FIRST at the end of ALTER TABLE CHANGE  COLUMN  query   ALTER TABLE UserOrder     CHANGE order id order id INT 11  NOT NULL FIRST

User · Answer

I had to run this for a column introduced in the later stages of a product  on 10  tables  So wrote this quick untidy script to generate the alter command for all  relevant  tables   SET  NeighboringColumn     lt YOUR COLUMN SHOULD COME AFTER THIS COLUMN gt     SELECT CONCAT  ALTER TABLE    t TABLE NAME    CHANGE COLUMN    COLUMN NAME        COLUMN NAME       c DATA TYPE  CASE WHEN c CHARACTER MAXIMUM LENGTH IS NOT  NULL THEN CONCAT      c CHARACTER MAXIMUM LENGTH       ELSE    END     AFTER      NeighboringColumn       FROM information schema COLUMNS c  information schema TABLES t WHERE c TABLE SCHEMA     lt YOUR SCHEMA NAME gt   AND c COLUMN NAME     lt COLUMN TO MOVE gt   AND c TABLE SCHEMA   t TABLE SCHEMA AND c TABLE NAME   t TABLE NAME AND t TABLE TYPE    BASE TABLE  AND  NeighboringColumn IN  SELECT COLUMN NAME      FROM information schema COLUMNS c2      WHERE c2 TABLE NAME   t TABLE NAME

[mysql] How to move columns in a MySQL table?

Examples related to mysql

Examples related to sql

Examples related to alter