嘿，刚接触MySQL不用慌，我来一步步帮你搞定这个问题——先解决触发器自动建表的需求，再针对你实际的监测站场景分析存储方案的优劣～

MySQL的触发器本身不能直接执行动态SQL（比如根据变量拼接CREATE TABLE语句），所以我们需要先写一个存储过程处理动态建表逻辑，再用触发器调用这个存储过程。

1. 创建建表存储过程 #

这个存储过程接收新增行的id和number参数，动态生成包含time主键和number1到numberZ列的表：

DELIMITER //
CREATE PROCEDURE create_numbers_table(IN p_id INT, IN p_number INT)
BEGIN
    -- 初始化列定义，time设为主键，用DATETIME适配时间存储，也可替换为TIMESTAMP
    DECLARE col_defs TEXT DEFAULT '`time` DATETIME PRIMARY KEY';
    DECLARE i INT DEFAULT 1;

    -- 循环生成number1到numberZ的列，这里假设温度用FLOAT类型，可按需改为DECIMAL等
    WHILE i <= p_number DO
        SET col_defs = CONCAT(col_defs, ', `number', i, '` FLOAT COMMENT ''传感器温度''');
        SET i = i + 1;
    END WHILE;

    -- 拼接完整建表语句，用IF NOT EXISTS避免重复创建（test.id是主键不会重复，但加上更安全）
    SET @create_sql = CONCAT(
        'CREATE TABLE IF NOT EXISTS `numbers', p_id, '` (', 
        col_defs, 
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT ''监测站', p_id, '温度数据表'''
    );

    -- 执行动态SQL
    PREPARE stmt FROM @create_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

2. 创建触发调用存储过程的触发器 #

当test表新增一行时，自动调用上面的存储过程：

DELIMITER //
CREATE TRIGGER trigger_after_test_insert AFTER INSERT ON test
FOR EACH ROW
BEGIN
    -- 传入新增行的id和number值
    CALL create_numbers_table(NEW.id, NEW.number);
END //
DELIMITER ;

注意事项 #

• 确保执行这些语句的用户拥有CREATE TABLE和CREATE PROCEDURE权限；
• 表名用反引号包裹，避免和MySQL关键字冲突；
• 列类型可根据温度数据精度调整，比如用DECIMAL(5,2)存储精确到小数点后两位的温度；
• 如果后续需要修改test表的number字段，可补充AFTER UPDATE触发器调整对应分表结构（监测站传感器数量一般不会变更，此步骤可选）。

针对你实际的场景——20年温度数据、每15分钟存储、可能新增监测站，我们来分析三种常见方案的优劣：

方案1：单宽表（sensor1~sensorN列） #

• 结构：主键为(timestamp, station_id)，列包含所有可能的传感器字段（sensor1到sensorN），传感器数量不足的行对应列存NULL。
• 存储空间：InnoDB对NULL值存储效率很高，不会占用额外数据空间（仅用少量标记位），但传感器数量差异极大时，宽表元数据会更复杂。
• 性能：数据量会很大（比如100个监测站20年约7000万行），但合理索引（如(station_id, timestamp)）可支撑查询。不过传感器数量过多时，查询读取列过多会影响性能；新增传感器需修改表结构加列，会锁表影响业务。
• 适合场景：传感器数量固定、极少新增，且跨监测站查询频繁的场景。

方案2：按监测站分表（你最初的numbersX方案） #

• 优势：
  • 每个表结构完全匹配对应监测站的传感器数量，无NULL值，存储空间紧凑；
  • 查询单个监测站数据时，操作小表性能更优；
  • 新增监测站自动建表，无需修改现有表结构，扩展性好。
• 劣势：
  • 跨监测站查询需要用UNION ALL拼接多个表，监测站越多查询越复杂、性能越差；
  • 大量表会增加运维成本（备份、优化、迁移数据都更繁琐），MySQL表数量过多（如上万个）会导致元数据管理性能下降。
• 适合场景：大部分查询都是单个监测站的历史数据，极少做跨站聚合查询的场景。

方案3：单窄表（行转列设计） #

这是我更推荐的方案，解决了前两种方案的痛点：

• 结构：表结构为(timestamp DATETIME, station_id INT, sensor_id INT, temperature FLOAT, PRIMARY KEY(station_id, timestamp, sensor_id))，每个传感器的每次数据单独存一行。
• 优势：
  • 无NULL值，存储空间高效；
  • 新增传感器无需修改表结构，扩展性极强；
  • 单表管理简单，跨站、单站、单传感器的查询都能通过索引高效实现；
  • 数据量虽比宽表大，但InnoDB对大行数的支持远好于宽表。
• 劣势：单表行数会比宽表多，但只要索引合理，性能完全没问题。
• 适合场景：几乎所有监测数据存储场景，尤其是传感器数量多变、需要灵活查询的情况。

如果你的查询以单个监测站为主，分表方案可以满足需求；但从长期维护和扩展性来看，**单窄表（行转列）**是最优选择。

内容的提问来源于stack exchange，提问作者Luke