COLUMNS

• 该表提供查询表中的列(字段)信息

• 该表是InnoDB引擎临时表

• 表定义语句

CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT '',
  `GENERATION_EXPRESSION` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 该表提供在表中的列信息，部分字段含义如下：

• TABLE_SCHEMA：显示列对应的库名
• TABLE_NAME：显示列对应的表名
• COLUMN_NAME：显示列名称
• ORDINAL_POSITION：显示表列在表中的列创建顺序
• COLUMN_DEFAULT：显示表列默认值
• IS_NULLABLE：显示表列是否带有NULL属性
• DATA_TYPE：显示列的数据类型，不包含列的数据类型定义的额外属性
• CHARACTER_MAXIMUM_LENGTH：显示字符类型字段的定义长度
• CHARACTER_OCTET_LENGTH：显示字符类型字段的定义长度对应的字节数，如果是单字节字符集，则该字段值与CHARACTER_MAXIMUM_LENGTH字段值相同(多字节字符集除外)
• NUMERIC_PRECISION：显示数字类型字段的精度（定义长度）
• NUMERIC_SCALE：显示数字类型字段的标度（小数位数）
• DATETIME_PRECISION：显示时间类型字段的精度（5.6版本之后，datetime时间类型字段在存储引擎层存储时都当作int类型处理，但存储时会比timestamp多一个字节）
• CHARACTER_SET_NAME：显示表列的字符集，如果使用SHOW FULL COLUMNS语句查看，那么可以从结果集的Collation列中看到字符集类型，例如：Collation值为latin1_swedish_ci，则该字符集就是latin1
• COLLATION_NAME：显示列的校对规则
• COLUMN_TYPE：显示表列的定义类型，包含列数据类型定义的额外属性（在show columns语句的结果集中该字段信息显示在Type列），例如：varchar(32)，该字段为 "MySQL extension" 列
• COLUMN_KEY：如果字段是索引列，则这里会显示出索引的类型
• EXTRA：显示生成列的类型，有效值为：VIRTUAL GENERATED或VIRTUAL STORED，该字段为 "MySQL extension" 列
• PRIVILEGES：显示列的可授予权限列表，未列出的权限无法使用grant语句授予
• COLUMN_COMMENT：显示列的注释信息
• GENERATION_EXPRESSION：显示生成列的计算表达式，该字段为 "MySQL extension" 列

• 表记录完整字段内容示例

admin@localhost : information_schema 05:32:07> select * from COLUMNS where TABLE_NAME='sbtest1' limit 1\G;
*************************** 1. row ***************************
          TABLE_CATALOG: def
            TABLE_SCHEMA: sbtest
              TABLE_NAME: sbtest1
            COLUMN_NAME: id
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
            IS_NULLABLE: NO
              DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
      NUMERIC_PRECISION: 10
          NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
            COLUMN_TYPE: int(10) unsigned
              COLUMN_KEY: PRI
                  EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
  GENERATION_EXPRESSION: 
1 row in set (0.00 sec)

• 查询表列信息可以使用select语句与show columns语句

# select语句
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']

## 例如
root@localhost : information_schema 12:42:20> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT  FROM INFORMATION_SCHEMA.COLUMNS where table_schema='sbtest' and table_name='sbtest1';
+-------------+-----------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+-------------+-----------+-------------+----------------+
| id          | int      | NO          | NULL          |
| k          | int      | NO          | 0              |
| c          | char      | NO          |                |
| pad        | char      | NO          |                |
+-------------+-----------+-------------+----------------+
4 rows in set (0.00 sec)

# show columns语句
SHOW [full] COLUMNS
  FROM tbl_name
  [FROM db_name]
  [LIKE 'wild']

## 例如
root@localhost : information_schema 12:42:34> show columns from sbtest.sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |
| k    | int(10) unsigned | NO  | MUL | 0      |                |
| c    | char(120)        | NO  | MUL |        |                |
| pad  | char(60)        | NO  |    |        |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

root@localhost : information_schema 12:44:46> show columns from sbtest.sbtest1 where Field like 'id%';
+-------+------------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO  | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

# 另外，还可以使用desc语句和show create table tb_name;语句
desc db_name.tb_name column_name;
desc db_name.tb_name;
show create table tb_name;

• PS：查询某个表所有的字段的可授予权限，还可以使用show full columns语句，如下（Privileges列内容展示了对应的字段所有的可授予权限）

root@localhost : information_schema 09:29:07> show full columns from sbtest.sbtest1;
+-------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type            | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(10) unsigned | NULL      | NO  | PRI | NULL    | auto_increment | select,insert,update,references |        |
| k    | int(10) unsigned | NULL      | NO  | MUL | 0      |                | select,insert,update,references |        |
| c    | char(120)        | utf8_bin  | NO  | MUL |        |                | select,insert,update,references |        |
| pad  | char(60)        | utf8_bin  | NO  |    |        |                | select,insert,update,references |        |
+-------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
4 rows in set (0.00 sec)

---

上一篇：COLLATION_CHARACTER_SET_APPLICABILITY表 |下一篇：COLUMN_PRIVILEGES表