mysql中information_schema的columns,tables,KEY_COLUMN_USAGE等表字段含义说明

一、COLUMNS表：该表记录了所有数据库中的表和视图的列信息，包括列名、数据类型、默认值、是否为空等。
字段含义：

• TABLE_CATALOG：表的目录名

• TABLE_SCHEMA：表所属的数据库名

• TABLE_NAME：表的名称

• COLUMN_NAME：列的名称

• ORDINAL_POSITION：列的位置（在列表中的顺序）

• COLUMN_DEFAULT：列的默认值

• IS_NULLABLE：列是否可以为NULL

• DATA_TYPE：列的数据类型

• CHARACTER_MAXIMUM_LENGTH：列的最大字符长度（对于非数字类型）

• NUMERIC_PRECISION：列的数字精度（对于数字类型）

• NUMERIC_SCALE：列的数字小数位数（对于数字类型）

• DATETIME_PRECISION：列的日期时间精度（对于日期和时间类型）

• CHARACTER_SET_NAME：列字符集名称

• COLLATION_NAME：列的排序规则名称

• COLUMN_TYPE：列的类型

• COLUMN_KEY：列的关键属性（例如：PRI表示主键）

• EXTRA：额外的属性信息（例如：auto_increment）

• PRIVILEGES：操作列的权限

• COLUMN_COMMENT：列的注释信息

字段 含义
table_schema 表所有者（对于schema的名称）
table_name 表名
column_name 列名
ordinal_position 列标识号
column_default 列的默认值
is_nullable 列的为空性。如果列允许 null，那么该列返回 yes。否则，返回 no
data_type 系统提供的数据类型
character_maximum_length 以字符为单位的最大长度，适于二进制数据、字符数据，或者文本和图像数据。否则，返回 null。有关更多信息，请参见数据类型
character_octet_length 以字节为单位的最大长度，适于二进制数据、字符数据，或者文本和图像数据。否则，返回 nu
numeric_precision 近似数字数据、精确数字数据、整型数据或货币数据的精度。否则，返回 null
numeric_precision_radix 近似数字数据、精确数字数据、整型数据或货币数据的精度基数。否则，返回 null
numeric_scale 近似数字数据、精确数字数据、整数数据或货币数据的小数位数。否则，返回 null
datetime_precision datetime 及 sql-92 interval 数据类型的子类型代码。对于其它数据类型，返回 null
character_set_catalog 如果列是字符数据或 text 数据类型，那么返回 master，指明字符集所在的数据库。否则，返回 null
character_set_schema 如果列是字符数据或 text 数据类型，那么返回 dbo，指明字符集的所有者名称。否则，返回 null
character_set_name 如果该列是字符数据或 text 数据类型，那么为字符集返回唯一的名称。否则，返回 null
collation_catalog 如果列是字符数据或 text 数据类型，那么返回 master，指明在其中定义排序次序的数据库。否则此列为 null
collation_schema 返回 dbo，为字符数据或 text 数据类型指明排序次序的所有者。否则，返回 null
collation_name 如果列是字符数据或 text 数据类型，那么为排序次序返回唯一的名称。否则，返回 null。
domain_catalog 如果列是一种用户定义数据类型，那么该列是某个数据库名称，在该数据库名中创建了这种用户定义数据类型。否则，返回 null
domain_schema 如果列是一种用户定义数据类型，那么该列是这种用户定义数据类型的创建者。否则，返回 null
domain_name 如果列是一种用户定义数据类型，那么该列是这种用户定义数据类型的名称。否则，返回 NULL

mysql中information_schema.tables表字段说明

字段    含义
Table_catalog    数据表登记目录
Table_schema    数据表所属的数据库名
Table_name    表名称
Table_type    表类型[system view|base table|VIEW]
Engine    使用的数据库引擎[MyISAM|CSV|InnoDB]
Version    版本，默认值10
Row_format    行格式[Compact|Dynamic|Fixed]
Table_rows    表里所存多少行数据
Avg_row_length    平均行长度
Data_length    数据长度
Max_data_length    最大数据长度
Index_length    索引长度
Data_free    空间碎片
Auto_increment    做自增主键的自动增量当前值
Create_time    表的创建时间
Update_time    表的更新时间
Check_time    表的检查时间
Table_collation    表的字符校验编码集
Checksum    校验和

Create_options    创建选项
Table_comment    表的注释、备注

KEY_COLUMN_USAGE表字段

字段    说明
CONSTRAINT_CATALOG    约束所属的目录的名称。 该值始终为def
CONSTRAINT_SCHEMA    约束所属的结构（数据库）的名称
CONSTRAINT_NAME    约束名称
TABLE_CATALOG    包含索引的表所属的目录的名称。 该值始终为def
TABLE_SCHEMA    具有约束的表的名称
TABLE_NAME    具有约束的表的名称
COLUMN_NAME    具有约束的列的名称。如果约束是外键，那么这是外键的列，而不是外键引用的列
ORDINAL_POSITION    列在约束内的位置，而不是列在表中的位置。 列位置从1开始编号
POSITION_IN_UNIQUE_CONSTRAINT    NULL表示唯一和主键约束。 对于外键约束，此列是正在引用的表的键中的序号位置
REFERENCED_TABLE_SCHEMA    约束引用的结构（数据库）的名称
REFERENCED_TABLE_NAME    约束引用的表的名称
REFERENCED_COLUMN_NAME    约束引用的列的名称

--更新2024.5.21 14：00【MySql】【英文版】

SELECT sc.table_name AS '表名',t.table_comment  AS '表注释',
sc.ordinal_position AS '列序号',sc.column_name AS '列名',sc.column_comment AS '列注释',
sc.data_type AS '字段类型',sc.character_maximum_length AS '长度',
if (sc.is_nullable = 'no','Y','') AS '非空', 
sc.column_default AS '默认值',
if (sc.column_key = 'PRI','Y','') AS '主键',

sc.character_octet_length AS '字节',sc.numeric_scale AS '小数位',

sc.is_nullable AS '是否为空',sc.column_type AS '数据类型',
if (sc.column_key = 'UNI','Y','') AS '唯一',
if (sc.column_key = 'MUL','Y','') AS '索引',
sc.table_schema AS '数据库',sc.column_key AS '约束',
c.constraint_name AS '约束名',c.referenced_table_name AS '父表名称',c.referenced_column_name AS '父表字段',sc.character_set_name AS '字符集'
FROM information_schema.COLUMNS sc 
LEFT JOIN information_schema.TABLES t ON sc.table_name = t.table_name and sc.table_schema=t.table_schema
LEFT JOIN information_schema.KEY_COLUMN_USAGE c ON sc.table_name = c.table_name and sc.table_schema = c.table_schema and sc.column_name=c.column_name
WHERE sc.table_schema = 'alitest' 
AND t.table_schema  = 'hkcms_admin'

ORDER BY sc.table_name,sc.ordinal_position 

--is_nullable 列的为空性。如果列允许 null，那么该列返回 yes。否则，返回 no
--COLUMN_KEY 约束： PRI，主键约束；UNI：唯一约束；MUL：可以重复索引；


-- alitest为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
-- hkcms_admin为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称

 -- 更新2024.5.21 15：00【SQLServer 中文版】
 
 use DBAliCMS;

SELECT 
表名 = D.NAME,--CASE WHEN A.COLORDER=1 THEN D.NAME ELSE '' END,
表注释 = CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,'') ELSE '' END,列序号 = A.COLORDER,
列名 = A.NAME,列注释 = ISNULL(G.[VALUE],''),字段类型 = B.NAME,
长度 = COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'),非空 = CASE WHEN A.ISNULLABLE=1 THEN ''ELSE 'Y' END,默认值 = ISNULL(E.TEXT,''),
主键 = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN (
SELECT NAME FROM SYSINDEXES WHERE INDID IN(
SELECT INDID FROM SYSINDEXKEYS WHERE ID=A.ID AND COLID=A.COLID))) THEN 'Y' ELSE '' END,


字节 = A.LENGTH,小数位 = ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0),
标识 = CASE WHEN COLUMNPROPERTY(A.ID,A.NAME,'ISIDENTITY')=1 THEN 'Y' ELSE '' END

FROM SYSCOLUMNS A
LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE='U ' --AND D.NAME<>'DTPROPERTIES'
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID
LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id   
LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0

--where D.NAME='tbname'   --查询这个表
ORDER BY A.ID,A.COLORDER