Sắp xếp thứ tự cột hiển thị bằng SHOW COLUMNS trong MySQL

Để sắp xếp thứ tự SHOW COLUMNS, truy vấn INFORMATION_SCHEMA.COLUMNS với ORDER BY.

Cú pháp:

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

Demo: Bảng OrderByColumnName với 6 cột Student*.

CASE 1: Giảm dần (DESC):

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)

CASE 2: Tăng dần (mặc định, không cần 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)