MySQL 常用运维统计语句

这些 SQL 是在几次线上排查问题和做数据库迁移时积累的。不常用但每次要用都想不起来，整理到一起备查。

---

一、查询数据磁盘占用量

查看所有数据库容量大小

SELECT
    table_schema AS '数据库',
    SUM(table_rows) AS '记录数',
    SUM(TRUNCATE(data_length / 1024 / 1024, 2)) AS '数据容量(MB)',
    SUM(TRUNCATE(index_length / 1024 / 1024, 2)) AS '索引容量(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

查看所有数据库各表容量

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE(data_length / 1024 / 1024, 2) AS '数据容量(MB)',
    TRUNCATE(index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM information_schema.tables
ORDER BY data_length DESC, index_length DESC;

查看指定数据库容量

SELECT
    table_schema AS '数据库',
    SUM(table_rows) AS '记录数',
    SUM(TRUNCATE(data_length / 1024 / 1024, 2)) AS '数据容量(MB)',
    SUM(TRUNCATE(index_length / 1024 / 1024, 2)) AS '索引容量(MB)'
FROM information_schema.tables
WHERE table_schema = '库名';

---

二、按时间段统计数据

统计功能用得比较多，information_schema 里的时间函数用起来不太直觉，这里整理了常用的几个：

-- 今天
SELECT * FROM 表名 WHERE TO_DAYS(时间字段) = TO_DAYS(NOW());

-- 昨天
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) <= 1;

-- 最近 7 天
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段);

-- 本周
SELECT * FROM 表名
WHERE YEARWEEK(DATE_FORMAT(时间字段, '%Y-%m-%d')) = YEARWEEK(NOW());

-- 上周
SELECT * FROM 表名
WHERE YEARWEEK(DATE_FORMAT(时间字段, '%Y-%m-%d')) = YEARWEEK(NOW()) - 1;

-- 本月
SELECT * FROM 表名
WHERE DATE_FORMAT(时间字段, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 上月
SELECT * FROM 表名
WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(时间字段, '%Y%m')) = 1;

-- 本季度
SELECT * FROM 表名 WHERE QUARTER(时间字段) = QUARTER(NOW());

-- 上季度
SELECT * FROM 表名
WHERE QUARTER(时间字段) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER));

-- 本年
SELECT * FROM 表名 WHERE YEAR(时间字段) = YEAR(NOW());

-- 去年
SELECT * FROM 表名 WHERE YEAR(时间字段) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));

---

三、批量修改字符集

从 utf8 迁移到 utf8mb4 时用到，三个层级都要改：库 → 表 → 字段。

修改数据库字符集

ALTER DATABASE `库名` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

批量修改表字符集

执行以下语句，把查询结果复制出来再执行（遇到错误跳过即可）：

SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
    '` CHARACTER SET = utf8mb4, COLLATE = utf8mb4_general_ci;'
) AS sql_command
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '库名';

批量修改字段字符集

SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
    '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
    IF(IS_NULLABLE = 'YES',
       CONCAT('NULL DEFAULT ', IF(COLUMN_DEFAULT IS NULL, 'NULL ', CONCAT('"', COLUMN_DEFAULT, '" '))),
       'NOT NULL '
    ),
    'COMMENT \'', COLUMN_COMMENT, '\';'
) AS sql_command
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '库名'
  AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'char');