mysql表类型查询示例详解_Mysql

普通表

select 
    table_schema as database_name,
    table_name
from 
    information_schema.tables
where 
    table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and table_type = 'base table'
    and table_name not in (
        select distinct table_name 
        from information_schema.partitions 
        where partition_name is not null
    )
order by 
    table_schema, table_name;

分区表

select 
    p.table_schema as database_name,
    p.table_name,
    group_concat(p.partition_name order by p.partition_ordinal_position) as partitions,
    p.partition_method,
    p.partition_expression
from 
    information_schema.partitions p
where 
    p.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and p.partition_name is not null
group by 
    p.table_schema, p.table_name, p.partition_method, p.partition_expression
order by 
    p.table_schema, p.table_name;

区分表

select 
    t.table_schema as database_name,
    t.table_name,
    case 
        when p.table_name is null then '普通表'
        else '分区表'
    end as table_type,
    p.partition_method,
    p.partition_expression
from 
    information_schema.tables t
left join (
    select distinct 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    from 
        information_schema.partitions 
    where 
        partition_name is not null
) p on t.table_schema = p.table_schema and t.table_name = p.table_name
where 
    t.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and t.table_type = 'base table'
order by 
    t.table_schema, t.table_name;

查出数据量

select 
    t.table_schema as '数据库名',
    t.table_name as '表名',
    case 
        when p.table_name is null then '普通表'
        else concat('分区表(', p.partition_method, ')')
    end as '表类型',
    t.table_rows as '数据行数(估算)',
    concat(round(t.data_length / (1024 * 1024), 2), ' mb') as '数据大小',
    concat(round(t.index_length / (1024 * 1024), 2), ' mb') as '索引大小',
    concat(round((t.data_length + t.index_length) / (1024 * 1024), 2), ' mb') as '总大小',
    p.partition_expression as '分区键'
from 
    information_schema.tables t
left join (
    select distinct 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    from 
        information_schema.partitions 
    where 
        partition_name is not null
) p on t.table_schema = p.table_schema and t.table_name = p.table_name
where 
    t.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and t.table_type = 'base table'
order by 
    t.table_schema, 
    case when p.table_name is null then 0 else 1 end,  -- 普通表在前
    t.table_name;
select 
    t.table_schema as '数据库',
    t.table_name as '表名',
    case 
        when p.partition_method is null then '普通表'
        else concat('分区表(', p.partition_method, ')')
    end as '表类型',
    t.table_rows as '估算行数',
    concat(round(t.data_length/1024/1024, 2), ' mb') as '数据大小',
    p.partition_expression as '分区键'
from 
    information_schema.tables t
left join (
    select 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    from 
        information_schema.partitions
    where 
        partition_name is not null
    group by 
        table_schema, table_name, partition_method, partition_expression
) p on t.table_schema = p.table_schema and t.table_name = p.table_name
where 
    t.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and t.table_type = 'base table'
order by 
    t.table_schema, t.table_name;

查出表行数

select 
    t.table_schema as '数据库',
    t.table_name as '表名',
    case 
        when p.partition_method is null then '普通表'
        else concat('分区表(', p.partition_method, ')')
    end as '表类型',
    t.table_rows as '估算行数',
    p.partition_expression as '分区键'
from 
    information_schema.tables t
left join (
    select distinct 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    from 
        information_schema.partitions
    where 
        partition_name is not null
) p on t.table_schema = p.table_schema and t.table_name = p.table_name
where 
    t.table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
    and t.table_type = 'base table'
order by 
    t.table_schema, t.table_name;

到此这篇关于mysql表类型查询的文章就介绍到这了,更多相关mysql表类型查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！