mysql 数据库字段备注读取_mysql select column as comment-优快云博客

本文链接：https://blog.youkuaiyun.com/maxiaojingabc/article/details/79749156

本文介绍如何使用SQL语句查询MySQL数据库的元数据，包括读取表的字段信息、注释及表结构等，适用于数据库管理员及开发人员进行数据库维护和优化工作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、读取表的字段
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='mammothcode';

SELECT column_name,column_comment FROM information_schema. COLUMNS

WHERE table_schema = 'staq' AND table_name = 'jfjy_user_exam' AND table_name = 'jfjy_users_group'

2、读取指定的表、字段

SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name='t_adminuser' AND table_schema='mammothcode'

SELECT COLUMN_NAME 列名, DATA_TYPE 字段类型,COLUMN_COMMENT 字段注释
FROM INFORMATION_SCHEMA. COLUMNS WHERE

table_name = 'jfjy_user_exam' ##表名AND table_schema = 'staq' ##数据库名 AND column_name LIKE 'uid' ##字段名

3、一次性读取全部的字段

SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`='mammothcode'

4、查看表的详细信息
SELECT
    column_name AS '列名',
    data_type   AS '数据类型',
    character_maximum_length AS '字符长度',
    numeric_precision AS '数字长度',
    numeric_scale AS '小数位数',
    is_nullable AS '是否允许非空',
    CASE
        WHEN extra = 'auto_increment' THEN 1
        ELSE 0
    END AS '是否自增',
    column_default AS '默认值',
    column_comment AS '备注'
FROM
    Information_schema.columns
WHERE
    table_Name='test_table';