MySQL：information_schema 与特殊列名

工作中要动态删除很久以前的数据表（数据按天分表，比如 runningdataset_2022_7_25），第一步是查出某个数据库里有哪些表。研究了一下 information_schema。

information_schema

MySQL 自带的系统库，存的是「关于数据库的数据」：有哪些库、哪些表、每张表有哪些列。其中 TABLES 表记录了所有表的信息：

最常用的两列：

• TABLE_SCHEMA：表所在的数据库名；
• TABLE_NAME：表名。

还有 TABLE_ROWS（大致行数）、DATA_LENGTH（数据大小）、CREATE_TIME（创建时间）等，查哪张表占空间大很有用。

查指定数据库里的所有表

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'secote';

WHERE 里可以用 LIKE 模糊查找，只要 runningdataset_ 开头的表：

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'secote' AND TABLE_NAME LIKE 'runningdataset\_%';

注意 LIKE 里的 _ 是通配符，匹配任意一个字符；% 匹配任意多个。原文写的 'runningdataset_%' 也会匹配到 runningdatasetX... 这样的表，要精确匹配下划线，就写成 \_。

查某张表在哪个库

SELECT TABLE_SCHEMA FROM information_schema.TABLES
WHERE TABLE_NAME = 'runningdataset_2022_7_25';

删除过期的表

查到表名后，在 C# 里拼出 DROP TABLE 语句执行：

// 查出所有按天分的表
var tables = new List<string>();
using (var cmd = new MySqlCommand(
    "SELECT TABLE_NAME FROM information_schema.TABLES " +
    "WHERE TABLE_SCHEMA = @db AND TABLE_NAME LIKE 'runningdataset\\_%'", conn))
{
    cmd.Parameters.AddWithValue("@db", "secote");
    using var reader = cmd.ExecuteReader();
    while (reader.Read()) tables.Add(reader.GetString(0));
}

DateTime limit = DateTime.Today.AddDays(-90);
foreach (string name in tables)
{
    // 从表名里解析日期：runningdataset_2022_7_25
    string[] p = name.Split('_');
    var date = new DateTime(int.Parse(p[1]), int.Parse(p[2]), int.Parse(p[3]));
    if (date < limit)
        new MySqlCommand($"DROP TABLE `{name}`", conn).ExecuteNonQuery();
}

表名不能用参数传，只能拼字符串，所以表名必须来自上面查到的结果，而不是外部输入。

列名里有特殊字符

我把列名设成了 0:00-1:00 这种样子（一天 24 个时段各一列），执行 SQL 时报 1064 错误（语法错误）。MySQL 把冒号、横杠当成了运算符。

解决：用反引号 ` 把列名括起来（键盘上 Tab 上面、数字 1 左边那个键，英文输入状态下）：

SELECT 0:00-1:00 FROM data;     -- 报错
SELECT `0:00-1:00` FROM data;   -- 正常

表名、库名同理，和 MySQL 关键字重名的（比如 order、desc、key）也要用反引号。

原文的列名里用的是中文全角冒号「：」。全角符号在 SQL 里很容易和半角混淆，查起来非常费劲，列名最好只用英文字母、数字和下划线，比如 h00_01，显示用的中文标题放在程序里映射。

延伸

• 比按天分表更好的做法：分区表。按天分表要自己拼表名、跨天查询要 UNION 多张表。MySQL 的分区表（PARTITION BY RANGE）对外是一张表，内部按日期分成多个分区，查询照常写 WHERE time BETWEEN ...；清理时 ALTER TABLE t DROP PARTITION p20220725，和删表一样快。
• 数据量不大时：一张表加上时间列的索引，DELETE FROM t WHERE time < ... 分批删除（每次 LIMIT 10000）也完全够用。
• 计划任务：MySQL 自带事件调度器（CREATE EVENT ... ON SCHEDULE EVERY 1 DAY），可以在数据库里定时清理，不依赖上位机程序。
• 数据目录和安装问题见 MySQL：安装时启动超时，以及更换数据目录。