changing order of fields

i have a table called "A"
it has 4 fields
1
2
3
4

is there a query where i could switch 2 for 3 so it would be this:

1
3
2
4

mysql

 

It is not possible to change the order of the table fields.

 

rename the table as <table_name>_t

create a view on the table (use original table_name) and in the select order the columns the way you require.

rename A to A_t

create view A as
select 1, 3, 2, 4 from A_t

 

You want third field after first field, so add a new field after field1 then fill the new field with field3, drop original field3, rename new field as field3.


ALTER TABLE `a` ADD `mysqlfront_temp_fieldname` VARCHAR(5) AFTER `field1`;
UPDATE `a` SET `mysqlfront_temp_fieldname`=`field3`;
ALTER TABLE `a` DROP `field3`;
ALTER TABLE `a` CHANGE `mysqlfront_temp_fieldname` `field3` VARCHAR(5);


NOTE: I suggest to BACKUP YOUR TABLE before doing.