MySQL arrange existing table columns

Question

How can I change the position of a certain existing column in mysql table.

Ex: I want username to be at the after all the columns? or I want it before any column in my table.

Thanks in advance :)

Answer 1

you cant change the order of existing columns.

but u can delete the column u want to change the position and use this command to add it in your desired position.

ALTER TABLE `MYTABLE` ADD `MYFILED` INT( 5 ) NOT NULL AFTER `POSITION` 

or

to add a field at the beginning of table

ALTER TABLE `MYTABLE` ADD `MYFIELD` INT( 5 ) NOT NULL FIRST 

Answer 2

You can change the order of columns if you like.

If your username column is varchar(255) then:

alter table `mytable` 
change column username username varchar(255) after `somecolumn`;

If it helps to better read a table definition, then why not?

Answer 3

Thanks guys for all of your response, I'm already done with it.

ALTER TABLE tbl_user MODIFY gender char(1) AFTER username;

Well it's just like organizing your table right? you don't want your primary key field to be on the last order of your table, at least I know how to coonfigure it if ever I encounter that problem, I'm trying to get used in text based database and not using gui for now.

Thanks again guys :)