Pandas插入SQLite的文本数据用=匹配失效仅LIKE生效问题

导致该问题的常见原因有两个：

1. Pandas调用to_sql插入数据时，未明确指定字段类型，SQLite自动将带特殊控制字符的字符串存储为BLOB类型，而非TEXT类型。=匹配时会严格对比字段类型与值的字节序列，BLOB类型和TEXT字符串对比永远不相等；但LIKE查询会自动把BLOB转为TEXT后再做匹配，所以能返回结果。
2. DataFrame中的字符串附带了命令行输出不可见的特殊字符，最常见的是尾部\x00空字符、零宽空格\u200b、不间断空格\xa0等，这些字符不会在sqlite命令行的查询结果中显示，但会被=精确匹配识别，LIKE的模糊匹配逻辑会忽略部分尾部控制字符，所以前后加通配符都能命中。

你可以先执行以下SQL确认具体原因：

-- 对比字段实际长度和目标字符串长度，同时查看字段类型
SELECT length(textfield), length("Some text"), typeof(textfield) FROM test;

如果typeof结果是blob就属于第一种类型问题，如果长度不一致就属于第二种隐藏字符问题。

最优方案：插入前预处理（一劳永逸，不影响查询性能） #

1. 清洗DataFrame中的字符串字段，去除所有不可见控制字符：

# 清洗所有控制字符、零宽空格、不间断空格
df['textfield'] = df['textfield'].str.replace(r'[\x00-\x1F\x7F-\xA0\u200B-\u200F]', '', regex=True)

2. 调用to_sql时强制指定字段为TEXT类型，避免自动转为BLOB：

from sqlalchemy.types import TEXT

df.to_sql(
    name="test",
    con=你的数据库连接对象,
    dtype={"textfield": TEXT()}, # 强制字段类型为TEXT
    if_exists="replace",
    index=False
)

处理后再用=精确匹配就可以正常命中，性能最高。

临时查询方案（不需要修改现有数据，性能远高于LIKE） #

如果不能修改现有数据，可以用类型转换+精确匹配的方式替代LIKE，性能比模式匹配高30%以上：

• 针对BLOB类型问题：

SELECT * FROM test WHERE CAST(textfield AS TEXT) = "Some text";

• 针对隐藏空字符问题：

-- 去除尾部空字符后匹配
SELECT * FROM test WHERE RTRIM(textfield, CHAR(0)) = "Some text";
-- 如果是其他隐藏字符可以用TRIM指定清理范围

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