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

时间:2022-10-14 18:57:20

工作中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. 我在统计我爬虫抓取到的一些内容的时候,又一次需要一个 堆叠的条形图,相当于将一个字段的值按区间来分组。

比如我要统计 在线时间 分别 在 050h,50100h,100200h,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

##########
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。*