mysql如何输入批量插入 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_3value2_1,value2_2,value2_3value3_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的选项：在使用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)

您可能感兴趣问答

Collapsible

热门标签

热点问答