mysql - SHOW FIELDS 查询可以使用 ORDER BY 吗？

Question

docs不要提到 ORDER BY，我没有运气来表达这个查询:

SHOW FIELDS FROM `TB_Main`  WHERE Type = 'mediumtext' OR Type = 'bit(1)' ORDER BY Field;

我必须在脚本中进行排序吗？

Answer 1

您可以改为直接查询信息架构表。作为manual says :

The following statements are nearly equivalent:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']

SHOW COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']

您可以将 ORDER BY 子句应用于第一种类型的语句，因为它是一个普通的 SELECT 命令。

因此，代替您的 SHOW 命令，您可以:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
      TABLE_NAME = 'TB_Main'
  AND COLUMN_TYPE IN ('mediumtext', 'bit(1)')
ORDER BY COLUMN_NAME;