【MYSQL】浅谈分页查询和自定义函数

vlambda
2022-03-04

【MYSQL】浅谈分页查询和自定义函数

春天来了，金三银四要到了，那么我也为了粉(liu)丝(liang)讲一下面试涉及的一些技术点。这篇讲一下MYSQL的分页查询和自定义函数两个知识点。

分页查询

假设表scores,存放的是学生的成绩，我们怎么查询第二名的成绩呢？

name	score
Amy	100
Bob	89
Cindy	78
Daisy	96

先谈一下MYSQL的查询语句，我们熟知的是:

SELECT name,score from scores limit 1;

而当我们需要排序取出TOP2的时候

SELECT name,score from scores order by score desc limit 2;

其实，这句语法的完全形态应该是：

SELECT _column,_column from _table [where Clause] [limit N][offset M]

可以简写为

SELECT _column,_column from _table [where Clause] [limit N,M]

其中N(可选)指定返回记录行的偏移量，M(必填)指定返回记录行的最大数目，其中N默认为0, 即我们熟知的limit 5 其实是limit 0,5 ，查询第二名的学生名字的SQL也就呼之欲出了。

---- 当没有第二名的时候返回nullselect  (SELECT score from scores group by scoreorder by score desc  limit 1,1) as SecondHighestScore;

题目来源于Leecode第176题，求第二高的薪水，有兴趣的同学可以去看看；另外随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似 select score from scores group by score order by score desc limit 100000,1; 这时候查询的效率就会变得很慢且效率与偏移量N是负相关的，N越大，效率越差。优化方案可以考虑子查询分页、借助索引等方式，关于分页查询的进阶我们后面有时间再讲。

自定义函数

官网是这么定义自定义函数的：


CREATE [DEFINER = user] FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
func_parameter: param_name type
type: Any valid MySQL data type
characteristic: { COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }}
routine_body: Valid SQL routine statement

解释一下：

sp_name 为函数名,不能与已有关键字冲突

func_parameter为参数列表, 包括参数名和参数类型，可选，多个参数需要用','隔开

RETURNS 函数返回值, 必选，且需要指明返回值的类型，如果返回值不符合指定类型，将会被强制转换为对应的类型

characteristic 指定函数的特性，create function至少满足以下一种特性

1、comment 'string'：注释，用来描述函数。

2、LANGUAGE SQL 支持且唯一支持sql语句

3、[NOT] DETERMINISTIC ：表示返回结果是否确定，默认NOT DETERMINISTIC，DETERMINISTIC 表示结果是确定的，给定参数列表只能产生一个结果，不受存储数据、服务器环境等影响，NOT DETERMINISTIC 则反之。

4、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 表示sql语句的限制，默认为CONTAINS SQL

5、SQL SECURITY { DEFINER | INVOKER } 表示谁有权限执行函数，默认为DEFINER，DEFINER表示只有定义函数的人才能执行，INVOKER表示拥有权限的调用者可以执行

routine_body 表明了函数体可以由合法的SQL语句构成、可以是简单的读取/修改数据语句，如果是复合结构则使用begin end语句

另外，对于自定义函数中的变量，需要做DECLARE声明。

Q：结合题目来看一下，前文的scores表，需要求第N高的分数怎么做呢？

A：显然地，N是参数，sql主语句与分页查询的求第二高的分数相似，具体代码如下：

CREATE FUNCTION getNthHighestScore(N int)  # sp_name(func_parameter)# 函数返回值RETURNS int # routine_bodyBEGIN declare m int;# 声明变量set m = N - 1; RETURN ( # Write your MySQL query statement below. select score from scores group by score order by score desc limit m, 1 );END