MySQL arrange existing table columns
MysqlMysql Problem Overview
How can I change the position of a certain existing column in MySQL table?
Ex: I want to move the column username from its current position to instead be after all the columns or I want it before any certain column in my table.
Mysql Solutions
Solution 1 - Mysql
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?
Solution 2 - Mysql
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 :)
Solution 3 - Mysql
Simple use this Query
alter table `admin` modify id int(11) first;
or
alter table `admin` modify id int(11) after `some_column`;
Solution 4 - Mysql
Your SQL should look like this:
ALTER TABLE `Customers` MODIFY `UserName` INT (11) AFTER `Orders`
Done! One line changes position and there's nothing else to do.
I advise against @rahim-asgari recommendation of ALTER TABLE MYTABLE ADD MYFILED INT( 5 ) NOT NULL AFTER POSITION
, since you'll to:
- Add a new field
- Copy old field's data into the new field
- Modify any constraints/Indexes
- Delete old field
Syntax:
ALTER TABLE `TableName` MODIFY `FieldToBeMoved` [SAME FIELD SETTINGS] [ACTION] `TargetPosition`
-
[SAME FIELD SETTINGS]
Refers to the configuration of your field. TINYINT, VARCHAR, TEXT, etc. Remember to include the size. Ej.varchar (255)
-
[ACTION]
You can move a fieldBEFORE
orAFTER
a specific field.
Replace forBEFORE
orAFTER
accordingly.
EXAMPLE
If your...
- Table name:
Customers
- Field to move:
UserName
UserName
settings:int(11)
- Target Position (Last field of the table):
Orders
Solution 5 - Mysql
ALTER TABLE [tbl_name] MODIFY|CHANGE [column definition] [AFTER|BEFORE] [a_column]
both would work. MODIFY
would be preferable if you only want to change column order but not renaming. Also, you CANNOT combine multiple columns reordering in a single ALTER TABLE statement. I.E. to rearrange integer columns col1, col2, col3 order to be in the order of col3, col2, col1, you will have to do
ALTER TABLE tbl_name MODIFY col3 int FIRST; ALTER TABLE tbl_name MODIFY col2 int AFTER col3;
Solution 6 - Mysql
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