基于MySQL现有表列创建新表：寻求纯SQL脚本替代低效Python方案

当然可以只用SQL脚本实现你的需求，而且这绝对是效率最高的选择——数据库原生的批量操作完全能把你的处理时间从“近1年”压缩到“几天”级别的可接受范围。你的Python脚本慢的核心原因是逐行读取CSV+逐行执行INSERT，每一行都要经过应用层解析、连接传输（哪怕本地连接也有开销）、数据库事务处理，1.33亿行的累加开销直接爆炸。而纯SQL操作是数据库内部的批量处理，没有这些额外开销。

下面是具体的实现步骤和优化建议：

一、先创建14张目标表 #

你可以手动编写CREATE TABLE语句（因为只有14列，工作量不大），或者用MySQL的元数据自动生成脚本。注意要和原表的数据类型保持一致（比如原表的columnX是FLOAT，目标表的value就用FLOAT，别用VARCHAR浪费空间）。

示例创建语句（以某一列为例） #

CREATE TABLE IF NOT EXISTS vaisala2_temperature (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `milliseconds` BIGINT(20) NOT NULL DEFAULT '0',
    `value` FLOAT DEFAULT NULL,  -- 这里要和原表对应列的类型一致
    PRIMARY KEY (`id`),
    UNIQUE KEY `milliseconds` (`milliseconds`) COMMENT 'Eliminates duplicate millisecond values',
    KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

把上面的语句复制14次，替换表名（vaisala2_temperature）和value列的类型即可。

二、批量插入数据：用INSERT ... SELECT替代逐行插入 #

这是核心优化点——直接从原表查询数据插入到目标表，完全跳过应用层的开销。

单表插入示例 #

-- 关闭自动提交，减少事务日志开销
SET autocommit = 0;

INSERT INTO vaisala2_temperature (`timestamp`, milliseconds, value)
SELECT 
    `timestamp`,
    UNIX_TIMESTAMP(`timestamp`) * 1000,  -- 数据库内部直接计算毫秒数，比Python快得多
    temperature  -- 替换成原表对应的列名
FROM original_table;  -- 替换成你的原表名

COMMIT;

对每一张目标表执行一次上面的INSERT ... SELECT语句即可。

（可选）分批次插入避免资源耗尽 #

如果原表太大，一次性插入可能会占用过多内存或磁盘IO，可以分批次按id或timestamp分段插入：

-- 比如每次插入100万行
INSERT INTO vaisala2_temperature (`timestamp`, milliseconds, value)
SELECT `timestamp`, UNIX_TIMESTAMP(`timestamp`) * 1000, temperature
FROM original_table
WHERE id BETWEEN 1 AND 1000000;

COMMIT;

-- 下一批
INSERT INTO vaisala2_temperature (`timestamp`, milliseconds, value)
SELECT `timestamp`, UNIX_TIMESTAMP(`timestamp`) * 1000, temperature
FROM original_table
WHERE id BETWEEN 1000001 AND 2000000;

COMMIT;

你可以写一个简单的循环脚本（比如用MySQL的存储过程，或者shell脚本循环执行SQL）来自动处理所有批次，这样还能随时监控进度。

三、进一步优化：加速插入的配置调整 #

为了让插入速度更快，可以临时调整MySQL的配置（操作前记得备份配置文件）：

• 增大innodb_buffer_pool_size：比如设置为服务器内存的70%（如果服务器专门跑MySQL的话），让更多数据在内存中处理。
• 增大innodb_log_file_size：比如设置为1G-2G，减少日志切换的频率。
• 临时设置innodb_flush_log_at_trx_commit = 2：牺牲一点事务持久性（如果你的业务允许），换取写入速度的大幅提升。
• 插入前先删除目标表的非主键索引，插入完成后再重建：索引会大幅减慢插入速度，批量插入后建索引比插入时维护索引快得多。比如：

  -- 创建表时先不建非主键索引
  CREATE TABLE IF NOT EXISTS vaisala2_temperature (
      `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
      `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `milliseconds` BIGINT(20) NOT NULL DEFAULT '0',
      `value` FLOAT DEFAULT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- 插入数据...
  
  -- 插入完成后添加索引
  ALTER TABLE vaisala2_temperature
  ADD UNIQUE KEY `milliseconds` (`milliseconds`) COMMENT 'Eliminates duplicate millisecond values',
  ADD KEY `timestamp` (`timestamp`);
  

为什么你的Python脚本这么慢？ #

你的Python脚本每处理一行都要：

1. 解析CSV字符串
2. 转换时间戳
3. 生成一条INSERT语句
4. 通过Python的MySQL驱动发送到数据库执行
   1.33亿行的话，每一步的微小开销都会被放大到无法接受的程度。而纯SQL的INSERT ... SELECT是数据库内部的批量操作，用的是最底层的高效写入机制，没有这些额外开销，速度至少是Python脚本的100倍以上。

按照这个方案，处理1.33亿行数据完全可以在几天内完成，完全符合你的预期。

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