Every now and then I find myself adding a new field to a table as an after thought, this results in the column being listed last in your database client and depending on the importance my not be grouped next to the field you’d like it beside. I’m guilty of being very picky about what order my fields are in and I prefer to have my primary keys listed first and my foreign keys to be listed last. If were to, for example, add a middle name field to a table that stores data about customers I would want this new column between the ones for the first and names as this makes sense to me and when I’m scanning a database using PhpMyAdmin or SQLYog I want that column to be where I’d expect it to be, and not lingering at the end of the database.
So how does one go about moving a field, without recreating the table? Unforgeable it’s not something that’s typically offered by database front ends, so you’re going to have to execute a query that uses the ALTER TABLE statement:
ALTER TABLE foobartable MODIFY COLUMN foo VARCHAR(16) AFTER bar;
In the above example I am moving the field/column named “foo” so that it is placed directly after the one called “bar.” You’ll notice I also have to specify the field type, so I have restated that it is to be a 16 character VARCHAR field. After executing the query you’ll probably get a response back (depending on your front end) about the number of affected rows, which will be the same as the number of records you have. The reason for this is because silently the program is dropping the table, recreating it, and reinserting all your records. This may seem excessive, but that’s the way it’s done, and the final result is the same.
A seasoned Senior Solutions Architect with 18 years of experience in technology design and implementation. Renowned for innovative solutions and strategic insights, he excels in driving complex projects to success. Outside work, he is a passionate fisherman and fish keeper, specializing in planted tanks.