MySQL 查看表结构命令的几种方式

一、简单描述表结构，字段类型

显示表结构，字段类型，主键，是否为空等属性，但不显示外键。

MySQL [db_video]> desc tab_video_tag;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| fld_ID      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| fld_VideoID | int(10) unsigned    | NO   | MUL | 0       |                |
| fld_TagName | varchar(20)         | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

二、查询表中列的注释信息

MySQL [db_video]> select * from information_schema.columns 
where table_schema = 'db_video'  #表所在数据库
and table_name = 'tab_video_tag' \G #你要查的表

*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db_game
              TABLE_NAME: tab_video_tag
             COLUMN_NAME: fld_ID
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 20
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint(20) unsigned
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
   GENERATION_EXPRESSION: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db_game
              TABLE_NAME: tab_video_tag
             COLUMN_NAME: fld_VideoID
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: 0
             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: MUL
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 视频ID
   GENERATION_EXPRESSION: 
*************************** 3. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db_game
              TABLE_NAME: tab_video_tag
             COLUMN_NAME: fld_TagName
        ORDINAL_POSITION: 3
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 20
  CHARACTER_OCTET_LENGTH: 60
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_general_ci
             COLUMN_TYPE: varchar(20)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 视频标签
   GENERATION_EXPRESSION: 
3 rows in set (0.00 sec)

三、只查询列名和注释

MySQL [db_video]> select  column_name, column_comment from information_schema.columns where table_schema ='db_video'  and table_name = 'tab_video_tag' ;
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| fld_ID      |                |
| fld_VideoID | 视频ID         |
| fld_TagName | 视频标签       |
+-------------+----------------+
3 rows in set (0.00 sec)

四、查看表的注释

MySQL [db_video]> select table_name,table_comment from information_schema.tables  where table_schema = 'db_game' and table_name ='tab_video_tag';
+---------------+---------------+
| table_name    | table_comment |
+---------------+---------------+
| tab_video_tag | 视频标签       |
+---------------+---------------+
1 row in set (0.00 sec)

五、查看表生成的 DDL

可以用 \G 来结尾使得结果更容易阅读，该命令把创建表的 DDL 显示出来，于是表结构、类型，外键，备注全部显示出来了

MySQL [db_video]> show create table tab_video_tag\G
*************************** 1. row ***************************
       Table: tab_video_tag
Create Table: CREATE TABLE `tab_video_tag` (
  `fld_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fld_VideoID` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '视频ID',
  `fld_TagName` varchar(20) NOT NULL COMMENT '视频标签',
  PRIMARY KEY (`fld_ID`),
  UNIQUE KEY `IDX_VideoID` (`fld_VideoID`,`fld_TagName`),
  CONSTRAINT `FK_VideoID` FOREIGN KEY (`fld_VideoID`) REFERENCES `tab_video_info` (`fld_ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=629 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MySQL 扩展阅读：

• MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.10 SHOW CREATE TABLE Statement

发表评论