MySQL下读取表中字段的说明和备注信息_MySQL



bitsCN.com 在MySQL下运行完下面这个建表语句后。 如何从数据字典中，检索出这个表的字段的相关信息？

DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table(
Test_ID int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键(自增长)’,
Test_Key varchar(10) NOT NULL COMMENT ‘种类’,
Test_Value varchar(20) NOT NULL COMMENT ’数值’,
Test_Type int NOT NULL COMMENT ‘内部类型’,
Test_BelongTo int COMMENT ‘从属关系’ ,
Test_Grade int DEFAULT 1 COMMENT ’等级’,
Test_Remark varchar(50) COMMENT ‘备注’,
Test_Visible bit DEFAULT 1 COMMENT ‘是否可见’
)
COMMENT = ‘测试表’;



答案是：

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’;
bitsCN.com