262

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.

Improve this question
13
  • 1
    May I ask why? The column order is pretty much just an esthetic problem... Commented Jul 24, 2011 at 7:08
  • 10
    @deceze perhaps not -- it defines the order of values in a SELECT * statement. (Granted, if the order of values is important, one should list them explicitly in the statement, but perhaps OP doesn't have total control here.) Commented Jul 24, 2011 at 7:12
  • 1
    I know it does not affect anything. My original table is having many columns so I just added 3 columns which are added in the last. But I want them to display at positions 3-4-5 to ease the use of SELECT statement Commented Jul 24, 2011 at 7:16
  • 7
    @iSumitG: Also note that the AFTER column can be used with ALTER TABLE ADD column as well. (for next time you add some fields.) Commented Jul 24, 2011 at 7:40
  • 3
    If loading a mysql dump, it uses insert into values. So if you for instance, you're loading data from prod into dev and the columns are out of order, you'll get an error. That's why one might want to do this. Commented May 15, 2013 at 17:48

6 Answers 6

Reset to default
426

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Just a note: MODIFY is not supported until version 4.
You need to repeat the column name, because the syntax assumes you may want to change the column name. Eg: ALTER TABLE Employees CHANGE COLUMN empName empName varchar(50) AFTER department;
Have in mind that changed order will not be reflected in database SQL dumps.
@SalehEnamShohag - Yes, according to the docs, the COLUMN keyword is optional in ALTER TABLE statements. I prefer to use it because I think it makes the statement more readable.
Works fine for me, in my case I needed define that the column was NOT NULL DEFAULT 1, this is done just after the column type in the example VARCHAR(50)
|
85

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;
Improve this answer

4 Comments

Mentioning how to move it to first position was quite usefull
Any idea how this would perform on a large table? Is it just changing some metadata, or does it actually have to reorganize data on the disk?
nevermind, answered my own question by trying it on a table i had with 3.9M rows, it took about 2 minutes. so it is definitely doing more than just swapping some metadata.
I was search for a BEFORE keyword. But FIRST was the solution.
21

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

Improve this answer

4 Comments

The question asks specifically "How can this be done using ALTER statements". Not everyone running MySQL uses phpMyAdmin.
I learn much of what I do nowadays at a command line from observing the query output from GUI tools like phpMyAdmin. I'm happy for this post to get as many downvotes as ppl see fit on this basis: 1 person will see this, get their job done in an environment they feel comfortable in, learn a little and move on.
for this action phpmyadmi doens't show the command doing the action . Not found it
All that being said: The phpMyAdmin GUI provided this code (which worked, as it ran the code before reporting success): ALTER TABLE workflow CHANGE count_to count_to INT(11) NOT NULL AFTER count_from; There are backticks around all the column names. Not shown here. This moved a column which was before "count_from" and was the 3rd column, to be after "count_from" and is now the 4th column instead.
2

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.

This will generate an ALTER TABLE command for all TABLES in the schema that contain the COLUMN that you're trying to move to a different position.

SET @NeighboringColumn = '<YOUR COLUMN SHOULD COME AFTER THIS COLUMN>';

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 = '<YOUR SCHEMA NAME>'
AND c.COLUMN_NAME = '<COLUMN TO MOVE>'
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);
Improve this answer

Comments

1

In SQL :

If you want to move id column to the first place, we have a query for that, is like below:

ALTER TABLE `mydatabase` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT FIRST;

In this query, information is like below:

  • mydatabase : your table name.

But if you want to move a column after another column, mean maybe your A column is at the secound and you want to move it to the last place of your table after B column so use this query:

ALTER TABLE `mydatabase` CHANGE `title` `title` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `img_name`;

The information of this query is like below:

  • mydatabase: your database name is here.
  • title: is your column, that you want to move (A column).
  • img_name: the secound column (B column).
  • The title type is : VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL ( maybe yours is different type)

In PHPMYADMIN :

  • From sidebar, click on + inside of your table, click on COLUMNS.
  • It open a table with all name of columns. Click on change under column action (column you want to move). Now you see another page,
  • the last item is Move column. It is select option and choose place you want to move that column.
  • Choose and click on save button.
Improve this answer

Comments

0

For those using TablePlus, you can just mark all tables, right click -> Copy, in the new table -> Paste.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.