SQLite导入CSV时十六进制数未被识别为数值的原因咨询

问题原因 #

SQLite的.import --csv命令自动推断列类型时，只会把无特殊前缀的纯数字字符串识别为数值类型（整数/浮点数）。带0x前缀的十六进制格式不在默认的数值识别规则范围内，所以B列的0x1会被当成普通字符串存储。

查询时的差异逻辑：

• 执行WHERE a = 0x1时，SQLite会把A列的字符串"1"隐式转换为数值1，而0x1在SQL语法里是十进制1的十六进制表示，两者数值相等，因此能匹配到结果。
• 执行WHERE b = 0x1时，B列存储的是字符串"0x1"，和数值1（0x1的实际值）类型不匹配且内容不等，自然查不到结果。

解决办法 #

方法1：导入后手动转换列值 #

先按原流程导入CSV，再通过SQL语句将B列的十六进制字符串转换为数值：

# 导入CSV文件
sqlite3 my.db ".import --csv my.csv somet"
# 转换B列：去掉0x前缀，按十六进制规则转整数
sqlite3 my.db "UPDATE somet SET B = CAST(SUBSTR(B, 3) AS INTEGER, 16);"
# 验证转换结果
sqlite3 my.db "SELECT * FROM somet WHERE B = 0x1;"

方法2：先指定表结构再导入 #

先手动创建包含数值类型列的表，再导入CSV并转换列值：

# 创建指定结构的表
sqlite3 my.db "CREATE TABLE somet(A TEXT, B INTEGER);"
# 导入CSV数据
sqlite3 my.db ".import --csv my.csv somet"
# 转换B列的十六进制字符串为数值
sqlite3 my.db "UPDATE somet SET B = CAST(SUBSTR(B, 3) AS INTEGER, 16);"

方法3：查询时动态转换 #

如果不想修改表内数据，可在查询时直接转换B列字符串进行匹配：

sqlite3 my.db "SELECT * FROM somet WHERE CAST(SUBSTR(B, 3) AS INTEGER, 16) = 0x1;"

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