How to move columns in a MySQL table?
Solution 1
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.
Solution 2
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;
Solution 3
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
Solution 4
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 = '<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);
Related videos on Youtube
sumit
Updated on August 04, 2022Comments
-
sumit almost 2 years
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.
-
Gromski almost 13 yearsMay I ask why? The column order is pretty much just an esthetic problem...
-
Ted Hopp almost 13 years@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.) -
sumit almost 13 yearsI 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 -
TehShrike almost 13 yearsTed Hopp's answer is spot on. However, I would like to direct iSumitG to this link: parseerror.com/sql/select*isevil.html
-
Gromski almost 13 yearsYou should always identify your columns by name, not by their order. Hence order shouldn't matter.
-
ypercubeᵀᴹ almost 13 years@iSumitG: Also note that the
AFTER column
can be used withALTER TABLE ADD column
as well. (for next time you add some fields.) -
Hooray Im Helping about 11 yearsIf 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.
-
Yann almost 10 yearsNote about SQL: I would suggest to remove the SQL tag for this question. Unlike MySQL, it is not possible to change the order with a script and therefore this response is not relevant to SQL users. To do this with SQL, open SMSS, right click on the table, and select "design" (modify) then slide the column to the right position. Then save (ctrl+S). SMSS will rebuild the entire table to order the columns accordingly.
-
Pang over 9 yearspossible duplicate of How to rearrange Mysql columns?
-
maaartinus about 8 years@Yann You're badly confusing SQL with some server by some well-known company. I do use SQL for years, but I've never heart about SMSS or other tools related to the server you mean.
-
Yann about 8 years@maaartinus, not sure to understand, I was just saying that it is not possible to do that using SQL syntax, but it is possible with MySQL? So the SQL tag in the question is misleading.. SSMS stands for SQL Server management studio (Microsoft). This is not SQL, but I assume that this is one interface that most people use.
-
maaartinus about 8 years@Yann While this syntax is not part of SQL-2011, it's still called SQL, at least for a lack of a better word. Other databases support such a syntax too, e.g. Firebird. So the SQL tag is IMHO right, as commands send to a DB are called SQL and no single DB implements the standard exactly.
-
HoldOffHunger about 3 yearsGreat question! In case anyone isn't sure, yes, column order does affect performance!
-
-
Erre Efe almost 13 yearsJust a note: MODIFY is not supported until version 4.
-
brianjcohen over 11 yearsYou 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;
-
Tristian almost 11 yearsMentioning how to move it to first position was quite usefull
-
Caleb about 9 yearsThe question asks specifically "How can this be done using ALTER statements". Not everyone running MySQL uses phpMyAdmin.
-
Admin about 9 yearsI 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.
-
skalee almost 9 yearsHave in mind that changed order will not be reflected in database SQL dumps.
-
Kip over 8 yearsAny 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?
-
Kip over 8 yearsnevermind, 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.
-
Ted Hopp about 8 years@SalehEnamShohag - Yes, according to the docs, the
COLUMN
keyword is optional inALTER TABLE
statements. I prefer to use it because I think it makes the statement more readable. -
learner about 8 yearsIs it mandatory to give datatype information? If I'm just rearranging a column without changing its default value, constraint etc, why do I need to mention its datatype since I don't need any change of datatype after rearranging ?
-
Ted Hopp about 8 years@learner -- Yes, it's required. The docs (see my previous comment) indicates that the column definition is not optional for either
MODIFY COLUMN
orCHANGE COLUMN
. I think that's because the most common use for those commands is to change the column definition. -
Luiz Rossi almost 8 yearsWorks 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 exampleVARCHAR(50)
-
Ted Hopp almost 8 years@LuizRossi - Excellent point. I updated my answer to incorporate this info.
-
Tebe over 7 yearsfor this action phpmyadmi doens't show the command doing the action . Not found it
-
Avatar about 3 yearsI was search for a
BEFORE
keyword. ButFIRST
was the solution.