快捷導(dǎo)航

Mysql查詢表字段結(jié)構(gòu)注釋的方式

更新時(shí)間：2023年08月23日 17:14:53 作者：樂(lè)逍遙?

這篇文章主要介紹了Mysql查詢表字段結(jié)構(gòu)注釋的方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

查詢某表的字段及注釋

select  COLUMN_NAME 字段名,
column_comment 字段說(shuō)明,
column_type 字段類型,
column_key 約束 from information_schema.columns 
where table_schema = '數(shù)據(jù)庫(kù)名'
and table_name = '表名' ;

查詢某數(shù)據(jù)庫(kù)下所有表及表字段的注釋

SELECT
a.table_name 表名,
a.table_comment 表說(shuō)明,
b.COLUMN_NAME 字段名,
b.column_comment 字段說(shuō)明,
b.column_type 字段類型,
b.column_key 約束
FROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = '數(shù)據(jù)庫(kù)名'
ORDER BY
a.table_name

查詢某數(shù)據(jù)庫(kù)下的所有表名表注釋

SELECT
table_name 表名,
table_comment 表說(shuō)明
FROM
information_schema.TABLES
WHERE
table_schema = '數(shù)據(jù)庫(kù)名'
ORDER BY
table_name

mysql 查詢字段名注釋字段類型長(zhǎng)度精度

SELECT
    COLUMN_COMMENT 注釋,
    COLUMN_NAME 字段名稱,
   data_type   字段類型,
     COLUMN_TYPE 字段類型2,
    (
CASE
    WHEN data_type = 'float' 
    OR data_type = 'double' 
    OR data_type = 'TINYINT' 
    OR data_type = 'SMALLINT' 
    OR data_type = 'MEDIUMINT' 
    OR data_type = 'INT' 
    OR data_type = 'INTEGER' 
    OR data_type = 'decimal' 
    OR data_type = 'bigint' THEN
    '數(shù)字' ELSE '文本' 
END 
) AS '數(shù)據(jù)類型',
    (
    CASE
            WHEN data_type = 'DECIMAL' THEN
            CONCAT( NUMERIC_PRECISION, '/', NUMERIC_SCALE ) 
            WHEN data_type = 'float' 
            OR data_type = 'double' 
            OR data_type = 'TINYINT' 
            OR data_type = 'SMALLINT' 
            OR data_type = 'MEDIUMINT' 
            OR data_type = 'INT' 
            OR data_type = 'INTEGER' 
            OR data_type = 'decimal' 
            OR data_type = 'bigint' THEN
                NUMERIC_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH 
            END 
            ) AS '數(shù)據(jù)長(zhǎng)度/精度' 
        FROM
            INFORMATION_SCHEMA.COLUMNS 
        WHERE
        table_schema = 'db_name'  #修改為數(shù)據(jù)庫(kù)名
AND table_name = 'table_name'#修改為表名