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 删除。

编辑于

王旭的专栏

1 篇文章1 人订阅

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏FreeBuf

基于约束的SQL攻击

前言 值得庆幸的是如今开发者在构建网站时,已经开始注重安全问题了。绝大部分开发者都意识到SQL注入漏洞的存在,在本文我想与读者共同去探讨另一种与SQL数据库相关...

23450
来自专栏一“技”之长

iOS开发之AddressBook框架详解

    首先,AddressBook框架是一个已经过时的框架,iOS9之后官方提供了Contacts框架来进行用户通讯录相关操作。尽管如此,AddressBoo...

32010
来自专栏PHP在线

MySQL SQL语句优化的10条建议

1、将经常要用到的字段(比如经常要用这些字段来排序,或者用来做搜索),则最好将这些字段设为索引 2、字段的种类尽可能用int或者tinyint类型。另外字段尽可...

33450
来自专栏程序员的SOD蜜

使用OQL+SQLMAP解决ORM多表复杂的查询问题

    一般情况下,使用ORM框架来完成单个实体的查询是很方便的,但如果有复杂的查询条件,普通的ORM组件比较困难,PDF.NET数据开发框架的ORM实体类查询...

24160
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第二十四天 Oracle学习【悟空教程】

ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S...

30420
来自专栏软件开发

一个小时学会MySQL数据库

随着移动互联网的结束与人工智能的到来大数据变成越来越重要,下一个成功者应该是拥有海量数据的,数据与数据库你应该知道。 一、数据库概要 数据库(Database)...

35880
来自专栏乐百川的学习频道

使用sqlite3 模块操作sqlite3数据库

Python内置了sqlite3模块,可以操作流行的嵌入式数据库sqlite3。如果看了我前面的使用 pymysql 操作MySQL数据库这篇文章就更简单了。因...

21570
来自专栏软件开发

一个小时学会MySQL数据库

随着移动互联网的结束与人工智能的到来大数据变成越来越重要,下一个成功者应该是拥有海量数据的,数据与数据库你应该知道。

32830
来自专栏Java帮帮-微信公众号-技术文章全总结

高级框架-springDate-JPA 第二天【悟空教程】

通过annotation(注解)来映射实体类和数据库表的对应关系,基于annotation的主键标识为@Id注解, 其生成规则由@GeneratedValue ...

24110
来自专栏JAVA高级架构

SQL性能优化梳理

前言 本文主要针对的是关系型数据数据库MySql。键值类数据库可以参考最简大数据Redis。先简单梳理下Mysql的基本概念,然后分创建时和查询时这两个阶段的优...

31870

扫码关注云+社区

领取腾讯云代金券