What is the cleanest way to sort "describe table" query results?

I'm working on "describe table" output t开发者_运维百科o show a list of fields and their types, i want my primary keys to be at top of the list.. I think there's no way to sort describe's results using SQL (something like 'order by') rather than sorting it in PHP.

what do you think guys ? thanks

When you do a SHOW COLUMNS or a DESCRIBE TABLE, you're really just using the builtin special database called INFORMATION_SCHEMA to pull information about a named table. Funny thing is, it seems to not return the information as a table, so it's impossible to get the data returned by those functions to act like a table (for sorting, subquerying, etc.).

Fortunately, you can set up your own query to perform the same lookup as SHOW or DESCRIBE:

select
    COLUMN_NAME as "Field",
    COLUMN_TYPE as "Type",
    IS_NULLABLE as "Null",
    COLUMN_KEY as "Key",
    COLUMN_DEFAULT as "Default",
    EXTRA as "Extra"
from
    INFORMATION_SCHEMA.COLUMNS
where
    TABLE_NAME = 'my table' and
    TABLE_SCHEMA = 'my database'

-- add ordering --
order by COLUMN_TYPE;

You're correct. MySQL will always output columns in their actual order — that is, the order they're physically stored in the table's data.

You can physically move columns around within your table so that the primary keys are first, though this will require MySQL to lock the table and rewrite its entire data in the new order. That's almost never worthwhile.

If you're just interested in presenting nice output, regardless of how the table is actually structured, you can indeed sort the columns in PHP, perhaps using a simple usort() call.

Do not use describe but use the schema information tables which are provided by most dbms. Then you can use simple selects to get your information.