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 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 硬解析与软解析

Oracle 硬解析与软解析是我们经常遇到的问题,什么情况会产生硬解析,什么情况产生软解析,又当如何避免硬解析?下面的描述将给出

713
来自专栏精讲JAVA

Java面试之数据库面试题

触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可...

1152
来自专栏程序员的SOD蜜

抽象SQL查询:SQL-MAP技术的使用

什么是参数化查询?我们来看百科对此的定义和示例: 一,定义 --------------------------------------------------...

27610
来自专栏Java后端生活

JDBC(三)PreparedStatement

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的 SQL 语句段或命令,从而利用系统的 SQL 引擎完成恶意行为的做法

571
来自专栏深度学习之tensorflow实战篇

SQL中distinct的用法

在表中,可能会包含重复值。这并不成问题,不过,有时您也许希望仅仅列出不同(distinct)的值。关键词 distinct用于返回唯一不同的值。 表A: ? 表...

2813
来自专栏Java技术

面试中有哪些经典的数据库问题?

1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯...

771
来自专栏PHP在线

MySQL的语句执行顺序

MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是 FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作...

46710
来自专栏H2Cloud

游戏服务器ID生成器组件

游戏服务器程序中,经常需要生成全局的唯一ID号,这个功能很常用,本文将介绍一种通用ID生成组件。游戏服务器程序中使用此组件的场景有:  创建角色时,为其分配唯一...

3908
来自专栏杨建荣的学习笔记

通过ORA错误反思sql语句规范(r4笔记第41天)

今天快下班的时候,有个开发的同事问我一个问题,说他在客户端执行一条sql语句,里面包含子查询,如果单独执行子查询,会报"invalid identifier"...

2303
来自专栏架构师之路

赶集mysql军规

总是在灾难发生后,才想起容灾的重要性。 总是在吃过亏后,才记得曾经有人提醒过。 一,核心军规 不在数据库做计算,cpu计算务必移至业务层 控制单表数据量,单表...

41610

扫码关注云+社区