MYSQL: change column order

This article will see how to change columns’ order in a MySQL table without losing the data already present in the column.

Table of Contents

• Syntax for MySQL change column order
• MySQL change column order AFTER
• MySQL make column FIRST
• Using CHANGE clause to change column order of a table in MysQL

Let us get started by making a sample data table followed by inserting a few rows into it.

To view the snapshot of the table, student_enroll_data execute:

SELECT * FROM student_enroll_data;

Output:-

Syntax for MySQL change column order

Observe the below syntax to change column order.

ALTER TABLE tableName MODIFY [COLUMN] columnName column_definition [FIRST | AFTER col_name]

• Here we are using the ALTER statement along with MODIFY Clause.
• tableName: is the name of your working table.
• columnName: is the name of the column for which you need to change the order.
• column_definition: is the datatype and size of the column whose order we are changing.
• [COLUMN] : is optional.
• [FIRST | AFTER col_name]: FIRST or AFTER can be used to change a column’s order. FIRST is used if we want to make our column the first column of a table; AFTER keyword is used if we’re going to place our column after a specific column (col_name).

Let us forge ahead into other sections to view the examples for a better understanding.

MySQL change column order AFTER

In our table student_enroll_data, the column student_ssn_no is placed at the fourth position and after enroll_date. Let us see in this section how to move the column student_ssn_no to some other place after student_id in our example.

ALTER TABLE student_enroll_data MODIFY COLUMN student_ssn_no BIGINT AFTER student_id ;

Action Output Message :-

16:23:50 ALTER TABLE student_enroll_data MODIFY COLUMN student_ssn_no BIGINT AFTER student_id 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.022 sec.

Let us verify if the position got changed by executing:

Output:-

Output in the image_2 shows that the column student_ssn_no is after student_id. Also, the data is not lost.

MySQL make column FIRST

Let us see in this section how to make the column student_ssn_no the first column of the table student_enroll_data. Observe the below query for the same.

ALTER TABLE student_enroll_data MODIFY COLUMN student_ssn_no BIGINT FIRST ;

Action Output Message :-

18:33:15 ALTER TABLE student_enroll_data MODIFY COLUMN student_ssn_no BIGINT FIRST 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.015 sec

Let us verify if the position of student_ssn_no got changed by executing:

SELECT * FROM student_enroll_data;

Output:-

Output in image_3 shows that the column student_ssn_no is the first column of the table. Also, the data is not lost.

Using CHANGE clause to change column order of a table in MysQL

Alternatively, we can also use the CHANGE keyword instead of MODIFY to change a column’s MySQL position both FIRST and AFTER can be used with CHANGE.

Syntax:–

ALTER TABLE tableName  CHANGE [COLUMN] oldColumnName newColumnName column_definition [FIRST | AFTER col_name]

• Generally, CHANGE is used when we want to rename the column and change the column definition simultaneously.
• tableName: is the name of your working table.
• oldColumnName: is the name of the column for which you need to change the order.
• newColumnName: is used in case you want to rename the column as well.  READ MORE: How to rename a column in MySQL
• column_definition: is the datatype and size of the column whose order we are changing.
• [COLUMN]: is optional.
• [FIRST | AFTER col_name]: FIRST or AFTER can be used to change a column’s order. FIRST is used if we want to make our column the first column of a table, AFTER keyword is used if we’re going to place our column after a specific column (col_name).

Observe the below query and its output to make student_ssn_no the first column of the table student_enroll_data.

ALTER TABLE student_enroll_data CHANGE COLUMN student_ssn_no student_ssn_no BIGINT FIRST;

Output:-

Observe the below query and its output to make student_ssn_no as the second column of the table.

ALTER TABLE student_enroll_data CHANGE COLUMN student_ssn_no student_ssn_no BIGINT AFTER student_id;

Output:-

In both cases, the order of data is changed while preserving the data of the column.

We hope this article helped you in changing the order of the column in a MySQL table. Good Luck!!!