SQLite主键字段查询无结果 表主键定义未生效问题排查

问题现象 #

表结构定义如下：

CREATE TABLE tz ('id' INT(4),'name' TEXT NOT NULL PRIMARY KEY);

查询行为不符合预期：

• 基于id字段执行等值查询可正常返回匹配结果，示例：

sqlite> select * from tz where id = 10;
10|Africa/Brazzaville

• 确认目标数据存在的前提下，基于主键name执行等值查询无任何结果返回，示例：

sqlite> select * from tz where name = 'Pacific/Rarotonga';
-- 无结果返回

根本原因 #

最高概率的诱因是数据导入时换行符处理错误，name字段尾部残留不可见的回车符（\r，ASCII码13），和现象完全匹配：

1. 如果你是用SQLite自带的.import命令导入文本/CSV格式的数据源，而源文件是Windows系统生成的CRLF（\r\n）换行格式，SQLite默认仅将\n识别为行分隔符，每行末尾的\r会被判定为最后一列（即name列）的内容留存。
2. 两个字段的比较规则差异造成了查询表现不一致：
   • id为INT类型，SQLite的类型亲和性会在比较时自动将字段值转为整数，转换过程会自动忽略尾部的非数字字符（包括\r、空格等），因此即使id值尾部带\r，where id = 10也能正常命中结果。
   • name为TEXT类型主键，等值匹配为严格逐字节比较，只要尾部多了不可见的\r，就会和SQL语句中写入的无\r字符串不相等，自然返回空结果。

验证与修复方案 #

问题验证 #

执行以下语句确认是否为尾部不可见字符问题：

-- 用后缀模糊匹配绕过尾部字符检查，能返回记录则说明确实存在多余尾部字符
select * from tz where name like 'Pacific/Rarotonga%';
-- 查看字段实际长度，Pacific/Rarotonga正常长度为18，返回19则说明多了1个不可见字符
-- hex函数可以直接查看字段的二进制值，能直接看到尾部是否存在0D（即\r的十六进制值）
select length(name), id, hex(name) from tz where name like '%Rarotonga%';

修复操作 #

确认问题后执行批量更新，清理所有name字段尾部的回车符：

update tz set name = rtrim(name, char(13));

清理完成后再次执行等值查询即可正常返回结果。

如果模糊查询也无法命中记录，再排查两个低概率问题：

• 检查查询字符串拼写是否和存储值完全一致，注意大小写、特殊字符差异
• 执行PRAGMA table_info(tz);确认name字段的排序规则未被修改为非常规规则，默认BINARY排序规则不会影响等值匹配逻辑

内容的提问来源于stack exchange，提问作者clearlight