CODEWITHSUNDEEP
mysqlmariadb
marius
marius

Reputation: 1266

MySQL (MariaDB) swap column positions by index, NOT using after

I am using the lastest version of MariaDB.

I have a simple table and want to swap positions of field 'test' with field 'gerge'. I can't retrieve the information of the whole column, which is why I can't use 'after'. (e.g. ALTER TABLE myTable MODIFY gerge INT AFTER id;).

I've googled abit but there seems to be no easy solution to this. I am looking for something like that:

ALTER TABLE CHANGE INDEX 2 TO 1

Does anyone tackeled this issue?

enter image description here

Upvotes: 0

Views: 194

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562661

The ordinal_position of a column can be found in information_schema.columns.

You can generate the ALTER TABLE by reading that table, for example:

select concat('alter table `', c1.table_schema, '`.`', c1.table_name, 
  '` modify column `', c1.column_name, '` ', c1.column_type, 
  ' after `', c2.column_name, '`;') as _sql
from information_schema.columns as c1 
join information_schema.columns as c2 using (table_schema, table_name)
where c1.table_schema='test' and c1.table_name='mytable' 
  and c1.ordinal_position=2 and c2.ordinal_position=3;

Output given your table:

+--------------------------------------------------------------------------+
| _sql                                                                     |
+--------------------------------------------------------------------------+
| alter table `test`.`mytable` modify column `test` int(11) after `gerge`; |
+--------------------------------------------------------------------------+

There's more work to be done, because my example doesn't handle NOT NULL or DEFAULT or extra column options or comments.

Upvotes: 1

Rick James
Rick James

Reputation: 142366

Use SHOW CREATE TABLE tablename; to get the whole info on every column. From that, formulate ALTER TABLE ... AFTER ...

Upvotes: 1

Nick
Nick

Reputation: 10539

Option 1.

dump the table data with column names. then restore it.

Option 2.

create table as select

Option 3.

Create new field, copy the data (update table set newf=oldf;)

Then drop old column and rename

Upvotes: 1

Related Questions