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

相关文章

来自专栏Java进阶架构师

「mysql优化专题」你们要的多表查询优化来啦!请查收(4)

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根...

412
来自专栏数据和云

让SQL成为一种生活方式:认识分页查询

? 1分页查询的书写方式 分页的目的就是控制输出结果集大小,将结果尽快的返回。 Oracle的分页查询语句有两种基本格式。第一种格式如下: ? 上面给出的这个...

2736
来自专栏数据和云

常与无常:SQL语句中常量的处理及性能差异解析

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 在ITPUB论坛上看到一个有意思的问题:两...

3209
来自专栏java达人

漫谈数据库索引

一、引言 对数据库索引的关注从未淡出我的们的讨论,那么数据库索引是什么样的?聚集索引与非聚集索引有什么不同?希望本文对各位同仁有一定的帮助。有不少存疑的地方,诚...

1739
来自专栏xingoo, 一个梦想做发明家的程序员

oracle数据结构

 数据类型: 1 字符数据:CHAR VARCHAR NCHAR NVARCHAR2 LONG CLOB NCLOB  2 数字数据类型:NUMBER 唯一用来...

1906
来自专栏Hadoop数据仓库

去除重复数据

有两个意义上的重复记录,一是完全重复的记录,也即所有字段均都重复,二是部分字段重复的记录。对于第一种重复,比较容易解决,只需在查询语句中使用distinct关键...

1745
来自专栏java架构师

【SQL Server】系统学习之二:索引优化

页大小8192个字节,行限制为8060字节(大型对象除外)。 包含varchar nvarchar varbinary sql_variant(8012,obj...

2316
来自专栏Danny的专栏

《高性能 MySQL》读书笔记

1、隔离级别有四种: READ UNCOMMITTED(未提交读),同事务中某个语句的修改,即使没有提交,对其他事务也是可见的。这个也叫脏读。 READ...

762
来自专栏漆洪凯的专栏

MySQL 数据库设计总结

MySQL支持很多种不同的数据类型,并且选择正确的数据类型对于获得高性能至关重要。本文将对MySQL 数据库设计总结,希望与大家共同探讨。

22.9K7
来自专栏精讲JAVA

Java面试之数据库面试题

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

834

扫码关注云+社区