MySql黑科技

    科技2025-05-21  40

    记录一些MySql比较少见但是有奇效的用法。

     IFNULL(@rownum:=@rownum+1, @rownum:=1): IFNULL函数,如果第一个表达式为NULL,则返回第二个表达式的值,否则返回第一个表达式的值。给session中定义一个变量有两种方式,set @rownum=0,或者如上IFNULL,但是IFNULL有一个问题下一次使用该session继续查询时,rownum不是从1开始,可以改成如下方式:select  a.* , @rownum:=@rownum+1 from student a , (select @rownum:=0) order by  student_id。

     order by后面可以跟两个或者以上的字段,这样可以按多个字段分组,就可以显示一个分组中多个字段了。第一个排序字段是分组字段,因为同一分组字段名称都一样,它们会被聚合到一组,然后再按照第二字段排序。如 select * from student order by class_id desc, student_id desc.

    求每个班级前N名学生:

    select class_id, student_id, name, row_num from (

      select class_id, student_id, name, IF(@bak=class_id, @rownum:=@rownum+1, @rownum:=1) as row_num, @bak:=class_id

      from

        (select class_id, student_id, name  from student order by class_id, student_id ) a, ( select @rownum:=0, @bak:='' ) b 

    )  c  where c.row_num <= 2

     GROUP_CONCAT 函数通常和group by一起使用,把相同的分组字段值连接起来,SEPARATOR 默认是',' 它和ORDER BY是可选的。如

    select GROUP_CONCAT(u_name,u_id ORDER BY u_id desc SEPARATOR '|') as users, p_id from webusers GROUP BY p_id, 则会出现类似如下结果:

    users        p_id

    张三56|李四40|王五32  0

    赵六5|陈七3           1

     一条语句实现找到重复数据并删除之,使用到 group分组多个字段、行号定义、CONCAT:

    delect from reviews where r_id in (

    select r_id from (

    select a.r_id, if(@tmp=CONCAT(r_content, r_userid), @rownum:=@rownum+1, @rownum:=1) as rownum, @tmp:=CONCAT(r_content,r_userid) from (

    select a.* from reviews a INNER JOIN (

    select r_content,r_userid, count(*) as num from reviews group by r_content,r_userid HAVING num>1 ) b on a.r_content=b.r_content and a.r_userid=b.r_userid ) a, (select @rownum:=0,@tmp:='') b ) a where rownum > 1 )

      通过程序去除然后再删除

     select GROUP_CONCAT(ids SEPARATOR '|') as ids from (

    select GROUP_CONCAT(r_id) as ids, r_content, r_userid, count(*) from reviews GROUP BY r_content, r_userid HAVEING count(*) > 1) a

     在判断一个比较长的content是否已经存在时,如果存在就不插入更新某个字段,如果不存在则插入。此时因为content太长不适合在上边建立唯一索引,我们可以增加一个字段,该字段保存content的MD5或者sha1值,在该字段建立唯一索引,并使用

    insert into ON DUPLICAT KEY UPDATE  

     mysql里面的case when语句格式:

    case

      when 表达式 then 表达式

      else 表达式

    end 

    可以用在查询,也可以用在根据不同条件update, update语句中可以插入另一张表

    update user_level, (select avg(user_total) as avgtotal from user_level) b  set user_rank = 

      case when round(user_total/b.avgtotal) >= 1 and round(user_total/b.avgtotal) < 2 then '白金用户'

      when round(user_total/b.avgtotal)>=2 then '黄金用户'

    else '观众'

    end where user_total > b.avgtotal

     两天内每个用户最大消费的百分之10当作积分赋给用户:

    update users_score a INNER JOIN

    (select max(paymoney) as mp, user_name from users_buy group by user_name) b on a.user_name = b.user_name

    set a.user_score=a.user_score+(b.mp*.01)

    在排行榜上让某些用户永远置顶

    select * from (select * from user_level where id in (2,4,6) order by user_total desc) a

    union

    select * from (select * from user_level where id not in (2,4,6) order by user_total desc) b

    简单点:

    select * from user_level order by id in (4,6,2) and id <> 2 desc , user_total desc 

    =0的字段不参与排序,讲等于某个值的字段排在前面

    https://blog.csdn.net/u012228558/article/details/72927048

    联合索引底层

    https://segmentfault.com/a/1190000010991930

     

    Processed: 0.011, SQL: 8