一、MySQL 里长什么样

create function 名字(参数 类型) returns 类型 deterministic begin … return … end。 和存储过程比多了三样：returns、return、deterministic。 returns 声明返回类型，return 是真正的返回语句。 一个存储函数必须恰好返回一个值——不返回、返回多个都不行。 deterministic 声明「确定性」： 同样的输入永远给同样的输出。 now()、rand() 这种就不是确定性的。 这个声明不是注释，优化器真的会用： 确定性函数在某些场景下能被提前算好、能进索引、能参与分区裁剪； 写错了（该写不写）不仅慢，还可能直接报错—— 开了 binlog 的库上创建非确定性函数，会被拦下来。

二、存储函数和存储过程的三处不同

一是调用方式： 过程用 call，函数嵌在 SQL 里。 这意味着函数能出现在 where、group by、order by、甚至 check 约束里。 二是返回值：过程可以有零个或多个 out 参数，函数必须恰好返回一个值。 三是能不能改数据： 过程可以 insert／update／delete，函数原则上不许。 MySQL 默认禁止函数改数据 （除非声明 modifies sql data）， 因为它可能在一句 select 里被调用几百万次——一边查一边改，结果就没法预期了。 记住这条区分线： 「算一个值」用函数，「做一件事」用过程。 把这两件事混在一起，早晚出问题。

三、本沙箱用 create_function 注册

create_function('名字', 入参个数, Python 函数, deterministic=True)， 四个参数：名字、几个入参、实现、是否确定性。 注册完它就成了「这台连接上的一个函数」， 和 abs()、length() 一样用。 Demo 里注册前后数了一下 pragma function_list：239 个变 240 个。 那 239 个里有一批不是内置的——是前面几章为了模拟 MySQL 而注册的垫片（ifnull、concat、 date_format 那些）。 它们就是同一套办法做的。 所以你早就用过这个功能了，只是不知道它在哪。 「连接级」是关键差别： 换一个连接、换一个进程，就得重新注册一遍。 MySQL 的函数存在库里，谁连都能用；这里的函数存在内存里，进程一关就没了。 这也是为什么它「不是真的存储函数」，只是行为上像。

四、它能出现在 select 的每个角落

select grade(height) from students——最直接的用法。 where grade(height) = '高'——注意这一句会让索引失效（第 12.1 篇讲过「列被函数包住」）， 因为每行都得先算出函数值才能比较。 对内置函数也一样，UDF 更不用说。 group by grade(height)——把函数结果当分组依据，很常用： 「按身高档位统计人数」就是这么写的。 order by 里也能用，having 里也能用。 一句话：SQL 里任何一个「能放表达式」的地方都能放它。 因为它对 SQL 来说就是个表达式。

五、出错时的三条报错

Python 里抛异常 → user-defined function raised exception。 原始异常信息被吞掉了，只剩这一句。 入参个数不对 → wrong number of arguments to function grade()。 注意它把函数名带出来了，好查。 没注册过 → no such function: nosuchfn。 这个最常见的原因是「忘了注册」或者「换了连接」。 第一条最坑：Python 那边抛的是 ZeroDivisionError，到了这边只剩一句「函数抛异常了」， 连哪一行都不说。 所以函数体里该自己 try 一下、把异常写进日志再抛——否则线上只能靠猜。

六、自定义聚合函数：create_aggregate

sum()、avg() 这类「把多行压成一行」的叫聚合函数。 这边也能自己造一个： 写一个类，带 step 和 finalize 两个方法。 step 每来一行调一次（把值累加进去），finalize 在最后调一次（交出结果）。 total 和内置 sum 给出的数一样，说明这条路是通的。 有个坑必须提： group by 的时候，每组各用一个新实例—— 所以累加器必须在 __init__ 里初始化，不能写成类属性。 写成类属性，各组的数据会串在一起，结果大得离谱。

七、慢在哪：每行跨一次边界

Demo 在两万行上比了一次：什么都不算 0.1 毫秒，带一个 grade() 条件 15.2 毫秒。 但这个倍率不能当「UDF 慢多少倍」的结论—— 前者只是数行数，后者要逐行调 Python 再比较，干的活本来就不一样。 能确定的只有一件事：每行都要跨一次「SQL 引擎 → Python」的边界。 所以 UDF 的正确用法是「少量行上的复杂判断」， 不是「在大结果集上逐行算」。 真要逐行处理两万行，拿出来在 Python 里循环更快—— 少了两万次来回。 这也解释了为什么「能用内置函数就别用 UDF」：内置函数在 C 层跑，不跨边界， 差的是数量级，不是几倍。

八、动手 Demo：注册一个 grade()，看它能待在哪、错在哪、慢在哪

Demo 六段：数一遍函数个数、注册 grade()、在 select／where／group by 里用它、试三种错、 造一个聚合函数、最后在两万行上量一次。 第一段那个「239 变 240」值得留意—— pragma function_list 把内置函数和注册进来的函数列在同一张表里。

import _q46 as Q
c = Q.con()

# 一个「存储函数」：同样的输入永远同样的输出
def grade(h):
    if h is None:
        return "缺"
    if h >= 180:
        return "高"
    if h >= 170:
        return "中"
    return "矮"

n0 = len(c.execute("PRAGMA function_list").fetchall())
c.create_function("grade", 1, grade, deterministic=True)
n1 = len(c.execute("PRAGMA function_list").fetchall())
print("注册前 %d 个函数，注册后 %d 个" % (n0, n1))

Q.show(c, "SELECT id, name, height, grade(height) AS 档 FROM students")

# where、group by、order by 里都能用
Q.show(c, "SELECT name FROM students WHERE grade(height) = '高'")
Q.show(c, "SELECT grade(height) AS 档, COUNT(*) AS 人数 "
          "FROM students GROUP BY grade(height)")

# 函数里出错、参数个数不对、函数不存在
def boom(x):
    raise ValueError("故意炸")

c.create_function("boom", 1, boom)
for s in ("SELECT boom(1)", "SELECT grade(1, 2)", "SELECT nosuchfn(1)"):
    try:
        c.execute(s)
    except Exception as e:
        print(s, "->", e)

# 自定义聚合函数：step 每行调一次，finalize 最后调一次
class Total:
    def __init__(self):
        self.s = 0.0
    def step(self, v):
        if v is not None:
            self.s += v
    def finalize(self):
        return round(self.s, 2)

c.create_aggregate("total", 1, Total)
Q.show(c, "SELECT total(height) AS 合计, ROUND(SUM(height), 2) AS 内置 "
          "FROM students")

实跑输出：

--------------------------------------------------------------------
第六十九课：存储函数——能写进 select 里的那种
--------------------------------------------------------------------

  MySQL 里长什么样
  MySQL 原码（本沙箱不执行）：
  | DELIMITER $$
  | CREATE FUNCTION grade(h DECIMAL(4,1))
  | RETURNS VARCHAR(4)
  | DETERMINISTIC
  | BEGIN
  |     RETURN CASE
  |         WHEN h >= 180 THEN '高'
  |         WHEN h >= 170 THEN '中'
  |         ELSE '矮'
  |     END;
  | END $$
  | DELIMITER ;
  **用法：`SELECT name, grade(height) FROM students;`**
  **`returns` 声明返回类型，`deterministic` 声明「同样的输入永远同样的输出」。**
  **那个声明不是装饰**：
  **MySQL 的优化器靠它决定能不能缓存、能不能放进索引。**
  **写错了（该写不写）会让查询慢，或者直接报错。**

  这里用 create_function 注册
  **`create_function('grade', 1, grade, deterministic=True)`**——
  **四个参数：名字、入参个数、Python 函数、是否确定性。**
  **注册完它就成了这台连接上的一个函数**，
  **和 `abs()`、`length()` 一样用。**
  **注册前 `pragma function_list` 有 239 个函数，注册后 240 个。**
  SQL> SELECT id, name, height, grade(height) AS 档 FROM students
  id     name     height     档
  ----------------------------------
  1      张伟     175.50     中
  2      李娜     162.00     矮
  3      王强     181.00     高
  （3 行）
  **注意这个函数是「连接级」的**：
  **换一个连接、换一个进程，就得重新注册一遍。** **这是它和「真正存在库里的函数」最大的区别**——
  **MySQL 的函数存在库里，谁连都能用；这里的函数存在内存里，进程一关就没了。**

  能用在 select 的任何位置
  SQL> SELECT name FROM students WHERE grade(height) = '高'
  name
  -----
  王强
  （1 行）
  SQL> SELECT grade(height) AS 档, COUNT(*) AS 人数 FROM students GROUP BY grade(height)
  档     人数
  ------------
  中     1
  矮     1
  高     1
  （3 行）
  SQL> SELECT grade(height) AS 档, ROUND(AVG(height), 2) AS 平均 FROM students
       GROUP BY grade(height) ORDER BY 档
  档     平均
  --------------
  中     175.50
  矮     162.00
  高     181.00
  （3 行）
  **`where`、`group by`、`order by` 里都能用**，
  **因为对 SQL 来说它就是个普通函数。** **这一点和 MySQL 的存储函数完全一样。**

  函数里出错会怎样
  SQL> SELECT boom(1)
  报错：user-defined function raised exception
  SQL> SELECT grade(1, 2)
  报错：wrong number of arguments to function grade()
  SQL> SELECT nosuchfn(1)
  报错：no such function: nosuchfn
  **三种错，三条不同的报错**：
  **Python 里抛异常 → `user-defined function raised exception`**
  （**原始异常信息被吞掉了，只剩这一句**）；**参数个数不对 → `wrong number of arguments`**；
  **没注册过 → `no such function`。**
  **第一条最坑**：
  **Python 那边抛的是 `ZeroDivisionError`，到了这边只剩一句「函数抛异常了」**——
  **排查时得在函数体里自己往日志写。** **这一条比 MySQL 还难查，因为 MySQL 至少会带 `SQLSTATE`。**

  自定义聚合函数：create_aggregate
  **`sum()`、`avg()` 这类「把多行压成一行」的叫聚合函数。** **这边也能自己造一个**：
  **写一个类，带 `step` 和 `finalize` 两个方法。**
  SQL> SELECT total(height) AS 合计, ROUND(SUM(height), 2) AS 内置sum FROM students
  合计       内置sum
  -------------------
  518.50     518.50
  （1 行）
  **`step` 每来一行调一次，`finalize` 在最后调一次。**
  **`total` 和内置 `sum` 给出的数一样，说明这条路是通的。**
  **`group by` 的时候，每组各用一个新实例**——
  **所以 `__init__` 里初始化累加器，别放类属性上。**
  **这个坑很常见：写成类属性，各组的数据会串在一起。**

  慢：每行调一次 Python
  **两万行上比一次**：**什么都不算 0.1 毫秒，带一个 `grade()` 条件 15.2 毫秒。**
  **注意这个倍率不能当「UDF 慢多少倍」的结论**——**前者只是数行数，后者要逐行调 Python 再比较**，
  **干的活本来就不一样。** **能确定的只有一件事：每行都要跨一次「SQL 引擎 → Python」的边界。**
  **所以 UDF 的正确用法是「少量行上的复杂判断」**，
  **不是「在大结果集上逐行算」。** **真要逐行处理两万行，拿出来在 Python 里循环更快**——
  **少了两万次来回。**

注册前 239 个函数，注册后 240 个。 张伟「中」、李娜「矮」、王强「高」，where 筛出王强，group by 三档各 1 人。 三条错：user-defined function raised exception、 wrong number of arguments to function grade()、no such function: nosuchfn。 自定义聚合 total 和内置 sum 都是 518.50。 两万行：0.1 毫秒对 15.2 毫秒。

30 秒实验

1. where grade(height) = '高' 这句能用上 height 上的索引吗？为什么？
2. 把 Total 里的 self.s 改成类属性 s，输出会变成什么？先猜再试。
3. 为什么 MySQL 默认不许存储函数改数据？

常见误区

1. 在 where 里套 UDF 还指望走索引。 列被函数包住，索引就废了，内置函数也一样。
2. 把累加器写成类属性。 group by 时各组共用一个实例，数据会串在一起。
3. 拿 UDF 在大结果集上逐行算。 每行跨一次边界，不如取出来在应用里循环。

函数是「算一个值」，下一篇讲「做一件事、而且不用人叫」的：13.4 触发器——增删改的时候自动跑。

• ☐ （本篇完，下一篇 13.4 触发器）

相关笔记

• 存储过程
• 索引简介
• 🦏 MySQL即学即用 · 索引