Sort mySQL table's fields

Question

So lets say I have a table (lets say 50 rows, 50 columns) that I occasionally have to make tiny edits to. While I realize I could be (maybe some of you will tell me I should be) using the query to do this, I like using a database management program (like Sequel Pro) to make changes by hand.

So I have two questions:

When you do a query without specifying the order to show the results, it follows the order that it is saved in the database. Is there any way to change THAT order (not the order of your results, the order in the table itself) without manually switching rows?

Also, is there any way to automatically sort the way the Fields show up in the table (the column order)?

I know that I should just be using queries to change values, but (imo) if the table is small enough, I find it easier to find the row and column in a program like Sequel Pro and just change it that way.

Thanks

Also, if my methods are completely wrong, please tell me

Answer 1

When you do a query without specifying the order to show the results, it follows the order that it is saved in the database. Is there any way to change THAT order (not the order of your results, the order in the table itself) without manually switching rows?

No.

is there any way to automatically sort the way the Fields show up in the table (the column order)?

The database doesn't care about column order. I'm told you can execute a statement from some SQL IDE's that resort columns, but it is creating a new table & transferring the data under the covers.

Answer 2

to Quote OMG Ponies

When you do a query without specifying the order to show the results, it follows the order that it is saved in the database. Is there any way to change THAT order (not the order of your results, the order in the table itself) without manually switching rows?

No.

and to add, not only can you not change it, but

it follows the order that it is saved in the database

is not necessarily correct. The order that records are returned from the database is not guaranteed to be in any specific order UNLESS you use an ORDER BY clause.

Other than having a handy SQL Script that formats your table in the order and columns you want which you could simply paste in each time you wanted to make a change I cant really help you.

eg

Select ID, FirstColumn, ThirdColumn, SecondColumn from myConfigTable 
Order By FieldToOrder

Answer 3

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE name ORDER BY something

It comes with some heavy caveats, and don't rely on them for anything else then perhaps some very small performance gain in sorting by it, of just for your convenience in tools that run arbitrary selects without an order by:

ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

Answer 4

Ditto on unpredictable order. If you want the rows in a certain order, that's what ORDER BY is for. To be snarky, your question is like asking, "How can I make my car turn left without using the steering wheel?" Even if there is some tricky way to accomplish this, what's the point?

RE column order: MySQL has an extension to the SQL standard that allows you to rearrange the ordering of columns in a table. I believe it's

ALTER TABLE mytable MODIFY mycolumn varchar(50) AFTER othercolumn;

This will move column "mycolumn" to come after "othercolumn". I think you have to give the data type -- varchar(50) in this example but of course it could be anything -- even if it's not changed.

To make a column the first column, you say FIRST instead of AFTER something.

Note that this is NOT standard SQL. Most SQL engines do not allow you to specify column order. Some purists insist that there is no such thing as column order, that a row is a collection of columns with no particular ordering. In real life, though, the database engine has to store them in SOME order, and I've never seen a database where "select *" did not return the columns in a consistent order. Personally, I often write generic code that builds data entry screens from the schema, and lists the fields in the order they come back from a "select *". I like MySQL for this kind of program because it allows me to specify the column order in the database, rather than having to layer that on somewhere else or have to rebuild a table to get the desired order.

I'm not familiar with SQL Pro but most tools will respect the DBMS's column order.