一、七个函数各跑一遍

七个里沙箱缺一个 TRUNCATE，其余都能跑。

注意第三行：floor(-2.7) 给 -3，不是 -2。 FLOOR 是「向下取整」，往下就是往更小的方向，负数上就是「离零更远」。 这一条在算「分页数」时特别容易错：总数除以每页要向上取整，用 floor 会少一页。 而最后一页只有一条数据的时候，这一页的 bug 最难被发现。 第五行 pow(2, 10) 给 1024.0，是浮点数。 SQLite 的数学函数一律返回浮点，所以它不能直接当整数用。

二、取整的三种口径

三种口径是：四舍五入、直接截断、向下取整。 它们在正数上差别不大，在负数上能差出一整个单位。

第三行那个做法是 floor(x * 100) / 100，在负数上给 -2.57，比原值更小。 这就是「向下」的含义：不是「去掉小数」，是「往小的方向走」。 所以别用一个公式对付所有场景。 金额用四舍五入，分成用截断（不能凭空多出钱），配额用向下取整（不能超额）。 先问清「往哪个方向取」，再选函数。 这个问题问错了，后面怎么写都错。

三、TRUNCATE：沙箱没有，注册一个

TRUNCATE(x, n) 是「按位截断」，MySQL 有，沙箱没有。 注意它和 TRUNCATE TABLE 是两回事，只是名字撞了。

def truncate(x, n):
    f = 10.0 ** int(n)
    v = x * f
    return (int(v) if v >= 0 else -int(-v)) / f

那个 if v >= 0 是必需的吗？ Python 的 int() 对负数就是「向零取整」，正好等于截断，所以直接 int(v) 也行。 但别的语言不一定这样：C 的 (int) 也是向零，而 floor 是向下。 写这种函数时把方向写清楚，比省两行更重要。 结果对照：截断给 2.56 和 -2.56，四舍五入给 2.57 和 -2.57。 两列放一起看，才知道差在哪。

四、RAND()：随机数怎么用

SQLite 没有 RAND()，只有 random()，给的是 64 位整数。 要 0 到 1 之间的小数，得自己取模再除：(abs(random()) % 100) / 100.0。 % 在 SQL 里是取模运算符，和 Python 一样。 但它在 LIKE 里是通配符——同一个符号在两个位置两种含义，靠位置区分。 最常用的「随机抽几行」是 ORDER BY random() LIMIT 4。 代价是整表排序，每一行都要算一次随机数再排，大表会很慢。 更快的做法是先随机取一个 id 起点再取几条，但那要求 id 连续。 所以「随机抽」这件事，先看表多大再决定用哪种。

五、浮点误差：别用等号比小数

0.1 + 0.2 = 0.3 在沙箱里给 0，意思是「不相等」。 这不是数据库的错，是二进制小数存不下 0.1 和 0.2 这种十进制小数。 所以金额一律用 DECIMAL 存，比较之前先 ROUND 到业务精度，再比。 用 DOUBLE 存钱，迟早会在某一笔上差一分钱，而且极难复现。 ROUND(0.1 + 0.2, 2) = 0.3 给 1，说明「先修再比」是可行的。 关键是要固定一个精度，不能有的地方两位、有的地方四位。

六、动手 Demo：数学函数，和取整的三种口径

这段的主体是「同一批数，三种取整口径各给什么」。 TRUNCATE 那一段先把报错打出来，再注册替身，顺序和教材一致。

import sqlite3

c = sqlite3.connect(u":memory:")

# ① 七个函数
for e in [u"abs(-3.5)", u"floor(2.7)", u"floor(-2.7)", u"sqrt(9)",
          u"pow(2, 10)", u"pi()", u"round(2.567, 2)"]:
    print(e, c.execute(u"SELECT " + e).fetchone()[0])

# ② 三种口径
print(c.execute(u"SELECT round(2.567, 2), round(-2.567, 2)").fetchone())
print(c.execute(u"SELECT floor(2.567 * 100) / 100, "
                u"floor(-2.567 * 100) / 100").fetchone())

# ③ 注册 truncate
def truncate(x, n):
    f = 10.0 ** int(n)
    v = x * f
    return (int(v) if v >= 0 else -int(-v)) / f

c.create_function(u"truncate", 2, truncate)
print(c.execute(u"SELECT truncate(2.567, 2), truncate(-2.567, 2)").fetchone())

# ④ 浮点误差
print(c.execute(u"SELECT 0.1 + 0.2 = 0.3").fetchone())      # 0
print(c.execute(u"SELECT round(0.1 + 0.2, 2) = 0.3").fetchone())  # 1

实跑输出：

--------------------------------------------------------------------
第四十五课：数学函数，和取整的三种口径
--------------------------------------------------------------------

  **教材 10.2 列了七个数学函数：`ABS`、`FLOOR`、`RAND`、`PI`、`TRUNCATE`、`ROUND`、`SQRT`。**
  **前六个里沙箱缺一个 `TRUNCATE`，其余都能跑。**
  **这一课的重点不是「函数有哪些」，而是「取整有三种口径，混用会算错钱」。**

  一、七个函数各跑一遍
  --------------------------------------------------------------------

  写法               结果
  ------------------------------------
  `abs(-3.5)`        3.5
  `floor(2.7)`       2.0
  `floor(-2.7)`      -3.0
  `sqrt(9)`          3.0
  `pow(2, 10)`       1024.0
  `pi()`             3.141592653589793
  `round(2.567, 2)`  2.57
  **注意第三行：`floor(-2.7)` 给 -3，不是 -2。**
  **`FLOOR` 是「向下取整」，往下就是往更小的方向，负数上就是「离零更远」。**
  **这一条在算「分页数」时特别容易错：`ceil(总数 / 每页)` 是对的，`floor` 会少一页。**
  **第五行 `pow(2, 10)` 给 1024.0，是浮点数。**
  **SQLite 的数学函数一律返回浮点，所以它不能直接当整数用，比较之前要留意。**

  二、取整的三种口径
  --------------------------------------------------------------------

  口径                        2.567 的结果  -2.567 的结果
  -------------------------------------------------------
  `round(2.567, 2)`           2.57          -2.57
  `truncate(2.567, 2)`        沙箱无此函数  沙箱无此函数
  `floor(2.567 * 100) / 100`  2.56          -2.57
  **三种口径分别是：四舍五入、直接截断、向下取整。**
  **它们在正数上差别不大，在负数上能差出一整个单位。**
  **第三行那个 `floor` 写法在负数上给 -2.57，比原值更小，这就是「向下」的含义。**
  **如果业务要的是「截断」，这个写法就错了。**
  **所以别用一个公式对付所有场景：金额用四舍五入，分成用截断，配额用向下取整。**
  **先问清「往哪个方向取」，再选函数。**

  三、TRUNCATE：沙箱没有，注册一个
  --------------------------------------------------------------------
  **`truncate` 报 `no such function: truncate`。**

  写法                   结果
  ----------------------------
  `truncate(2.567, 2)`   2.56
  `truncate(-2.567, 2)`  -2.56
  `round(2.567, 2)`      2.57
  `round(-2.567, 2)`     -2.57
  **注册版里那个 `if v >= 0` 不是多余的。**
  **`int()` 在 Python 里对负数是「向零取整」，正好等于截断；但要真按截断写，两种方向都得对上。**
  **第一行和第二行是 2.56 和 -2.56，第三行第四行是 2.57 和 -2.57。**
  **两列放一起看，才知道「截断」和「四舍五入」差在哪。**
  **MySQL 的 `TRUNCATE` 就是截断，和这里注册的语义一致。**
  **注意它不是 `TRUNCATE TABLE` 那个清表语句，同名不同物。**

  四、RAND()：随机数怎么用
  --------------------------------------------------------------------

  写法                              结果
  ------------------------------------------------
  `random()`                        一个 64 位整数
  `abs(random()) %% 100`            68
  `(abs(random()) %% 100) / 100.0`  0.99
  **SQLite 没有 `RAND()`，只有 `random()`，给的是 64 位整数。**
  **要 0 到 1 之间的小数，得自己取模再除，就是第三行那个写法。**
  **注意 `%` 在 SQL 里是取模运算符，和 Python 一样；但在 `LIKE` 里它是通配符。**
  **同一个符号在两个位置两种含义，这是 SQL 里少数几个「靠位置区分」的地方。**

  随手抽的四行：id  v
  -----------------------
  168               57.16
  114               37.18
  198               68.26
  82                25.34
  **`ORDER BY random() LIMIT 4` 是最常用的「随机抽几行」。**
  **它的代价是整表排序，小表无所谓，大表会很慢——因为每一行都要算一次随机数再排。**
  **更快的做法是先随机取一个 id 起点再取几条，但那要求 id 连续，适用面窄。**
  **所以「随机抽」这件事，先看表多大再决定用哪种。**

  五、浮点误差：别用等号比小数
  --------------------------------------------------------------------

  写法                         结果
  ---------------------------------------------------
  `0.1 + 0.2`                  0.30000000000000004441
  `0.1 + 0.2 = 0.3`            0
  `round(0.1 + 0.2, 2) = 0.3`  1
  **第二行给 0，意思是「不相等」。**
  **这不是数据库的错，是二进制小数存不下 0.1 和 0.2 这种十进制小数。**
  **所以金额一律用 `DECIMAL` 存，比较之前先 `ROUND` 到业务精度，再比。**
  **用 `DOUBLE` 存钱，迟早会在某一笔上差一分钱，而且极难复现。**

floor(-2.7) 给 -3.0 是这一段的第一个坑。 「向下」不是「去掉小数」，是「往更小的方向」。 三种口径那四行排下来是 2.57 / -2.57、2.56 / -2.57。 只有第三列在负数上不一样，这就是「向下取整」和「截断」的分界。 注册 truncate 之后给 2.56 和 -2.56，和四舍五入的 2.57 / -2.57 正好差一分。 金额上这一分钱会被对账抓出来，所以口径必须先定。 最后两行是 0 和 1：直接比不等，先 ROUND 再比就等。 浮点数的正确比较方式就是「先修到精度再比」，没有别的办法。

30 秒实验

1. 把 floor(-2.7) 换成 ceil(-2.7)，看它给几，想想为什么是 -2。
2. 连续跑三次 SELECT random()，确认每次都不一样。
3. 把 round(2.567, 2) 的位数改成 0 和 1，看结果怎么跳。

常见误区

1. 算分页数用 floor，最后一页只有一条数据时少一页。
2. 把「截断」和「向下取整」当成一回事，负数上多扣了一分钱。
3. 用 = 比较两个浮点数，得到 0 还以为逻辑写错了。

下一篇 10.3 进字符串函数，重点是「索引从 1 开始」和「拼接遇 NULL 整体变 NULL」。

• ☐ （本篇完，下一篇 10.3 字符串函数）

相关笔记

• MySQL 函数
• MySQL 数据类型
• 运算符
• 🛢️ MySQL从入门到精通 · 索引