快捷導(dǎo)航

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

更新時間：2023年08月23日 17:14:53 作者：樂逍遙?

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

查詢某表的字段及注釋

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

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

SELECT
a.table_name 表名,
a.table_comment 表說明,
b.COLUMN_NAME 字段名,
b.column_comment 字段說明,
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ù)庫名'
ORDER BY
a.table_name

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

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

mysql 查詢字段名注釋字段類型長度精度

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ù)長度/精度' 
        FROM
            INFORMATION_SCHEMA.COLUMNS 
        WHERE
        table_schema = 'db_name'  #修改為數(shù)據(jù)庫名
AND table_name = 'table_name'#修改為表名