当前位置：首页 > 行业动态 > 正文

案例3快速填充数据库

快速填充数据库可通过并行加载、批量导入及临时禁用约束实现，结合ETL工具或数据库原生批量接口，优化索引与事务管理，利用多线程分片

常见快速填充方法

  1. 批量INSERT语句
    通过单条INSERT语句插入多行数据，减少网络交互次数。 

    INSERT INTO table_name (col1, col2) VALUES
(val1, val2),
(val3, val4),
...;

  2. LOAD DATA INFILE（MySQL）
    从文件直接导入数据，效率极高。 

    LOAD DATA INFILE 'data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
(col1, col2);

  3. 复制表结构并插入数据
    创建新表并插入数据，适用于结构相同的表。 

    CREATE TABLE new_table AS SELECT  FROM old_table WHERE 1=0;
INSERT INTO new_table SELECT  FROM source_table;

  4. 生成脚本自动填充
    通过脚本（如Python）生成INSERT语句并批量执行。 

    data = [(val1, val2), (val3, val4)]
sql = "INSERT INTO table_name (col1, col2) VALUES " + ",".join(["(%s,%s)"]  len(data))
cursor.executemany(sql, data)

方法对比与选择建议

方法 优点 缺点 适用场景
批量INSERT 简单直接，无需额外文件 数据量大时效率低 小批量数据（<1000行）
LOAD DATA INFILE 极快，适合超大批量数据 需服务器文件权限，依赖文件格式 大规模数据导入（万级+）
复制表结构 保留索引和约束，速度快 仅适用于结构完全相同的表 表结构一致的数据迁移
脚本生成INSERT 灵活定制，支持复杂逻辑 需编程能力，执行效率中等 动态数据生成或转换

注意事项

  1. 事务控制
    批量操作前开启事务（START TRANSACTION），出错时回滚（ROLLBACK）,避免部分成功导致数据不一致。

  2. 字段匹配与数据清洗

    • 确保插入数据与表结构完全匹配（字段顺序、类型）。
    • 清理脏数据（如空值、非规字符）,避免中断导入。

  3. 索引与约束优化

    • 导入前临时禁用索引和约束（如ALTER TABLE DISABLE KEYS），完成后重新启用，提升速度。
    • 避免唯一键冲突,可先清空目标表或去重处理。

相关问题与解答

问题1：使用LOAD DATA INFILE时，如何处理文件中存在缺失字段的行？

解答：

  • 在LOAD DATA语句中指定IGNORE关键字，跳过格式错误的行。
  • 提前清洗数据文件，补充缺失字段（如用默认值或空值）。
  • 示例： 
    LOAD DATA INFILE 'data.csv'
INTO TABLE table_name
IGNORE
FIELDS TERMINATED BY ','
(col1, col2); -缺失col2的行会被跳过

问题2：如何验证批量填充后的数据准确性？

解答：

  1. 行数校验：比较导入前后表的记录数（SELECT COUNT()）。
  2. 抽样检查：随机抽取部分数据，核对字段值是否正确。
  3. 约束验证：检查主键、外键、唯一性约束是否生效（如无重复数据）。
  4. 日志分析：查看数据库错误日志
批量插入数据导入优化
0

相关文章