UTF-8编码SQLite中SUBSTR与INSTR多字节字符兼容问题

问题描述 #

在UTF-8编码的SQLite数据库里，SUBSTR函数按字节而非字符处理字符串，这在包含多字节字符的场景下会导致功能失效。而INSTR函数基于Unicode码点返回位置，两者配合提取子串时会出现裁剪错误。

简化测试用例 #

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (title TEXT);
INSERT INTO test VALUES(X'3232313031315f313335365f417072c3a8732d6d6964695f31312d31302d32303232220d');
INSERT INTO test VALUES(X'3232313031335f313535315f5f5245504f52544147455f31372d30312d32303230220d');
COMMIT;

环境与数据信息 #

sqlite> .version
SQLite 3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600fb58238alt1
zlib version 1.2.11
gcc-8.3.1 20191121 (Red Hat 8.3.1-5)
sqlite> PRAGMA encoding;
UTF-8
sqlite> .schema
CREATE TABLE test (title TEXT);
sqlite> select * from test;
221011_1356_Après-midi_11-10-2022"
221013_1551__REPORTAGE_17-01-2020"

问题复现 #

两个字符串的字符长度相同，但第一个字符串包含双字节字符è，字节长度比第二个多1。使用INSTR返回的字符位置是34，但SUBSTR按字节裁剪，导致第一个字符串被多截了一部分：

sqlite> select INSTR(title, '"'), SUBSTR(title, 0, INSTR(title, '"')) from test;
34|221011_1356_Après-midi_11-10-202
34|221013_1551__REPORTAGE_17-01-2020

该问题在Windows下的SQLite 3.41.2版本中依然存在。

解决方法 #

将字段CAST为varchar即可解决此问题，让SUBSTR按字符而非字节处理：

sqlite> select INSTR(cast(title as varchar), '"'), SUBSTR(cast(title as varchar), 0, INSTR(cast(title as varchar), '"')) from test; -- nbk提供的解决方案，核心是转换为varchar让SUBSTR正常工作
34|221011_1356_Après-midi_11-10-2022
34|221013_1551__REPORTAGE_17-01-2020

内容的提问来源于stack exchange，提问作者Bruno Rohère