MySQL – Ordering Columns Same as Describe Table

MySQLmysql-8.0order-by

I was under impression that default order of columns is internal mysql order but for some reason describe table and selecting column_name gives me compleatly different order.

How can I set ORDER BY to be same as in 'describe table' ('default' table order – not by name)?

Server version: 8.0.12 MySQL Community Server - GPL
mysql> describe assetType;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| assetTypeID | int(11)     | NO   | PRI | NULL    | auto_increment |
| assetName   | varchar(45) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='assetType';
+-------------+
| COLUMN_NAME |
+-------------+
| assetName   |
| assetTypeID |
+-------------+
2 rows in set (0.00 sec)

Best Answer

SELECT column_name FROM information_schema.columns
WHERE table_name='assetType'
ORDER BY ORDINAL_POSITION;

The field ORDINAL_POSITION is there to help

mysql> desc information_schema.columns;
+--------------------------+----------------------------+------+-----+---------+-------+
| Field                    | Type                       | Null | Key | Default | Extra |
+--------------------------+----------------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(64)                | YES  |     | NULL    |       |
| TABLE_SCHEMA             | varchar(64)                | YES  |     | NULL    |       |
| TABLE_NAME               | varchar(64)                | YES  |     | NULL    |       |
| COLUMN_NAME              | varchar(64)                | YES  |     | NULL    |       |
| ORDINAL_POSITION         | int(10) unsigned           | NO   |     | NULL    |       |
| COLUMN_DEFAULT           | text                       | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)                 | NO   |     |         |       |
| DATA_TYPE                | longtext                   | YES  |     | NULL    |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21)                 | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21)                 | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(10) unsigned        | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(10) unsigned        | YES  |     | NULL    |       |
| DATETIME_PRECISION       | int(10) unsigned           | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(64)                | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(64)                | YES  |     | NULL    |       |
| COLUMN_TYPE              | mediumtext                 | NO   |     | NULL    |       |
| COLUMN_KEY               | enum('','PRI','UNI','MUL') | NO   |     | NULL    |       |
| EXTRA                    | varchar(57)                | YES  |     | NULL    |       |
| PRIVILEGES               | varchar(154)               | YES  |     | NULL    |       |
| COLUMN_COMMENT           | text                       | NO   |     | NULL    |       |
| GENERATION_EXPRESSION    | longtext                   | NO   |     | NULL    |       |
| SRS_ID                   | int(10) unsigned           | YES  |     | NULL    |       |
+--------------------------+----------------------------+------+-----+---------+-------+
22 rows in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

mysql>
Related Question