查询数据库中所有表名
SELECT
table_name
FROM
information_schema.tables
WHERE table_schema = 'csdb'
AND table_type = 'base table' ;
获取表字段信息
select
COLUMN_NAME `FIELD`,
data_type `TYPE`,
ifnull(
CHARACTER_MAXIMUM_LENGTH,
concat(
NUMERIC_PRECISION,
if(
NUMERIC_SCALE = 0,
'',
concat(',', NUMERIC_SCALE)
)
)
) `LENGTH`,
if (IS_NULLABLE = 'YES', '是', '否') NULLABLE,
COLUMN_COMMENT `COMMENTS`,
if (COLUMN_KEY = 'PRI', '是', '否') PRIMARYKEY,
column_default `DEFAULT`from
INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'table_name' ;
-- 备用SELECT
t.table_name AS 表名
, t2.table_comment AS 表注释
, t.column_name 字段名
, IF(t.data_type IN ('date', 'datetime', 'mediumtext', 'longtext'), t.data_type, CONCAT(
t.data_type,
'(',
IFNULL(
t.character_maximum_length,
CONCAT(t.numeric_precision, IF(t.numeric_scale = 0, '', CONCAT(',', t.numeric_scale)))
),
')'
)) 字段类型
, t.column_comment 字段注释
-- , t.data_type-- , t.character_maximum_length-- , t.numeric_precision-- , t.numeric_scaleFROM information_schema.columns t
LEFT JOIN information_schema.tables t2
ON t.table_name = t2.table_name
AND t2.table_schema = 'drgdb'
AND t2.table_type = 'base table'WHERE t.table_name IN ('control_kc24');
获取索引信息
select
TABLE_NAME as '表名',
INDEX_NAME as '索引名',
(
case
NON_UNIQUE
when 0
then '否'
when 1
then '是'
else ''
end
) as '是否唯一索引',
(
case
when count(COLUMN_NAME) > 1
then '是'
else '否'
end
) as '是否组合索引',
group_concat(COLUMN_NAME) as '索引字段'from
information_schema.statistics
where table_schema = 'db_name'group by TABLE_NAME,
INDEX_NAME ;