SHOW [FULL] COLUMNS FROM

SHOW [FULL] COLUMNS FROM <table_name> 语句用于以表格格式描述表或视图中的列。可选关键字 FULL 用于显示当前用户对该列的权限,以及表定义中的 comment

SHOW [FULL] FIELDS FROM <table_name>DESC <table_name>DESCRIBE <table_name>EXPLAIN <table_name> 语句都是 SHOW [FULL] COLUMNS FROM 的别名。

SHOW COLUMNS FROM - 图1

注意

DESC TABLE <table_name>DESCRIBE TABLE <table_name>EXPLAIN TABLE <table_name> 与上面的语句并不等价,它们是 DESC SELECT * FROM 的别名。

语法图

ShowColumnsFromStmt

SHOW COLUMNS FROM - 图2

TableName

SHOW COLUMNS FROM - 图3

ShowLikeOrWhere

SHOW COLUMNS FROM - 图4

  1. ShowColumnsFromStmt ::=
  2. "SHOW" "FULL"? ("COLUMNS" | "FIELDS") ("FROM" | "IN") TableName ( ("FROM" | "IN") SchemaName)? ShowLikeOrWhere?
  3. TableName ::=
  4. (Identifier ".")? Identifier
  5. ShowLikeOrWhere ::=
  6. "LIKE" SimpleExpr
  7. | "WHERE" Expression

示例

  1. CREATE VIEW v1 AS SELECT 1;
  1. Query OK, 0 rows affected (0.11 sec)
  1. SHOW COLUMNS FROM v1;
  1. +-------+-----------+------+------+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+-----------+------+------+---------+-------+
  4. | 1 | bigint(1) | YES | | NULL | |
  5. +-------+-----------+------+------+---------+-------+
  6. 1 row in set (0.00 sec)
  1. DESC v1;
  1. +-------+-----------+------+------+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+-----------+------+------+---------+-------+
  4. | 1 | bigint(1) | YES | | NULL | |
  5. +-------+-----------+------+------+---------+-------+
  6. 1 row in set (0.00 sec)
  1. DESCRIBE v1;
  1. +-------+-----------+------+------+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+-----------+------+------+---------+-------+
  4. | 1 | bigint(1) | YES | | NULL | |
  5. +-------+-----------+------+------+---------+-------+
  6. 1 row in set (0.00 sec)
  1. EXPLAIN v1;
  1. +-------+-----------+------+------+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+-----------+------+------+---------+-------+
  4. | 1 | bigint(1) | YES | | NULL | |
  5. +-------+-----------+------+------+---------+-------+
  6. 1 row in set (0.00 sec)
  1. SHOW FIELDS FROM v1;
  1. +-------+-----------+------+------+---------+-------+
  2. | Field | Type | Null | Key | Default | Extra |
  3. +-------+-----------+------+------+---------+-------+
  4. | 1 | bigint(1) | YES | | NULL | |
  5. +-------+-----------+------+------+---------+-------+
  6. 1 row in set (0.00 sec)
  1. SHOW FULL COLUMNS FROM v1
  1. +-------+-----------+-----------+------+------+---------+-------+---------------------------------+---------+
  2. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
  3. +-------+-----------+-----------+------+------+---------+-------+---------------------------------+---------+
  4. | 1 | bigint(1) | NULL | YES | | NULL | | select,insert,update,references | |
  5. +-------+-----------+-----------+------+------+---------+-------+---------------------------------+---------+
  6. 1 row in set (0.00 sec)
  1. SHOW FULL COLUMNS FROM mysql.user;
  1. +------------------------+---------------+-------------+------+------+---------+-------+---------------------------------+---------+
  2. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
  3. +------------------------+---------------+-------------+------+------+---------+-------+---------------------------------+---------+
  4. | Host | char(255) | utf8mb4_bin | NO | PRI | NULL | | select,insert,update,references | |
  5. | User | char(32) | utf8mb4_bin | NO | PRI | NULL | | select,insert,update,references | |
  6. | authentication_string | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | |
  7. | plugin | char(64) | utf8mb4_bin | YES | | NULL | | select,insert,update,references | |
  8. | Select_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  9. | Insert_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  10. | Update_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  11. | Delete_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  12. | Create_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  13. | Drop_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  14. | Process_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  15. | Grant_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  16. | References_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  17. | Alter_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  18. | Show_db_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  19. | Super_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  20. | Create_tmp_table_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  21. | Lock_tables_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  22. | Execute_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  23. | Create_view_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  24. | Show_view_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  25. | Create_routine_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  26. | Alter_routine_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  27. | Index_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  28. | Create_user_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  29. | Event_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  30. | Repl_slave_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  31. | Repl_client_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  32. | Trigger_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  33. | Create_role_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  34. | Drop_role_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  35. | Account_locked | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  36. | Shutdown_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  37. | Reload_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  38. | FILE_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  39. | Config_priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  40. | Create_Tablespace_Priv | enum('N','Y') | utf8mb4_bin | NO | | N | | select,insert,update,references | |
  41. | User_attributes | json | NULL | YES | | NULL | | select,insert,update,references | |
  42. +------------------------+---------------+-------------+------+------+---------+-------+---------------------------------+---------+
  43. 38 rows in set (0.00 sec)

MySQL 兼容性

SHOW [FULL] COLUMNS FROM 语句与 MySQL 完全兼容。如发现任何兼容性差异,请尝试 TiDB 支持资源

另请参阅