2

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)
  • But why do you care? DESCRIBE is lousy output; I advise never using it. – Rick James Oct 1 '18 at 23:17
  • @RickJames , I am printing it into qt table and would like to have always same sort. I am open if you have alternative suggestions – tmdag Oct 2 '18 at 3:18
1
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>
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.