MySQL中几种插入和批量语句实例详解

吾爱主题阅读：129 2024-04-02 08:05:47 评论：0

前言

最常见的方式就是为字段设置主键或唯一索引，当插入重复数据时，抛出错误，程序终止，但这会给后续处理带来麻烦，因此需要对插入语句做特殊处理，尽量避开或忽略异常，下面我简单介绍一下，感兴趣的朋友可以尝试一下：

这里为了方便演示，我新建了一个user测试表，主要有id，username，sex，address这4个字段，其中主键为id（自增），同时对username字段设置了唯一索引（idx_username）

1.insert ignore into

即插入数据时，如果数据存在，则忽略此次插入，前提条件是插入的数据字段设置了主键或唯一索引，测试SQL语句如下，当插入本条数据时，MySQL数据库会首先检索已有数据（也就是idx_username索引），如果存在，则忽略本次插入，如果不存在，则正常插入数据：

1	`INSERT` `IGNORE` `INTO` `user` `(username,sex,address)` `VALUES` `(` `'hanpang'` `,` `'boy'` `,` `'HongKong'` `)`

2.on duplicate key update

即插入数据时，如果数据存在，则执行更新操作，前提条件同上，也是插入的数据字段设置了主键或唯一索引，测试SQL语句如下，当插入本条记录时，MySQL数据库会首先检索已有数据（idx_username索引），如果存在，则执行update更新操作，如果不存在，则直接插入：

1 2 3 INSERT IGNORE INTO user (username,sex,address) VALUES ( 'hanpang' , 'boy' , 'HongKong' ) on duplicate key update SEX= 'boy' ,address= 'HongKong'

3.replace into

即插入数据时，如果数据存在，则删除再插入，前提条件同上，插入的数据字段需要设置主键或唯一索引，测试SQL语句如下，当插入本条记录时，MySQL数据库会首先检索已有数据（idx_username索引），如果存在，则先删除旧数据，然后再插入，如果不存在，则直接插入：

1	`REPLACE` `INTO` `user` `(username,sex,address)` `VALUES` `(` `'hanpang'` `,` `'boy'` `,` `'HongKong'` `)`

4.insert if not exists

即 insert into … select … where not exist ... ，这种方式适合于插入的数据字段没有设置主键或唯一索引，当插入一条数据时，首先判断MySQL数据库中是否存在这条数据，如果不存在，则正常插入，如果存在，则忽略：

1 2 3 INSERT INTO user (username,sex,address) SELECT 'hanpang' , 'boy' , 'HongKong' FROM user WHERE NOT EXISTS ( SELECT username FROM user WHERE username= 'hanpang' )

5.批量插入数据

上述的插入语句，是可以使用批量插入语句，表数据结构：

1 2 3 4 5 6 CREATE TABLE example ( example_id INT NOT NULL , name VARCHAR ( 50 ) NOT NULL , value VARCHAR ( 50 ) NOT NULL , other_value VARCHAR ( 50 ) NOT NULL )

个人习惯使用这种批量操作方式，进行SQL语句拼接，但是当你的字符串太长（百万数据）的时候，需要你对mysql设置指令：

1 2 3 4 5 6 INSERT INTO example VALUES (100, 'Name 1' , 'Value 1' , 'Other 1' ), (101, 'Name 2' , 'Value 2' , 'Other 2' ), (102, 'Name 3' , 'Value 3' , 'Other 3' ), (103, 'Name 4' , 'Value 4' , 'Other 4' );

实际开发中，我们更加喜欢使用通过程序代码进行批量添加操作（使用事务提交，批量插入数据库），使用上述的方式在插入测试数据或者其他低要求时比较合适，速度确实快。

6.批量更新

（1）replace into 批量更新(记得要有主键或者索引)

（2）insert into ...on duplicate key update批量更新

使用INSERT的时候有表T(id,A,B,C,D)

插入的时候希望通过A,B索引唯一记录，有重复的时候更新C,D

1	`INSERT` `INTO` `T(A,B,C,D)` `VALUES` `(a,b,c,d)` `ON` `DUPLICATE` `KEY` `UPDATE` `C=C+1，D=d`

（3）使用mysql 自带的语句构建批量更新

1 2 3 4 5 6 7 UPDATE yoiurtable SET dingdan = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 5 END WHERE id IN (1,2,3)

（4）创建临时表，先更新临时表，然后从临时表中update

1 2 3 create temporary table tmp(id int (4) primary key ,dr varchar (50)); insert into tmp values (0, 'gone' ), (1, 'xx' ),...(m, 'yy' ); update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;