工作中MySql的了解到的小技巧

工作中MySql的小技巧

1. 跑脚本时，经常遇到有则更新无插入的 逻辑操作；通常情况下，来一波if（）判断然后选择 更新还是插入，前两天逛论坛时发现有人在比较REPLACE INTO 和 INSET INTO *** ON DUPLICATE KEY UPDATE ** 的 效率，觉得这是一个是优化以前做法的知识，有点意思。

• REPLACE INTO 与 INSET INTO *** ON DUPLICATE KEY UPDATE ** 的使用与比较

先创建一个实验表：

CREATE TABLE gmm_test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `unqi` INT,
  field1 INT,
  field2 VARCHAR(12),
  UNIQUE KEY (unqi)
) ;

这张表里面，id自增，unqi是唯一索引。
同时我们添加一条记录：

INSERT INTO `gmm_test` (`unqi`,`field1`,`field2`) VALUES (2,2,'ddd');

表里面是这样的：

id    unqi  field1  field2  

 1       2       2  ddd  

当我们想以unqi为条件来一波--有更无增(指符合条件的记录执行更新，不符合的则插入新纪录，下同) 操作时；

如下即可：

REPLACE INTO `gmm_test` (`unqi`,`field2`) VALUES (2, 'qqqq')

执行后，有如下结果：
1 queries executed, 1 success, 0 errors, 0 warnings

查询：replace into `gmm_test` (`unqi`,`field2`) values (2, 'qqq')

共 2 行受到影响

执行耗时   : 0.003 sec
传送时间   : 0.003 sec
总耗时  : 0.006 sec

这时你应该注意到，共2行受到影响

看下表的结果，

id    unqi  field1  field2  

 2       2  (NULL)  qqqq    

连主键id都自增了一次，所以在使用时要注意了，毕竟id变了可不是小事~~

**再来看看 INSET INTO *** ON DUPLICATE KEY UPDATE ， 这个也可以来 ---有更无增；

INSERT INTO `gmm_test` (`unqi`, `field1`, `field2`) VALUES (2, 0, 'qqqq') ON DUPLICATE KEY UPDATE field1 = field1 + 10;

或者
INSERT INTO gmm_test (unqi, field1, field2) VALUES (2, 10, 'qqqq') ON DUPLICATE KEY UPDATE field1 = values(field1);

结果如下：

查询：INSERT INTO `gmm_test` (`unqi`, `field1`, `field2`) VALUES (2, 0, 'qqqq') ON DUPLICATE KEY UPDATE field1 = field1 + 10

共 2 行受到影响

执行耗时   : 0.002 sec
传送时间   : 0.001 sec
总耗时  : 0.003 sec

虽然也是两行，但是主键id并没有变，

id    unqi  field1  field2  
 2       2      10  qqqq   

关于效率问题，木有测，就借网上别人测好了的吧 .这里

总结下来，就是尽量还是不用replace into 吧，毕竟好多坑 坑

2. 我在统计我爬虫抓取到的一些内容的时候，又一次需要一个 堆叠的条形图，相当于将一个字段的值按区间来分组。

比如我要统计 在线时间 分别 在 0_50h,50100h,100_200h，200500h ,200h以上 内的人数分别有多少？

SELECT ELT(INTERVAL(h.online_time,0, 50,100, 200), '0','50','100', '200') AS on_time, COUNT(h.online_time) AS cnt
 FROM hupu_user h
 GROUP BY ELT(INTERVAL(h.online_time, 0, 50,100, 200), '0','50','100', '200');

解释：

INTERVAL(N,N1,N2,N3,..........)

INTERVAL()函数进行比较列表(N1，N2，N3等等)中的N值。该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。如果N为NULL，它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作。

eg：
假设有一个下载速度表（有 speed 和 count 两个字段），
然后统计1M，2M，4M，8M，8M以上这个5个速度区间的个数

select INTERVAL(speed,1000,2000,4000,8000) as i_s, sum(count)  from a_speed_table  group by i_s  

上面的 sql 根据速度区间分组，再对不同区间出现的次数求和

ELT(N,str1,str2,str3,...)

如果N =1返回str1，如果N= 2返回str2，等等。返回NULL如果参数的数量小于1或大于N。ELT()是FIELD()的补集。 这个比较好理解了

3. order by 排序重复的问题。

当order by 后面值相同时，系统对数据的排序可能变得随机化，即一会儿这条数据在前面，一会儿这条数据在后面了 ，所以我们看到了重复数据，所以在分页的时候使用oerder by的时候最后在目标排序字段的基础上再加上一个字段，组成一个不会相同的排序依据

···这些也不算难，权当些无聊时的获取些笔记乐趣吧，然后想吐槽下博客园对markdown格式支持还是有些不一样额，和预期的排版差一些~~||0,0||···

##########作者：fredGui
##########来源：http://www.cnblogs.com/guixiaoming/p/8672343.html
##########著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。*