-- 查看当前数据库中所有表的字段信息
SELECT
TABLE_NAME AS '表名',
COLUMN_NAME AS '字段名',
COLUMN_TYPE AS '数据类型',
IS_NULLABLE AS '允许为空',
COLUMN_DEFAULT AS '默认值',
COLUMN_COMMENT AS '字段说明',
EXTRA AS '额外信息',
COLUMN_KEY AS '索引类型'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() -- 当前数据库
ORDER BY TABLE_NAME, ORDINAL_POSITION;
SELECT
t.TABLE_NAME AS '表名',
t.TABLE_COMMENT AS '表注释',
c.COLUMN_NAME AS '字段名',
c.COLUMN_TYPE AS '数据类型',
c.DATA_TYPE AS '基础类型',
c.CHARACTER_SET_NAME AS '字符集',
c.COLLATION_NAME AS '排序规则',
c.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
c.NUMERIC_PRECISION AS '数字精度',
c.NUMERIC_SCALE AS '小数位数',
c.IS_NULLABLE AS '允许为空',
c.COLUMN_DEFAULT AS '默认值',
c.COLUMN_COMMENT AS '字段注释',
c.COLUMN_KEY AS '索引类型',
c.EXTRA AS '额外信息'
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;
-- 统计每个表的字段数量和类型分布
SELECT
TABLE_NAME AS '表名',
COUNT(*) AS '字段总数',
SUM(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS '可为空字段数',
SUM(CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END) AS '主键字段数',
SUM(CASE WHEN COLUMN_KEY = 'UNI' THEN 1 ELSE 0 END) AS '唯一键字段数',
SUM(CASE WHEN COLUMN_KEY = 'MUL' THEN 1 ELSE 0 END) AS '索引字段数',
GROUP_CONCAT(
CONCAT(COLUMN_NAME, '(', COLUMN_TYPE, ')')
ORDER BY ORDINAL_POSITION
SEPARATOR ', '
) AS '字段列表'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;
-- 查找所有字符集为 'utf8' 的字段及其表信息
SELECT
c.TABLE_SCHEMA AS '数据库名',
c.TABLE_NAME AS '表名',
c.COLUMN_NAME AS '字段名',
c.COLUMN_TYPE AS '数据类型',
c.CHARACTER_SET_NAME AS '字符集',
c.COLLATION_NAME AS '排序规则'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.CHARACTER_SET_NAME = 'utf8'
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION;
|