一、大小写与长度

UPPER 和 UCASE 在 MySQL 里是同义词，沙箱只有 UPPER。 教材写 UCASE 的地方，这里一律换成 UPPER。

第四行那个 2 再强调一次：沙箱数的是字符，MySQL 数的是字节。 这个差别在「长度校验」上会直接导致业务规则跑偏。 比如「昵称不超过 10 个字」，用 LENGTH 校验的话，四个汉字就被判超长。 这种问题在测试阶段很难发现，因为测试数据往往是英文。

二、截取：索引从 1 开始

SUBSTR(s, 起点, 长度) 的起点是 1，不是 0。 这一点和 Python 的切片相反，写惯 Python 的人第一次用 SQL 都会错一位。

第三行给 def：起点写负数表示「从末尾数」，-3 是倒数第三个字符。 取扩展名、取后四位这类需求，用负数起点最省事。 LEFT(s, n) 就是 SUBSTR(s, 1, n) 的简写，沙箱没有，补上只要一行。 注意 LEFT 在 MySQL 里还是 LEFT JOIN 的关键字，靠后面的括号区分。

三、INSERT(s1, x, len, s2)：一个叫 insert 的函数

这个函数按「位置」替换：从第 x 位起，把 len 个字符换成 s2。 它和 REPLACE 不一样，后者按内容找、全换。 沙箱里照抄会报语法错，不是「找不到函数」。 因为 INSERT 是语句关键字，解析器一看到它就往「插入语句」那条路上走。

def strinsert(s1, x, ln, s2):
    i = int(x) - 1
    return s1[:i] + s2 + s1[i + int(ln):]

c.create_function(u"strinsert", 4, strinsert)

strinsert('abcdef', 2, 3, 'X') 给 aXef：第 2 位起换 3 个。 strinsert('abcdef', 1, 0, 'X') 给 Xabcdef：len 写 0 就是纯插入。 函数名不能叫 insert，因为它是关键字。 这类「函数名和关键字撞车」的情况，换个名字注册就行，语义一点不变。

四、REVERSE、FIELD、LOCATE：三个都要注册

FIELD(s, s1, s2, ...) 返回 s 出现在第几个，找不到给 0。 它最常用的地方不是查询，是排序：ORDER BY FIELD(status, '待付', '已付')。 LOCATE 和 INSTR 是一对，功能一样，参数顺序相反。 LOCATE(要找的, 在谁里面)，INSTR(在谁里面, 要找的)。 写反了不报错，只是返回 0，然后你会以为「找不到」。 这种错只能靠对照结果发现，所以正文里这两行特意并排放。 REVERSE 是最简单的一个，一行代码就够。 但它能说明一件事：字符倒序和字节倒序不是一回事，前者不会把汉字拆散。

五、拼接、去空格与 NULL

TRIM 只去两端空格，不去中间；REPLACE 换的是内容，不看位置。 两个一起用，就是「清洗用户输入」最常见的两步。

三行打上中括号才看得出空白：[a] 是两端都去掉了，[a ] 是只去了左边。 不打标记直接看输出，三个结果长得一模一样。 拼接在沙箱里用 ||，在 MySQL 里 || 是逻辑或，拼接要用 CONCAT。 除非把 sql_mode 设成 PIPES_AS_CONCAT，否则两边同符号不同义。 更值得记的是 None 的传染：'a' || NULL 给 NULL，CONCAT('a', NULL) 也给 NULL。 只要有一个参数是空，整串就没了，不是「跳过它继续拼」。 Oracle 的 || 反过来，它把 NULL 当空串，所以 'a' || NULL 给 a。 同一句从一个库搬到另一个库，结果差一整串，这是最典型的水土不服。

六、动手 Demo：字符串函数，和索引从 1 开始这件事

这一段补了四个替身：left、strinsert、reverse、field、locate。 补完之后教材里的句子几乎能原样敲。

import sqlite3

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

# ① 截取：索引从 1 开始
for s in [u"substr('abcdef', 1, 3)", u"substr('abcdef', 2, 3)",
          u"substr('abcdef', -3)"]:
    print(s, c.execute(u"SELECT " + s).fetchone()[0])

# ② 注册四个替身
c.create_function(u"left", 2, lambda s, n: s[:int(n)])
c.create_function(u"reverse", 1, lambda s: s[::-1])
c.create_function(u"field", -1,
                   lambda s, *a: next((i for i, x in enumerate(a, 1)
                                     if x == s), 0))
c.create_function(u"locate", 2,
                   lambda s1, s: s.find(s1) + 1 if s.find(s1) >= 0 else 0)
print(c.execute(u"SELECT left('abcdef', 3), reverse('abc')").fetchone())
print(c.execute(u"SELECT field('b','a','b','c'), locate('cd','abcdef')").fetchone())

# ③ 拼接遇 NULL
print(c.execute(u"SELECT 'a' || NULL").fetchone())    # (None,)

实跑输出：

--------------------------------------------------------------------
第四十六课：字符串函数，和索引从 1 开始这件事
--------------------------------------------------------------------

  **教材 10.3 讲了十一组字符串函数。**
  **沙箱里有 `UPPER`、`SUBSTR`、`TRIM`、`INSTR` 这几组。**
  **缺的是 `LEFT`、`REVERSE`、`FIELD`、`LOCATE`、`INSERT`，一会儿挨个补上。**
  **这一课先把能跑的跑完，再把缺的注册成替身。**
  **顺手钉死一件事：字符串的索引从 1 开始，不是 0。**

  一、大小写与长度
  --------------------------------------------------------------------

  写法              结果
  ----------------------
  `upper('ab')`     AB
  `lower('AB')`     ab
  `length('abc')`   3
  `length('中文')`  2
  **`UPPER` 和 `UCASE` 在 MySQL 里是同义词，沙箱只有 `UPPER`。**
  **所以教材写 `UCASE` 的地方，这里一律换成 `UPPER`。**
  **第四行那个 2 再强调一次：沙箱数的是字符，MySQL 数的是字节。**
  **这个差别在「长度校验」上会直接导致业务规则跑偏。**

  二、截取：SUBSTRING 与 LEFT
  --------------------------------------------------------------------

  写法                      结果
  ------------------------------
  `substr('abcdef', 1, 3)`  abc
  `substr('abcdef', 2, 3)`  bcd
  `substr('abcdef', -3)`    def
  **第一行给 `abc`，说明起点是 1 而不是 0。**
  **这一点和 Python 的切片相反，写惯了 Python 的人第一次用 SQL 都会错一位。**
  **第三行给 `def`：起点写负数表示「从末尾数」。**
  **`-3` 是倒数第三个字符，这个用法在取扩展名、取后四位时很好用。**

  写法                      结果
  ------------------------------
  `left('abcdef', 3)`       abc
  `substr('abcdef', 1, 3)`  abc
  **两行结果一样，`LEFT(s, n)` 就是 `SUBSTR(s, 1, n)` 的简写。**
  **MySQL 两个都有，沙箱只有后者，所以注册一个 `left` 只是为了照抄教材方便。**
  **注意 `left` 在 MySQL 里还是 `LEFT JOIN` 的关键字。**
  **函数名和关键字同名时，靠后面的括号区分——这类重名在 SQL 里不算少见。**

  三、INSERT(s1, x, len, s2)：一个名字叫 insert 的函数
  --------------------------------------------------------------------
  **照抄报错：`near "insert": syntax error`。**
  **这条报错是语法错，不是「找不到函数」。**
  **因为 `INSERT` 在 SQLite 里是语句关键字，解析器一看到它就往「插入语句」那条路上走。**

  写法                              结果
  -----------------------------------------
  `strinsert('abcdef', 2, 3, 'X')`  aXef
  `strinsert('abcdef', 1, 0, 'X')`  Xabcdef
  **第一行把第 2 位开始的 3 个字符换成 `X`，得到 `aXef`。**
  **第二行在开头插一个 `X`，得到 `Xabcdef`——`len` 写 0 就是纯插入。**
  **这个函数的作用是「替换中间一段」，和 `REPLACE` 不一样。**
  **`REPLACE` 按内容找、全换；这个按位置换、只换一段。**

  四、REVERSE、FIELD、LOCATE：三个都要注册
  --------------------------------------------------------------------

  写法                         结果
  ---------------------------------
  `reverse('abc')`             cba
  `field('b', 'a', 'b', 'c')`  2
  `field('z', 'a', 'b', 'c')`  0
  `locate('cd', 'abcdef')`     3
  `instr('abcdef', 'cd')`      3
  **`FIELD` 返回「第几个」，从 1 数起，找不到给 0。**
  **它常用来做自定义排序：`ORDER BY FIELD(status, '待付', '已付', '已发')`。**
  **最后两行要注意参数顺序，这是最容易写反的一对。**
  **`LOCATE(要找的, 在谁里面)`，`INSTR(在谁里面, 要找的)`——一个是「针在前」，一个是「布在前」。**
  **写反了不报错，只是返回 0，然后你会以为「找不到」。**
  **这种错只能靠对照结果发现，所以这两行特意并排放。**

  五、拼接、去空格与替换
  --------------------------------------------------------------------

  写法                         结果
  --------------------------------------------------
  `'a' || 'b' || 'c'`          abc
  `concat('a', 'b')`           沙箱无此函数，用 `||`
  `trim('  a  ')`              [a]
  `ltrim('  a  ')`             [a  ]
  `rtrim('  a  ')`             [  a]
  `replace('aXbX', 'X', '-')`  a-b-
  **第一行是 SQLite 的拼接写法，MySQL 里 `||` 是逻辑或，拼接要用 `CONCAT`。**
  **这是本课反复出现的分叉：同一个符号，两边意思完全不同。**
  **第二行写「沙箱无此函数」而不是给结果，因为 `concat` 确实没注册。**
  **要照抄教材的话，注册一个变参 `concat` 就行，和前面那三个一样的套路。**
  **打上中括号才看得出空白：`[a]` 是两端都去掉了，`[  a]` 是只去了右边，`[a  ]` 是只去了左边。**
  **`TRIM` 只去两端空格，不去中间；`REPLACE` 换的是内容，不看位置。**
  **两个函数一起用，就是「清洗用户输入」最常见的两步。**

截取那三行是 1 起点的最好说明：从第 1 位取 3 个是 abc，从第 2 位取是 bcd。 要是按 0 起点理解，第一行会以为「什么都不取」。 LEFT 和 SUBSTR 的结果一样，这是冗余的两种写法，MySQL 两个都提供。 冗余不是坏事：取前三个字符时 LEFT 比 SUBSTR(s, 1, 3) 好读。 FIELD 和 LOCATE 那两行要看出参数顺序的差别： 一个「给值找位置」，一个「给针找布」，locate 是针在前。 最后一行给 (None,)，就是 NULL。 拼接遇空就整体消失，这一点和「跳过空值继续拼」的直觉不一样。

30 秒实验

1. 把 substr('abcdef', 1, 3) 改成 substr('abcdef', 0, 3)，看它给几。
2. 把 locate 的两个参数写反，确认它返回 0。
3. 试 'a' || ifnull(NULL, '')，看拼出来的结果是 a 还是空。

常见误区

1. 按 0 起点写 SUBSTR，结果整体错一位。
2. 把 LOCATE 的参数写反，返回 0 还以为字符串里没有。
3. CONCAT 里有一个 NULL，整串变 NULL，还以为是函数写错了。

下一篇 10.4 进日期时间函数，重点是「now 在沙箱里是 UTC」这件事。

• ☐ （本篇完，下一篇 10.4 日期和时间函数）

相关笔记

• MySQL 函数
• 数学函数
• 单表查询
• 🛢️ MySQL从入门到精通 · 索引