MySQL SHOW COLUMNS的返回顺序是什么？

要返回MySQL SHOW COLUMNS的顺序，需要使用ORDER BY子句。语法如下所示−

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘yourTableName’
AND column_name LIKE 'yourStartColumnName%'
ORDER BY column_name DESC;

让我们在数据库TEST中创建一个表。创建表的查询语句如下−

mysql> create table OrderByColumnName
   -> (
   -> StudentId int,
   -> StudentFirstName varchar(10),
   -> StudentLastName varchar(10),
   -> StudentAddress varchar(20), 
   -> StudentAge int,
   -> StudentMarks int
   -> );
Query OK, 0 rows affected (1.81 sec)

情况1−

在这种情况下，结果按降序排列。以下是返回MySQL中显示列顺序的查询语句−

mysql> SELECT COLUMN_NAME
   -> FROM INFORMATION_SCHEMA.COLUMNS
   -> WHERE table_name = 'OrderByColumnName'
   -> AND column_name LIKE 'student%'
   -> ORDER BY column_name DESC;

以下是输出内容−

+------------------+
| COLUMN_NAME      |
+------------------+
| StudentMarks     |
| StudentLastName  |
| StudentId        |
| StudentFirstName |
| StudentAge       |
| StudentAddress   |
+------------------+
6 rows in set (0.00 sec)

情况2−如果您希望结果按升序排序，则无需编写ASC关键字，因为默认情况下结果将按升序排序。

查询语句如下所示−

mysql> SELECT COLUMN_NAME
   -> FROM INFORMATION_SCHEMA.COLUMNS
   -> WHERE table_name = 'OrderByColumnName' 
   -> AND column_name LIKE 'student%'
   -> ORDER BY column_name;

以下是输出内容−

+------------------+
| COLUMN_NAME      |
+------------------+
| StudentAddress   |
| StudentAge       |
| StudentFirstName |
| StudentId        |
| StudentLastName  |
| StudentMarks     |
+------------------+
6 rows in set (0.00 sec)

阅读更多：MySQL 教程