10.7 其他函数 · 波仔的 AI 编程实战课

📄 一句话概括

FORMAT 干两件事：加千分位、保留小数位；它返回字符串，不是数字。 CAST 转换是「能读多少读多少」，读不出来给 0，不报错。 MySQL 的 utf8 最多 3 字节，存不下 emoji；要存全必须用 utf8mb4。

📝 本章和前面几章的关系

10.7 讲三个：FORMAT、改字符集、改字段类型。 这三个在沙箱里分别是 printf、Python 编解码、CAST。 这一篇也是第 10 章的收尾，最后把「类型和编码」这条线收一下，它们是本课反复出现的分叉点。

一、FORMAT：千分位与小数位

MySQL 的 FORMAT(x, n) 一次干两件事：加千分位、保留 n 位小数。 沙箱的 printf 要自己写格式串，%,.2f 里那个逗号就是千分位开关。

写法	结果
`printf('%,d', 1234567)`	1,234,567
`printf('%.2f', 3.14159)`	3.14
`printf('%,.2f', 1234567.891)`	1,234,567.89

第三行四舍五入到 1234567.89，不是截断。 格式化函数一律做四舍五入，要截断得先 TRUNCATE 再格式化。 还有一件事：FORMAT 返回的是字符串，不是数字。 拿它去参与运算会被隐式转回来，中间可能丢精度，所以只在「显示」这一步用。 这一条可以推广成一句：显示归显示，存储归存储。 把千分位、货币符号存进库里，后面所有计算都要先洗一遍数据。

二、CAST：类型转换

转换会「能读多少读多少」，遇到字母就停。 cast('123abc' AS INTEGER) 给 123，cast('abc' AS INTEGER) 给 0。

写法	结果
`cast('123abc' AS INTEGER)`	123
`cast('abc' AS INTEGER)`	0
`cast(3.99 AS INTEGER)`	3
`typeof(cast(123 AS TEXT))`	text

第二行那个 0 是很多「脏数据混进来还查得出来」的根源。 想让它报错，得在应用层校验，SQL 的 CAST 不会替你拦。 第三行给 3，是截断不是四舍五入。 所以「先 CAST 再算」和「先 ROUND 再算」结果不同，顺序不能随手换。 MySQL 的 CAST 目标类型更多：SIGNED、UNSIGNED、DECIMAL、CHAR、DATE、DATETIME。 沙箱这边只有 INTEGER、REAL、TEXT、BLOB、NUMERIC 这几种。

三、字符集：从「存不存得下」看

同一个字符在不同编码下占的字节数不同，所以「一列能存多少字」必须先问编码。 英文在三种编码里都是 3 字节，中文在 UTF-8 是 6、GBK 是 4。

字符串	字符数	UTF-8	GBK
`abc`	3	3	3
`张三`	2	6	4
一个笑脸	1	4	0

第三行那个笑脸在 GBK 里给 0，因为存不下。 这就是「四字节字符」的由来，也是 MySQL 里 utf8 和 utf8mb4 的分界线。 utf8mb4 里的 mb4 就是「最多 4 字节」。 MySQL 那个叫 utf8 的其实是残缺版，最多 3 字节。 所以建库第一件事就把字符集定成 utf8mb4，别等出问题再改。 改字符集要动整张表，数据量大的时候是一次停机操作。

四、把「类型」这条线收一下

typeof 是 SQLite 特有的：它是动态类型，同一列不同行可以是不同类型。 MySQL 是静态类型，列一建就定了，所以没有 typeof 这种东西。

想知道什么	沙箱	MySQL
这个值是什么类型	`typeof(x)`	没有对应函数
转成另一种类型	`cast(x AS ...)`	`cast` / `convert`
转成另一种字符集	应用层编解码	`convert(x using gbk)`

这个差别解释了本课反复出现的那些「怪结果」：沙箱里 '9' > 10 给 1，MySQL 给 0。 因为沙箱按「值现在是什么类型」比，MySQL 按「列声明的类型」比。 第 10 章到这里结束。函数这一章看着是背清单，真正要记的只有三件事： 同名可能不同义、缺的函数能自己补、类型和编码决定结果。

五、动手 Demo：格式化、类型转换与字符集转换

这一段从「显示」走到「存储」：先是千分位格式化，再是类型转换，最后用 Python 的编解码量出「同一个字在不同编码下几字节」。

import sqlite3

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

# ① 千分位
for f in [u"printf('%,d', 1234567)", u"printf('%.2f', 3.14159)",
          u"printf('%,.2f', 1234567.891)"]:
    print(f, c.execute(u"SELECT " + f).fetchone()[0])

# ② 类型转换
for f in [u"cast('123abc' AS INTEGER)", u"cast('abc' AS INTEGER)",
          u"cast(3.99 AS INTEGER)"]:
    print(f, c.execute(u"SELECT " + f).fetchone()[0])

# ③ 同一个字的三种编码长度
for s in [u"abc", u"张三", u"\U0001F600"]:
    print(s, len(s), len(s.encode(u"utf-8")),
          len(s.encode(u"gbk", u"ignore")))

实跑输出：

--------------------------------------------------------------------
第五十课：格式化、类型转换与字符集转换
--------------------------------------------------------------------

  **教材 10.7 讲三个「其他函数」：`FORMAT`、改字符集、改字段类型。**
  **这三件事在沙箱里分别是 `printf`、Python 编解码、`CAST`。**
  **这一课也是第 10 章的收尾，最后把「类型」这条线收一下。**

  一、FORMAT：千分位与小数位
  --------------------------------------------------------------------

  写法                            结果
  ---------------------------------------------------------
  `printf('%,d', 1234567)`        1,234,567
  `printf('%.2f', 3.14159)`       3.14
  `printf('%,.2f', 1234567.891)`  1,234,567.89
  `format(1234567.891, 2)`        沙箱无此函数，用 `printf`
  **MySQL 的 `FORMAT(x, n)` 一次干两件事：加千分位、保留 n 位小数。**
  **沙箱的 `printf` 要自己写格式串，`%,.2f` 那个逗号就是千分位开关。**
  **注意第三行四舍五入到 1234567.89，不是截断。**
  **格式化函数一律做四舍五入，要截断得先 `TRUNCATE` 再格式化。**
  **还有一件事：`FORMAT` 返回的是字符串，不是数字。**
  **拿它去参与运算会被隐式转回来，中间可能丢精度，所以只在「显示」这一步用。**

  二、CAST：类型转换
  --------------------------------------------------------------------

  写法                         结果
  ---------------------------------
  `cast('123abc' AS INTEGER)`  123
  `cast('abc' AS INTEGER)`     0
  `cast(3.99 AS INTEGER)`      3
  `cast(123 AS TEXT)`          123
  `typeof(cast(123 AS TEXT))`  text
  **第一行给 123：转换会「能读多少读多少」，遇到字母就停。**
  **第二行给 0：一个数字都读不出来时，给 0 而不是报错。**
  **第二行这个 0 是很多「脏数据混进来还查得出来」的根源。**
  **想让它报错，得在应用层校验，SQL 的 `CAST` 不会替你拦。**
  **第三行给 3，是截断不是四舍五入。**
  **所以「先 `CAST` 再算」和「先 `ROUND` 再算」结果不同，顺序不能随手换。**
  **MySQL 的 `CAST` 目标类型更多：`SIGNED`、`UNSIGNED`、`DECIMAL`、`CHAR`、`DATE`、`DATETIME`。**
  **沙箱这边只有 `INTEGER`、`REAL`、`TEXT`、`BLOB`、`NUMERIC` 这几种。**

  三、字符集转换：从「存不存得下」看
  --------------------------------------------------------------------

  字符串  字符数  UTF-8 字节  GBK 字节
  ------------------------------------
  abc     3       3           3
  张三    2       6           4
  😀      1       4           0
  **第一行英文：三个数都是 3，三种编码在 ASCII 范围内完全一致。**
  **这也是「英文网站感觉不到编码问题」的原因。**
  **第二行中文：字符 2 个，UTF-8 占 6 字节，GBK 占 4 字节。**
  **同一个字在不同编码下长度不同，所以「一列能存多少字」这个问题必须先问编码。**
  **第三行那个笑脸：UTF-8 要 4 字节，GBK 存不下，所以我用了 `ignore` 让它跳过。**
  **这就是「四字节字符」的由来，也是 MySQL 里 `utf8` 和 `utf8mb4` 的分界线。**

  四、utf8 与 utf8mb4：名字骗人的一对
  --------------------------------------------------------------------

  项                  utf8（MySQL 的）  utf8mb4
  ---------------------------------------------
  每个字符最多几字节  3                 4
  能不能存 emoji      不能              能
  能不能存生僻字      部分不能          能
  **MySQL 里的 `utf8` 其实是「UTF-8 的残缺版」，最多 3 字节，存不下 4 字节的字符。**
  **要存全，必须用 `utf8mb4`。这个名字的差别不是「多一个 mb4」这么简单。**
  **后果很具体：用户昵称里带一个 emoji，插入就报「Incorrect string value」。**
  **而这个问题在测试阶段往往发现不了，因为测试数据里没人用 emoji。**
  **所以建库第一件事就把字符集定成 `utf8mb4`，别等出问题再改——改起来要动整张表。**

  五、把「类型」这条线收一下
  --------------------------------------------------------------------

  想知道什么        沙箱              MySQL
  ----------------------------------------------------------
  这个值是什么类型  `typeof(x)`       没有对应函数
  转成另一种类型    `cast(x AS ...)`  `cast` / `convert`
  转成另一种字符集  应用层编解码      `convert(x using gbk)`
  **`typeof` 是 SQLite 特有的：它是「动态类型」，同一个列不同行可以是不同类型。**
  **MySQL 是静态类型，列一建就定了，所以没有 `typeof` 这种东西。**
  **这个差别解释了本课反复出现的那些「怪结果」：沙箱里 `'9' > 10` 给 1，MySQL 给 0。**
  **因为沙箱按「值现在是什么类型」比，MySQL 按「列声明的类型」比。**
  **第 10 章到这里结束。函数这一章看着是背清单，真正要记的只有三件事：**
  **同名可能不同义、缺的函数能自己补、类型和编码决定结果。**

格式化那三行是 1,234,567、3.14、1,234,567.89。 逗号位置由引擎自己算，不用你数位数。 类型转换那三行是 123、0、3。 中间那个 0 最危险：它把「转换失败」伪装成了一个合法数字。 编码那三行是 3/3/3、2/6/4、1/4/0。 最后一列那个 0 就是「GBK 存不下这个字」，MySQL 里 utf8 存不下它也是同样的道理。 这一段的三个小节其实是一条线：显示 → 类型 → 编码。 数据从库里出来，先转换类型，再格式化显示，中间任何一步搞错都会看到怪结果。

30 秒实验

把 printf('%,d', ...) 换成 printf('%d', ...)，看千分位消失。
把 cast('12abc34' AS INTEGER) 跑一次，看它是 12 还是 1234。
把笑脸换成「𠮷」这种生僻字，看它在 UTF-8 里占几字节。

常见误区

把千分位格式化后的字符串存进库，后面每次计算都要先洗数据。
以为 CAST 失败会报错，其实它给 0，脏数据就这么混进来了。
建库时用了 utf8，用户昵称里带 emoji 就插不进去。

自测三问（点开对答案）

1. `FORMAT(x, n)` 返回的是数字还是字符串？ —— 字符串。它已经带上了千分位和小数位，所以只能用来显示，不能直接参与计算。 2. `CAST('abc' AS INTEGER)` 为什么给 0 而不是报错？ —— 因为 SQL 的类型转换是「能读多少读多少」，一个字符都读不出来时给 0，不抛异常。要拦脏数据得在应用层做。 3. MySQL 的 `utf8` 和 `utf8mb4` 差在哪？ —— `utf8` 最多 3 字节，存不下 emoji 和部分生僻字；`utf8mb4` 最多 4 字节，能存全。建库应该直接用 `utf8mb4`。

第 10 章到这里结束。下一篇进第 11 章：索引，全书最该慢慢读的一章。

☐ （本篇完，下一篇 11.1 索引概述）