返回 MySQL SHOW COLUMNS 的顺序?
mysqlmysqli database
要返回 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)
