MySQL/Oracle 添加/查看表注释和字段注释

╰+哭是因爲堅強的太久メ 2021-12-18 06:23 516阅读 0赞

# 一、Oracle 添加/查看表注释和字段注释 #

## （1）添加表注释和字段注释 ##

-- ----------------------------
    -- Table structure for GW_CONCURRENT
    -- ----------------------------
    CREATE TABLE "GW_CONCURRENT"
    (
        "ID"             NUMBER(19)         NOT NULL,
        "API_GROUP_ID"   NUMBER(19)         NOT NULL,
        "API_ID"         NUMBER(19)         NOT NULL,
        "CLUSTER_ID"     NUMBER(19)         NOT NULL,
        "CREATE_TIME"    TIMESTAMP(6)       NULL,
        "MODIFY_TIME"    TIMESTAMP(6)       NULL,
        "QUEUE"          NUMBER(10)         NULL,
        "REMARKS"        VARCHAR2(255 CHAR) NULL,
        "STATUS"         VARCHAR2(255 CHAR) NOT NULL
    )
        LOGGING
        NOCOMPRESS
        NOCACHE
    ;
    COMMENT ON TABLE "GW_CONCURRENT" IS '并发控制表';
    COMMENT ON COLUMN "GW_CONCURRENT"."ID" IS 'id';
    COMMENT ON COLUMN "GW_CONCURRENT"."CLUSTER_ID" IS '集群id';
    COMMENT ON COLUMN "GW_CONCURRENT"."REMARKS" IS '简介';
    COMMENT ON COLUMN "GW_CONCURRENT"."CREATE_TIME" IS '创建时间';
    COMMENT ON COLUMN "GW_CONCURRENT"."MODIFY_TIME" IS '更新时间';
    COMMENT ON COLUMN "GW_CONCURRENT"."STATUS" IS '状态';
    COMMENT ON COLUMN "GW_CONCURRENT"."API_ID" IS 'apiID';
    COMMENT ON COLUMN "GW_CONCURRENT"."API_GROUP_ID" IS 'api分组ID';

## （2）查看表注释和字段注释 ##

获取表注释。获得 table_name,table_type,comments。

select * from user_tab_comments; 
    
    获取字段注释。获得 table_name,column_name,comments。

select * from user_col_comments;

![1481295-20190702100326448-1869786024.png][]

# 二、MySQL 添加/查看表注释和字段注释 #

## （1）添加表注释和字段注释 ##

-- ----------------------------
    -- Table structure for `gw_concurrent`
    -- ----------------------------
    CREATE TABLE `gw_concurrent`
    (
        `id`             int(11)    NOT NULL AUTO_INCREMENT COMMENT '自增ID',
        `cluster_id`     int(11)    NOT NULL COMMENT '集群ID',
        `api_group_id`   int(11)    NOT NULL COMMENT 'api分组ID',
        `api_id`         int(11)    NOT NULL COMMENT 'apiID',
        `max_concurrent` int(11)    NOT NULL COMMENT '最大并发',
        `queue`          int(11)    NOT NULL COMMENT '队列大小',
        `status`         varchar(5) NOT NULL COMMENT '状态',
        `remarks`        varchar(255)    DEFAULT NULL COMMENT '简介',
        `create_time`    timestamp  NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        `modify_time`    timestamp  NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (`id`)
    ) ENGINE = InnoDB
      DEFAULT CHARSET = utf8 COMMENT ='并发控制表';

## （2）查看表注释和字段注释 ##

-- 表注释
      select * from information_schema.TABLES where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' 
      -- 字段注释
      show  full  columns  from  test1; 
       --在元数据的表里面看 
      select * from information_schema.COLUMNS where TABLE_SCHEMA='数据库名' and TABLE_NAME='表名' ;

转载于:https://www.cnblogs.com/chaos-li/p/11118696.html

[1481295-20190702100326448-1869786024.png]: /images/20211218/a15c9fd43fb2461089562ca24aa58d6a.png