mysql 自定义查询函数_MySQL巧用自定义函数进行查询优化

用户自定义变量是一个很容易被遗忘的MySQL特性,但是用的好,发挥其潜力,在很多场景都可以写出非常高效的查询语句。

一. 实现一个按照actorid排序的列

1 mysql> set @rownum :=0;2 Query OK, 0 rows affected (0.00sec)3

4 mysql> select actor_id ,@rownum :=@rownum + 1 asrownum5 -> from sakila.actor limit 3;6 +----------+--------+

7 | actor_id | rownum |

8 +----------+--------+

9 | 58 | 1 |

10 | 92 | 2 |

11 | 182 | 3 |

12 +----------+--------+

13 3 rows in set (0.00 sec)

二. 扩展一下,现在需要获取演过最多电影的前十位,针对数量作一个排名,如果数量一样,则排名相同

1 mysql> set @curr_cnt :=0 ,@pre_cnt :=0 ,@rank :=0;2 Query OK, 0 rows affected (0.00sec)3

4 mysql> selectactor_id,5 -> @prev_cnt :=@curr_cnt as dummy,6 -> @curr_cnt := cnt ascnt,7 -> @rank := IF(@prev_cnt <> @curr_cnt,@rank+1,@rank) asrank8 -> FROM(9 -> SELECT actor_id ,count(*) ascnt10 -> FROMsakila.film_actor11 -> GROUP BYactor_id12 -> ORDER BY cnt DESC

13 -> LIMIT 10

14 -> )asder;15 +----------+-------+-----+------+

16 | actor_id | dummy | cnt | rank |

17 +----------+-------+-----+------+

18 | 107 | 0 | 42 | 1 |

19 | 102 | 42 | 41 | 2 |

20 | 198 | 41 | 40 | 3 |

21 | 181 | 40 | 39 | 4 |

22 | 23 | 39 | 37 | 5 |

23 | 81 | 37 | 36 | 6 |

24 | 158 | 36 | 35 | 7 |

25 | 144 | 35 | 35 | 7 |

26 | 37 | 35 | 35 | 7 |

27 | 106 | 35 | 35 | 7 |

28 +----------+-------+-----+------+

29 10 rows in set (0.00 sec)

三. 避免重复查询刚更新的数据

如果想要高效的更新一条记录的时间戳 ,又想返回更新的数据

1 mysql> create table t2 (id int,lastUpdated datetime);2 Query OK, 0 rows affected (0.03sec)3

4 mysql> insert into t2 (id ,lastupdated)values(1,sysdate());5 Query OK, 1 row affected (0.02sec)6

7 mysql> select * fromt2;8 +------+---------------------+

9 | id | lastUpdated |

10 +------+------