• 设为首页
  • 收藏本站
  • 积分充值
  • VIP赞助
  • 手机版
  • 微博
  • 微信
    微信公众号 添加方式：
    1:搜索微信号（888888）
    2:扫描左侧二维码
  • 登录 立即注册
    • 切换风格
    快捷导航
    福建二哥 › 门户 › 查看主题

    mysql表类型查询示例详解

    发布者: 网神之王 | 发布时间: 2025-6-14 14:24| 查看数: 153| 评论数: 0|帖子模式

    普通表
    1. SELECT
    2.     table_schema AS database_name,
    3.     table_name
    4. FROM
    5.     information_schema.tables
    6. WHERE
    7.     table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    8.     AND table_type = 'BASE TABLE'
    9.     AND table_name NOT IN (
    10.         SELECT DISTINCT table_name
    11.         FROM information_schema.partitions
    12.         WHERE partition_name IS NOT NULL
    13.     )
    14. ORDER BY
    15.     table_schema, table_name;
    复制代码
    分区表
    1. SELECT
    2.     p.table_schema AS database_name,
    3.     p.table_name,
    4.     GROUP_CONCAT(p.partition_name ORDER BY p.partition_ordinal_position) AS partitions,
    5.     p.partition_method,
    6.     p.partition_expression
    7. FROM
    8.     information_schema.partitions p
    9. WHERE
    10.     p.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    11.     AND p.partition_name IS NOT NULL
    12. GROUP BY
    13.     p.table_schema, p.table_name, p.partition_method, p.partition_expression
    14. ORDER BY
    15.     p.table_schema, p.table_name;
    复制代码
    区分表
    1. SELECT
    2.     t.table_schema AS database_name,
    3.     t.table_name,
    4.     CASE
    5.         WHEN p.table_name IS NULL THEN '普通表'
    6.         ELSE '分区表'
    7.     END AS table_type,
    8.     p.partition_method,
    9.     p.partition_expression
    10. FROM
    11.     information_schema.tables t
    12. LEFT JOIN (
    13.     SELECT DISTINCT
    14.         table_schema,
    15.         table_name,
    16.         partition_method,
    17.         partition_expression
    18.     FROM
    19.         information_schema.partitions
    20.     WHERE
    21.         partition_name IS NOT NULL
    22. ) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
    23. WHERE
    24.     t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    25.     AND t.table_type = 'BASE TABLE'
    26. ORDER BY
    27.     t.table_schema, t.table_name;
    复制代码
    查出数据量
    1. SELECT
    2.     t.table_schema AS '数据库名',
    3.     t.table_name AS '表名',
    4.     CASE
    5.         WHEN p.table_name IS NULL THEN '普通表'
    6.         ELSE CONCAT('分区表(', p.partition_method, ')')
    7.     END AS '表类型',
    8.     t.table_rows AS '数据行数(估算)',
    9.     CONCAT(ROUND(t.data_length / (1024 * 1024), 2), ' MB') AS '数据大小',
    10.     CONCAT(ROUND(t.index_length / (1024 * 1024), 2), ' MB') AS '索引大小',
    11.     CONCAT(ROUND((t.data_length + t.index_length) / (1024 * 1024), 2), ' MB') AS '总大小',
    12.     p.partition_expression AS '分区键'
    13. FROM
    14.     information_schema.tables t
    15. LEFT JOIN (
    16.     SELECT DISTINCT
    17.         table_schema,
    18.         table_name,
    19.         partition_method,
    20.         partition_expression
    21.     FROM
    22.         information_schema.partitions
    23.     WHERE
    24.         partition_name IS NOT NULL
    25. ) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
    26. WHERE
    27.     t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    28.     AND t.table_type = 'BASE TABLE'
    29. ORDER BY
    30.     t.table_schema,
    31.     CASE WHEN p.table_name IS NULL THEN 0 ELSE 1 END,  -- 普通表在前
    32.     t.table_name;
    33. SELECT
    34.     t.table_schema AS '数据库',
    35.     t.table_name AS '表名',
    36.     CASE
    37.         WHEN p.partition_method IS NULL THEN '普通表'
    38.         ELSE CONCAT('分区表(', p.partition_method, ')')
    39.     END AS '表类型',
    40.     t.table_rows AS '估算行数',
    41.     CONCAT(ROUND(t.data_length/1024/1024, 2), ' MB') AS '数据大小',
    42.     p.partition_expression AS '分区键'
    43. FROM
    44.     information_schema.tables t
    45. LEFT JOIN (
    46.     SELECT
    47.         table_schema,
    48.         table_name,
    49.         partition_method,
    50.         partition_expression
    51.     FROM
    52.         information_schema.partitions
    53.     WHERE
    54.         partition_name IS NOT NULL
    55.     GROUP BY
    56.         table_schema, table_name, partition_method, partition_expression
    57. ) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
    58. WHERE
    59.     t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    60.     AND t.table_type = 'BASE TABLE'
    61. ORDER BY
    62.     t.table_schema, t.table_name;
    复制代码
    查出表行数
    1. SELECT
    2.     t.table_schema AS '数据库',
    3.     t.table_name AS '表名',
    4.     CASE
    5.         WHEN p.partition_method IS NULL THEN '普通表'
    6.         ELSE CONCAT('分区表(', p.partition_method, ')')
    7.     END AS '表类型',
    8.     t.table_rows AS '估算行数',
    9.     p.partition_expression AS '分区键'
    10. FROM
    11.     information_schema.tables t
    12. LEFT JOIN (
    13.     SELECT DISTINCT
    14.         table_schema,
    15.         table_name,
    16.         partition_method,
    17.         partition_expression
    18.     FROM
    19.         information_schema.partitions
    20.     WHERE
    21.         partition_name IS NOT NULL
    22. ) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
    23. WHERE
    24.     t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    25.     AND t.table_type = 'BASE TABLE'
    26. ORDER BY
    27.     t.table_schema, t.table_name;
    复制代码
    到此这篇关于mysql表类型查询的文章就介绍到这了,更多相关mysql表类型查询内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

    来源：https://www.jb51.net/database/3398090u0.htm
    免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

    最新评论

    新人须知

    常见问题

    维权申诉

    关注微博

    扫描微信

    关于合作

    通讯联系

    QQ Archiver 手机版 小黑屋 福建二哥 ( 闽ICP备2022004717号|闽公网安备35052402000345号 )

    Powered by Discuz! X3.5 © 2001-2023

    快速回复 返回顶部 返回列表