主页 / dba / 问题 / 216498
Accepted
tmdag
Asked: 2018-09-02 17:20:14 +0800 CST

column_name 的顺序与描述表相同

  • 772

我的印象是列的默认顺序是内部 mysql 顺序，但出于某种原因描述表并选择 column_name 给了我完全不同的顺序。

如何将 ORDER BY 设置为与“描述表”（“默认”表顺序 - 不是按名称）相同？

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)
mysql order-by

1 个回答

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

    ORDINAL_POSITION 字段可以提供帮助

    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>
    • 1

相关问题