巧用变量

1:用变量排名

例: 以ecshop中的商品表为例,计算每个栏目下的商品数,并按商品数排名.

select cat_id,count(*)  as cnt from  goods group by cat_id order by cnt desc;

并按商品数计算这些栏目的名次

set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;

select cat_id, (@curr_cnt := cnt) as cnt,

(@rank := if(@curr_cnt <> @prev_cnt,@rank+1,@rank)) as rank,

@prev_cnt := @curr_cnt

 from ( select cat_id,count(*) as cnt from shop. goods group by shop. goods.cat_id order by cnt desc) as tmp;

 

2:用变量计算真正影响的行数

当插入多条,当主键重复时,则自动更新,这种效果,可以用insert on duplication for update

要统计真正”新增”的条目, 如下图,我们想得到的值是”1”,即被更新的行数.

 insert into user (uid,uname) values (4,’ds’),(5,'wanu'),(6,’safdsaf’)

 on duplicate key update  uid=values(uid)+(0*(@x:=@x+1)) , uname=values(uname);

 

mysql> set @x:=0;

Query OK, 0 rows affected (0.00 sec)

总影响行数-2*实际update数, 即新增的行数.

3: 简化union

比如有新闻表,news , news_hot,

new_hot是一张内存表,非常快,用来今天的热门新闻.

首页取新闻时,逻辑是这样的:

先取hot, 没有 再取news,为了省事,用一个union来完成.

select nid,title from news_hot where nid=xxx

union

select nid,title from news where nid=xxx;

如何利用变量让后半句select不执行 ,

select id,content,(@find := 1) from news where id=1

union

select id,content,(@find :=1) from news2 where id=1  and (@find <= 0)

union 1,1,1 where (@find :=null) is not null;

3:小心变量的顺序

如下图:变量先在where中发挥作用,然后再是select操作.

如果where不成立,select操作再不发生.

第2例: 

在这个例子中, 1,2两行,先排好序, 在内存中,就是这样的顺序  [2] [1]

再逐行where条件判断,取值. 

对比这2张图,分析:

1: where先发挥作用,把需要的行 都给找出

2: 然后再逐行 select

 

因此, 前者, 最终select时,select@num变量,都是一个值

后者,不断select,不断修改@num的值, 值不断变化.

 

 

 

同时: 使用变量,将会使sql语句的结果不缓存.