返回 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 中 show column 順序的查詢 -

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)

更新於: 2019-07-30

260 次檢視

開啟你的職業生涯

透過完成課程獲得認證

開始學習
廣告