How to change the column position of MySQL table without losing column data?

MysqlSql

Mysql Problem Overview

I want to change the column positions of my database table without losing data.

For example:

Current table:

+----+------+-------+----------+
| id | name | email | password |
+----+------+-------+----------+

to

+----+----------+------+-------+
| id | password | name | email |
+----+----------+------+-------+

Mysql Solutions

Solution 1 - Mysql

Try this:

ALTER TABLE table_name MODIFY password varchar(20) AFTER id

Solution 2 - Mysql

Hearaman's answer is correct; but if you are using phpMyAdmin, there is a visual and practical way to do that.

  1. Open the table
  2. Choose the "Structure" tab
  3. Click on "Move columns"
  4. Drag and drop column names

Move columns link, middle of the Structure tab Move columns popup

Solution 3 - Mysql

If you are using MySQL workbench,

  1. Right-click on table
  2. Alter table
  3. drag columns and re-order
  4. click apply and finish

Solution 4 - Mysql

Also, you can do it like this:

ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type AFTER another_column_name;

Solution 5 - Mysql

You can use modify/change keyword.

ALTER TABLE [table] CHANGE COLUMN [column] [column] [column definition] AFTER [column]

ALTER TABLE [table] MODIFY COLUMN [column] [column definition] AFTER [column]

Eg:

ALTER TABLE table_name MODIFY password varchar(20) AFTER id

ALTER TABLE table_name MODIFY password varchar(20) varchar(20) AFTER id

Solution 6 - Mysql

If you are like me, using MySQL workbench and got lost trying to implement @Mohemmed Niyaz answer, you can follow these steps,

  • On your top left, click Schemas
  • find and select your database, then find and select the table you want to modify
  • When you hover over the table name you would see three icons, click on settings (that is the one in the middle) or you can right-click and select Alter Table ,
  • then click on the column and drag it to the position you want your column to be.
  • on your bottom right you will see apply (click and apply).
Categories
Recommended Mysql Solutions
Recommended Sql Solutions

Previous Solution:

Why is stack memory size so limited?

C++Memory Management

Next Solution:

node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query?

node.jsNode Postgres

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionD SView Question on Stackoverflow
Solution 1 - MysqlHearamanView Answer on Stackoverflow
Solution 2 - MysqlArman OzakView Answer on Stackoverflow
Solution 3 - MysqlMohemmed NiyazView Answer on Stackoverflow
Solution 4 - MysqljundevView Answer on Stackoverflow
Solution 5 - MysqllogbasexView Answer on Stackoverflow
Solution 6 - MysqlUkpa UchechiView Answer on Stackoverflow