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

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:-

image_1: student_enroll_data

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:-

image_2

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:-

image_3

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:-

image_4

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:-

image_5

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!!!