mysql alter table + column position

Anon

current table structure:

<pre>
table_name

| id | columnA | columnB | columnC |

</pre>

lets say i wanted to add columnD... easy
ALTER TABLE table_name ADD COLUMN columnD TYPE

what if i wanted columnD to physically lie between id and columnA... can i do a MOVE COLUMN or something like that?

i basically want a command that will result in this:

<pre>
table_name

| id | columnD | columnA | columnB | columnC |

</pre>

thanks

Anon

select columnD, columnA, columnB, columnC from table;

would get your data in the order requested.

You have to not think of a database as a spreadsheet however - think of it more like a big bag of hollow numbered balls. Inside each ball is little marbles.

The number is the record ID, the ball is all the data pertaining to that record ID and the marbles are the individual bits of info.

position and order matter not to a database. Its al in how you pull the data OUT that matters.

brandonschnell

ALTER TABLE table_name ADD COLUMN columnD TYPE after id

http://www.mysql.com/doc/A/L/ALTER_TABLE.html

Anon

obviously that works great if i know the table structure, but i asked the question because i needed the answer... this is for a system for user created database automation.

thanks for the wonderful analogy though...

Anon

thanks a lot...

[deleted]

Just a note:
As Craig mentioned, it is VERY bad (really very very very bad) to make your scripts so that they need the columns to appear in a certain order. Allways use column names, never SELECT *.

And why?
Because some databases simply don't let you change the order of columns, but most importantly, if you insert a new column inbetween existing columns lateron, you will have to change each and every query because suddenly half of your columns have shifted to the right and now contain the wrong data. You will not be happy.

Anon

i fully understand database design issues... and it doesn't even matter at all and my scripts will work fine no matter what, BUT for the problem i am solving, i needed to do this and asked the question and it was answered.

thanks for the tip, but i know whats up and just had to do this.

[deleted]

🙂