mysql如何输入批量插入 mysql写多条insert代码教程-mysql教程-PHP中文网

mysql如何输入批量插入 mysql写多条insert代码教程

星夢妙者

发布： 2025-07-10 18:12:02

原创

238人浏览过

mysql批量插入数据有四种主要方式。1.单条insert多值插入，语法简单但可能超包限制且全失败风险高；2.多条insert加事务，减少交互次数但占用资源多；3.load data infile性能最好，需处理文件权限及转义；4.编程语言批量功能灵活处理数据但需额外编码。选择依据为：小数据用多值insert，中等数据用事务，大数据用load data infile，需处理数据用编程语言。错误处理可用事务回滚或load data infile的ignore/replace。优化方法包括禁用索引、调mysql配置、用ssd。中文乱码需统一字符集并在load data infile或连接时指定character set。

mysql如何输入批量插入 mysql写多条insert代码教程

批量插入数据，在MySQL里其实挺重要的。单个insert效率太低，数据量一大，等得花儿都谢了。直接上解决方案吧，别绕弯子。

解决方案

MySQL批量插入主要有几种方式，性能各有差异，适用场景也不同。

单条INSERT语句，多值插入：

这是最常见，也相对简单的方式。把多条记录的值放在一个INSERT语句里。
```
INSERT INTO `your_table` (`column1`, `column2`, `column3`) VALUES
('value1_1', 'value1_2', 'value1_3'),
('value2_1', 'value2_2', 'value2_3'),
('value3_1', 'value3_2', 'value3_3');
```
登录后复制
这种方式的优点是语法简单，容易理解。缺点是，如果数据量特别大，这个SQL语句会变得非常长，可能会超过MySQL的max_allowed_packet限制。另外，一条SQL语句失败，整个批量插入就失败了。
多条INSERT语句，使用事务：

把多条INSERT语句放在一个事务里，一次性提交。
```
START TRANSACTION;
INSERT INTO `your_table` (`column1`, `column2`, `column3`) VALUES ('value1_1', 'value1_2', 'value1_3');
INSERT INTO `your_table` (`column1`, `column2`, `column3`) VALUES ('value2_1', 'value2_2', 'value2_3');
INSERT INTO `your_table` (`column1`, `column2`, `column3`) VALUES ('value3_1', 'value3_2', 'value3_3');
COMMIT;
```
登录后复制
使用事务可以保证数据的一致性，要么全部成功，要么全部失败。性能比单条INSERT要好，因为减少了与数据库的交互次数。但是，如果数据量太大，事务可能会占用大量的资源，导致锁等待，影响其他操作。
使用LOAD DATA INFILE：

这是MySQL官方推荐的批量插入方式，性能最好。它直接从文件中读取数据，然后插入到表中。

首先，你需要把数据整理成一个文本文件，每行一条记录，字段之间用分隔符分隔。
```
value1_1,value1_2,value1_3
value2_1,value2_2,value2_3
value3_1,value3_2,value3_3
```
登录后复制
然后，执行LOAD DATA INFILE语句：
```
LOAD DATA INFILE '/path/to/your/data.txt'
INTO TABLE `your_table`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
```
登录后复制
FIELDS TERMINATED BY指定字段分隔符，LINES TERMINATED BY指定行分隔符。使用LOAD DATA INFILE需要注意权限问题，MySQL服务器进程需要有读取文件的权限。另外，如果数据文件中包含特殊字符，需要进行转义。

使用编程语言的批量插入功能：

很多编程语言的数据库驱动都提供了批量插入的功能。例如，Python的pymysql库可以使用executemany()方法。

import pymysql

# 连接数据库
connection = pymysql.connect(host='your_host',
                             user='your_user',
                             password='your_password',
                             database='your_database',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # 准备SQL语句
        sql = "INSERT INTO `your_table` (`column1`, `column2`, `column3`) VALUES (%s, %s, %s)"

        # 准备数据
        data = [
            ('value1_1', 'value1_2', 'value1_3'),
            ('value2_1', 'value2_2', 'value2_3'),
            ('value3_1', 'value3_2', 'value3_3'),
        ]

        # 执行批量插入
        cursor.executemany(sql, data)

    # 提交事务
    connection.commit()

finally:
    connection.close()

登录后复制

这种方式的优点是可以灵活地处理数据，例如进行数据清洗、转换等。缺点是需要编写额外的代码。

如何选择合适的批量插入方式？

选择哪种方式取决于你的具体需求。

数据量小，对性能要求不高： 可以使用单条INSERT语句，多值插入。
数据量中等，需要保证数据一致性： 可以使用多条INSERT语句，使用事务。
数据量大，对性能要求高： 可以使用LOAD DATA INFILE。
需要对数据进行处理： 可以使用编程语言的批量插入功能。

批量插入时，如何处理错误？

批量插入时，如果遇到错误，可能会导致部分数据插入成功，部分数据插入失败。为了避免这种情况，可以使用事务。在事务中，如果遇到错误，可以回滚事务，保证数据的一致性。

在使用LOAD DATA INFILE时，可以使用IGNORE或REPLACE选项来处理重复数据。 IGNORE会忽略重复数据，REPLACE会替换重复数据。

如何优化批量插入的性能？

除了选择合适的批量插入方式外，还可以通过以下方式来优化批量插入的性能：

禁用索引： 在批量插入之前，可以禁用索引，插入完成后再启用索引。这样可以减少索引维护的开销。
调整MySQL配置： 可以调整MySQL的innodb_buffer_pool_size、innodb_log_file_size等参数，以提高IO性能。
使用SSD： 使用SSD可以显著提高IO性能。

批量插入数据时，中文乱码怎么办？

确保你的数据库、表、连接都使用了相同的字符集，例如UTF-8。在使用LOAD DATA INFILE时，可以使用CHARACTER SET选项指定字符集。

LOAD DATA INFILE '/path/to/your/data.txt'
INTO TABLE `your_table`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
CHARACTER SET utf8;

登录后复制

在编程语言中，也需要设置连接的字符集。例如，在Python的pymysql库中，可以设置charset参数。

connection = pymysql.connect(host='your_host',
                             user='your_user',
                             password='your_password',
                             database='your_database',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

登录后复制

以上就是mysql如何输入批量插入 mysql写多条insert代码教程的详细内容，更多请关注php中文网其它相关文章！