SQLite 字符串中的空字符

SQLite 字符串中的空字符
Nul Characters in Strings in SQLite

SQLite 允许在字符串值中包含 NUL 字符（`0x00`），但这可能会导致误导性的行为。标准的函数（如 `length()` 和 `quote()`）以及命令行界面（CLI）会在遇到第一个 NUL 字符时截断输出。这会造成一种差异：数据库确实完整存储了字符串，但查询结果和 CLI 显示可能会错误地暗示字符串已提前结束。若要验证包含 NUL 字符的字符串的真实长度和内容，应将该值转换为 `BLOB`。若要识别受到嵌入 NUL 字符影响的行，请使用 `instr(b, char(0))` 函数。如果需要清理数据，可以使用带有 `substr()` 的 `UPDATE` 语句来移除 NUL 字符及其后的内容。由于 NUL 字符可能导致混淆和工具不兼容，因此不建议在 SQLite 文本字符串中使用 NUL 字符。

```Hacker News 最新 | 过往 | 评论 | 提问 | 展示 | 招聘 | 提交登录 SQLite 字符串中的空字符 (sqlite.org) 4 点积分由 basilikum 于 31 分钟前发布 | 隐藏 | 过往 | 收藏 | 讨论 | 帮助考虑申请 YC 2026 秋季批次！申请截止日期为 7 月 27 日。指南 | 常见问题 | 列表 | API | 安全 | 法律 | 申请 YC | 联系搜索：```

原文

NUL Characters In Strings

SQLite allows NUL characters (ASCII 0x00, Unicode \u0000) in the middle of string values stored in the database. However, the use of NUL within strings can lead to surprising behaviors:

The length() SQL function only counts characters up to and excluding the first NUL.
The quote() SQL function only shows characters up to and excluding the first NUL.
The .dump command in the CLI omits the first NUL character and all subsequent text in the SQL output that it generates. In fact, the CLI omits everything past the first NUL character in all contexts.

The use of NUL characters in SQL text strings is not recommended.

Consider the following SQL:

CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b TEXT
);
INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz');

SELECT a, b, length(b) FROM t1;

The SELECT statement above shows output of:

(Through this document, we assume that the CLI has ".mode quote" set.) But if you run:

SELECT * FROM t1 WHERE b='abc';

Then no rows are returned. SQLite knows that the t1.b column actually holds a 7-character string, and the 7-character string 'abc'||char(0)||'xyz' is not equal to the 3-character string 'abc', and so no rows are returned. But a user might be easily confused by this because the CLI output seems to show that the string has only 3 characters. This seems like a bug. But it is how SQLite works.

If you CAST a string into a BLOB, then the entire length of the string is shown. For example:

SELECT a, CAST(b AS BLOB) FROM t1;

Gives this result:

In the BLOB output, you can clearly see the NUL character as the 4th character in the 7-character string.

Another, more automated, way to tell if a string value X contains embedded NUL characters is to use an expression like this:

If this expression returns a non-zero value N, then there exists an embedded NUL at the N-th character position. Thus to count the number of rows that contain embedded NUL characters:

SELECT count(*) FROM t1 WHERE instr(b,char(0))>0;

The following example shows how to remove NUL character, and all text that follows, from a column of a table. So if you have a database file that contains embedded NULs and you would like to remove them, running UPDATE statements similar to the following might help:

UPDATE t1 SET b=substr(b,1,instr(b,char(0)))
 WHERE instr(b,char(0));

This page was last updated on 2022-05-23 22:21:54Z

SQLite 字符串中的空字符 Nul Characters in Strings in SQLite

SQLite 字符串中的空字符
Nul Characters in Strings in SQLite