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

这篇文章主要给大家介绍了关于MySQL中几种插入和批量语句的相关资料,在mysql数据库中,实现批量插入数据与批量更新数据的例子,即批量insert、update的方法,需要的朋友可以参考下

目录

  • 前言
  • 1.insert ignore into
  • 2.on duplicate key update
  • 3.replace into
  • 4.insert if not exists
  • 5.批量插入数据
  • 6.批量更新
  • 总结

前言

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

这里为了方便演示,我新建了一个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 批量更新(记得要有主键或者索引)

?

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');

(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;

总结

到此这篇关于MySQL中几种插入和批量语句的文章就介绍到这了,更多相关MySQL插入和批量语句内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://juejin.cn/post/7000639629355401253

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/5331/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-25 6:26:53
下一篇 2022-07-25 6:27:06

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!