mysql批量新增或修改坑

原创

mob64ca12d9e536 2024-04-28 05:02:15 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12d9e536的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL批量新增或修改坑

在日常开发中，我们经常需要处理大量数据的新增或修改，而MySQL数据库的操作中，批量新增或修改数据是一项常见的需求。然而，在实际操作中，可能会遇到一些坑。本文将介绍在MySQL中进行批量新增或修改数据时可能遇到的问题，并给出相应的解决方案。

批量新增数据

在MySQL中，可以使用INSERT INTO语句来批量新增数据。一种常见的方法是使用INSERT INTO ... VALUES ...语法，如下所示：

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3),
       (value4, value5, value6),
       ...

然而，当要新增的数据量很大时，以上方法可能会遇到问题。MySQL有一个参数max_allowed_packet，用于限制单个SQL语句的大小，默认为1MB。如果要新增的数据量超过了max_allowed_packet的限制，就会导致插入数据失败。为了解决这个问题，可以通过修改max_allowed_packet参数的方式来扩大单个SQL语句的大小限制。

另一种方法是使用LOAD DATA INFILE语句，将数据从文件导入数据库中。这种方法相对于使用INSERT INTO语句效率更高，适合处理大量数据的批量新增操作。示例代码如下：

LOAD DATA INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

批量修改数据

在MySQL中，可以使用UPDATE语句来批量修改数据。一种常见的方法是使用UPDATE ... SET ... WHERE ...语法，如下所示：

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

类似于批量新增数据时的问题，当要修改的数据量很大时，以上方法也可能会遇到max_allowed_packet参数的限制。为了避免这个问题，可以将需要修改的数据分批处理，每次修改一部分数据。

另一种方法是使用INSERT ... ON DUPLICATE KEY UPDATE语句，该语句可以实现批量新增或修改数据。示例代码如下：

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3),
       (value4, value5, value6),
       ...
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1),
column2 = VALUES(column2),
column3 = VALUES(column3);