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的妙用

整理中。。。