Mysql使用insert插入多条记录 批量新增数据

如果要向table1中插入5条记录，下面写法是错误的： INSERT INTO table1 (id,name) VALUES(1,小明,2,小强,3,小杜,4,小李,5,小白); MySQL将会抛出下面的错误 ERROR 1136: Column count doesn't match value count at row 1 而正确的写法应该是这样： INSERT INTO t able1(i,name) VALUES(1,'小明'),(2,'小强'),(3,'小杜'),(4,'小李'),(5,'小白'); 当然，这种写法也可以省略列名，这样每一对括号里的值的数目必须一致，而且这个数 在MySQL数据库中，批量插入数据是一项常见的操作，特别是在处理大量数据时，批量插入可以显著提高效率。本篇文章将深入探讨如何正确使用`INSERT`语句来插入多条记录，并讨论相关的优化策略。 让我们来看一下批量插入的基本语法。假设我们有一个名为`table1`的表，它有两个字段：`id`和`name`。错误的批量插入示例如下： ```sql INSERT INTO table1 (id, name) VALUES (1, '小明', 2, '小强', 3, '小杜', 4, '小李', 5, '小白'); ``` 此语句会导致`ERROR 1136: Column count doesn't match value count at row 1`的错误，因为提供的值的数量与列的数量不匹配。正确的批量插入语法是： ```sql INSERT INTO table1 (id, name) VALUES (1, '小明'), (2, '小强'), (3, '小杜'), (4, '小李'), (5, '小白'); ``` 或者，如果你愿意省略列名，确保值的对数与列数一致： ```sql INSERT INTO table1 VALUES (1, '小明'), (2, '小强'), (3, '小杜'), (4, '小李'), (5, '小白'); ``` 批量插入的优势在于减少了与数据库的交互次数，降低了网络延迟，提高了整体性能。然而，当涉及到大量数据时，还有其他优化策略可以考虑： 1. **预处理语句（PreparedStatement）**：预处理语句允许你预先编译SQL语句，然后多次执行，每次只改变参数。这可以避免解析和编译SQL的开销。 2. **批量大小调整**：找出理想的批量大小，太大可能导致内存压力，太小则无法充分利用批量插入的优势。通常，批量大小应该根据具体的数据库和系统资源进行调整。 3. **事务（Transactions）**：将多条`INSERT`语句包裹在同一个事务中，可以减少提交的次数，进一步提升性能。但需要注意的是，如果事务中的某条语句失败，所有更改都将回滚。 4. **禁用唯一索引**：在插入大量数据且不关心唯一性冲突时，可以暂时禁用唯一索引，待插入完成后重新启用。但这种方法需谨慎，因为可能导致数据不一致。 5. **延迟键更新（Delayed Key Updates）**：对于具有外键约束的表，可以启用`DELAYED KEY`选项，使得主键更新在插入后延迟，减少插入过程中的锁竞争。 6. **批量插入与优化器**：MySQL的查询优化器会根据具体情况选择最优的执行计划，包括决定是否使用临时表。了解这些机制有助于写出更高效的批量插入语句。 7. **使用LOAD DATA INFILE**：对于大量CSV或文本文件的数据导入，`LOAD DATA INFILE`命令通常比`INSERT`语句更快，因为它可以直接读取文件并跳过客户端服务器之间的数据传输。 8. **优化硬件和配置**：增加内存、使用更快的硬盘以及优化数据库配置（如调整`innodb_buffer_pool_size`）都可以提升批量插入的性能。 在实践中，应结合具体的应用场景和需求，合理运用上述技巧，确保在保证数据完整性和一致性的同时，最大化批量插入的效率。同时，定期进行性能监控和调优，以适应不断变化的业务需求。