CODEWITHSUNDEEP
mysql
Barry
Barry

Reputation: 1587

MySQL arrange existing table columns

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.

Upvotes: 71

Views: 57128

Answers (6)

Omar Juvera
Omar Juvera

Reputation: 12287

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:

  1. Add a new field
  2. Copy old field's data into the new field
  3. Modify any constraints/Indexes
  4. 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 field BEFORE or AFTER a specific field.
    Replace for BEFORE or AFTER accordingly.

EXAMPLE

If your...

  • Table name: Customers
  • Field to move: UserName
  • UserName settings: int(11)
  • Target Position (Last field of the table): Orders

Upvotes: 9

Devy
Devy

Reputation: 10189

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;

Upvotes: 5

pradeep
pradeep

Reputation: 261

Simple use this Query 

alter table `admin` modify id int(11) first;

or

alter table `admin` modify id int(11) after `some_column`;

Upvotes: 20

Barry
Barry

Reputation: 1587

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

Upvotes: 47

ceteras
ceteras

Reputation: 3378

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?

Upvotes: 104

rahim asgari
rahim asgari

Reputation: 12437

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

Upvotes: -5

Related Questions