专栏首页王旭的专栏Web 开发 MYSQL 常用方法整理 (上)
原创

Web 开发 MYSQL 常用方法整理 (上)

一、数据插入篇

有唯一/主键(primary或者unique)存在时,避免重复插入的方法

在一些报名/拉票类型活动中, 往往需要对uin做唯一键处理,限制1个用户只能有1条报名记录。 为避免并发时重复数据的插入,

常用方法有3种:

  • insert ignore into
  • replace into
  • on duplicate key update

假设有表如下:

(1)使用 insert ignore into:

基本语法:

Insert ignore intotbMyTest (uin, num, dttime) values(516751917, 1, now());

分析:

Insert ignore的好处是,当存在唯一/主键冲突时,则直接忽略最新的insert操作,mysql返回0不报错;没有冲突则正常insert插入数据。一句话概括就是:“无则添加,有则忽略”。

假设原数据库已有数据:

执行insert ignore重复插入,提示插入了0行(表示忽略此次操作):

执行insert ignore插入,无主键重复,则正常插入新行:

(2)使用 replace into

Replace into,也是可以用于避免数据重复插入的方法,但它与insert ignore最大的不同就是: 当有唯一/主键冲突时,insert ignore是直接忽略新数据,而releace into 是用新数据整行替换旧数据, 它会先从数据表中删除唯一/主键冲突的行,再尝试插入新行。

基本语法:

Replace into tbMyTest (uin, num, dttime) values(516751917, 2, now());

关于返回:

Replace into执行后会返回受影响的行数,该数是被删除和被插入的行数总和。如果返回数是1,则说明是首次插入数据; 若返回数是2,则说明在新行插入前,有一行旧数据被删除;若是返回数大于2,则一般是表中有多个唯一索引,有可能是一个单一行替换了多个旧行。

假设原数据库已有数据:

执行上述replace语句后, 有uin主键重复,则先删除原有重复记录,再执行新插入,影响行数2行:

执行replace(无主键冲突),则是插入新行,影响行数1:

特殊说明:

如果表中有一个自增主键,则不建议用replace into,因为replace后新旧记录的主键值不同,若是还有其他表中与本表主键有关联数据的话,关联关系就会被破坏。再者,频繁的replace into,自增值迅速变大,会有溢出风险。

(3)使用 on duplicate key update

常用语法:

Insert into tbMyTest (uin, num, dttime) values(516751917, 5, now()) on duplicate key update num=num+1,dttime=now();

语法解析:

当在insert时若遇到主键/唯一键冲突,则会在原语句上执行update后面的操作, 无重复时则正常insert插入。

On deplicate key update适用的场景比较特定:就是当数据入库时,若遇到主键/唯一键重复存在的数据时,则需要去修改它;不存在时则新增。

假设原数据库已有数据:

执行Insert....on duplicate key update 有主键重复时,在原来语句上执行update,影响行数2行:

在使用上on duplicate key update 也可以支持多行插入,多行插入时,可以使用VALUES(列名)函数引用列值进行更新操作。

方法如下:

Insert into tbMyTest (uin, num, dttime) values(516751917, 8, now()),(21568475, 2, now()) on duplicate key update num=VALUES(num);

以上语句意思就是说:插入数据时,若当前行存在唯一键冲突,则引用当前行insert时的num列来更新num字段,无重复存在的记录则正常插入。

假设原数据库已有数据:

上述语句执行后,结果如下:

分析:

uin为111111遇到主键重复,所以是在原语句上update的,将num值update为当前语句的VALUES('num'),也就是num=8,影响行数是2行。 而uin为333333的行没有主键重复,所以是新增插入, 影响行数是1行。 故,总影响行数是3行。

二、数据查询篇

1、select *select a b c的差别

基于MYSQL select数据取值原理,如果抛开索引和where,光从select *select a b c的效率来说实际是相差不大的,如下图:

这和mysql内部数据存储结构有关,详细原因有兴趣的可以参考mysql的数据查找原理。这里呢就重点说说,两者在编程习惯上和扩展性上的差别:

(1)不管是select * 还是 select a b c,查询效率重点还是取决于where后面的索引设计

(2)原则上,尽量少用elect *

虽然从扩展性而已,如果表结构变更比较频繁,使用select *会减少不少的代码变更,但是在营销活动开发的应用场景上说, 每个活动都是短期存在的,且表结构变化不大,每条sql语句读取的字段都比较明确,建议优先使用select a b c

select a b c 可以减少网络传输,减少服务器的开销。

2、select count(1)count(*)、count(列名)的区别

整理中。。。

3、关于limit的妙用

整理中。。。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • android几种常见的启动模式

    launchMode在多个Activity跳转的过程中扮演着重要的角色,它可以决定是否生成新的Activity实例,是否重用已存在的Activity实例,是否和...

    xiangzhihong
  • vue获取dom元素注意问题

    mounted(){ setTimeout(()=>{ this.contentToggle(); },10...

    蓓蕾心晴
  • 武汉疫情系列(1)|java爬取丁香园|JAVA爬取丁香医生的全国新型肺炎疫情实时动态

    5、其他的信息可以爬取也可以不爬取,看情况吧,如果有需要的话,可以在文章底部留言,我会另外更新上去

    小小鱼儿小小林
  • 如何有计划地致富?这个小程序不服不行

    知晓君
  • 用Python实时获取地球卫星图并自动更新为桌面

    最近疯狂迷恋地球卫星图和地球的卫星视频,看上面的视频简直极度舒适。不禁想把这种图片作为桌面背景图。这就产生抓取其背景图片作为桌面的想法。

    数据森麟
  • 接口自动化测试--框架设计思路

    之前文章跟大家分享了一下自己在接口自动化测试中进行测试准备的一些相关知识点,接下来本篇文章详细分享一下接口自动化框架设计的思路总结,希望能对初次探索接口自动化...

    用户5521279
  • Arch Linux安装与初体验

    Arch Linux 是一个 x86-64 通用发行版,它流行于那些喜欢 DIY Linux 系统的用户和 Linux铁杆粉丝当中。其默认的安装文件只含有一个最...

    yuanfan2012
  • BZOJ1854: [Scoi2010]游戏(二分图匹配)

    lxhgww最近迷上了一款游戏,在游戏里,他拥有很多的装备,每种装备都有2个属性,这些属性的值用[1,10000]之间的数表示。当他使用某种装备时,他只能使用该...

    attack
  • 012.Elasticsearch基础API入门以及term与match综合测试

    当向一个不存在的index中添加document时,可以自动创建索引,也可以根据传入的数据自动创建mapping,ES也会自动对这些文档进行倒排索引

    CoderJed
  • 如何避免你的问题烂尾

    本节主要介绍我在工作中遇到了什么问题,遇到问题后我们如何去解决的思考过程,同时下文结合了《提问的智慧》(https://www.cnblogs.com/guyk...

    研究僧

扫码关注云+社区

领取腾讯云代金券