SQLite 数据库以轻量、零配置著称是移动端和嵌入式场景的事实标准。但很多从 MySQL、PostgreSQL 转到 SQLite 的朋友第一脚踩的坑往往就是它的数据类型——看着像字段类型实际上是一套完全不同的“动态类型”体系。这篇文章不抄官方文档我从实际操盘过的几个项目出发把 SQLite 里存储类、type affinity、字段改型、大小比较这些事拆开揉碎讲清楚。1. 存储类与亲和性SQLite 类型体系的核心逻辑1.1 五大存储类而不是“字段类型”SQLite 官方的术语很谨慎它不叫 field type叫 Storage Class存储类。整个数据库里任何值只属于以下五类之一存储类含义常见来源NULL空值未赋值字段INTEGER有符号整数按需占用 1/2/3/4/6/8 字节自增 ID、计数REAL8 字节 IEEE 浮点数金额、测量值TEXT字符串UTF-8/UTF-16名称、描述BLOB原始字节流图片、序列化对象这个设计带来的直接后果是同一个字段的不同行存储类可以完全不同。你在CREATE TABLE里写age INT然后往里插abcSQLite 不会拒绝它照单全收。这一点和 MySQL 那种强约束的列类型理念是两条路线理解不了这一层后面全是混乱。我举个例子。我有一个 IoT 项目设备上报表的temperature字段偶尔会上报N/A字符串。在 MySQL 里这是写入失败需要上层处理异常在 SQLite 里它就这样存进去了查询时靠CAST或程序逻辑兜底。初看像是“缺陷”实际是嵌入式场景的灵活性优势——设备端代码不用为了类型错误频繁崩溃。1.2 type affinity声明类型时的“意图提示”虽然存储类不强制SQLite 也不是完全不管。每个字段可以带一个“Type Affinity”类型亲缘性它决定当值试图进入这个字段时SQLite 按什么偏好去转换。偏好顺序大致是INTEGER 亲和性如果声明类型含INT如INT、BIGINT字符串能无损转成整数就转纯数字实数值若小数点后没有小数部分也会转成 INTEGER。TEXT 亲和性含CHAR、CLOB、TEXT等会把 REAL/INTEGER 转成文本形式。REAL 亲和性含REAL、FLOA、DOUB整数会转成浮点数。NUMERIC 亲和性含NUM、DEC比较宽松整数存整数浮点存浮点如果插入时带了引号且能无损转数值也会转。BLOB 亲和性声明类型里啥都不含或者显式写了 BLOB则不做转换原样存储。我踩过一个很典型的坑在CREATE TABLE里写了price DECIMAL(10,2)然后插入12.5字符串形式。按常识这是字符串应该报错但 SQLite 因为 NUMERIC 亲和性把它悄无声息转成了12.5REAL。排查问题的时候看数据一辈子看不出毛病后来用typeof(price)才发现全是 REAL不是预期的 NUMERIC 定点表达。这个typeof()函数是排查类型问题最好的工具下一个章节专门说。2. 字段声明那些“坑”写法和实际的偏差2.1 “不写类型”和 DATETIME 的真相SQLite 允许建表时完全省略字段类型比如CREATE TABLE notes (content, created_at);这样写的字段是 BLOB 亲和性即无亲和性什么值都能进。很多人觉得偷懒但我个人建议还是写清楚类型声明因为后续要跟 ORM、Pandas 对接时没有声明会让驱动层的类型推断变得被动。另外还有一个常年争论SQLite 没有原生的 DATETIME 类型。你写DATETIME它最后落在 NUMERIC 亲和性上。也就是说你插入2024-01-01 12:00:00字符串在 SQLite 里它本质是 TEXT 存储你插入datetime(now)返回的也是 TEXT。真正做时间比较时一定要统一格式ISO8601 的YYYY-MM-DD HH:MM:SS否则同一天的数据会因为2024-1-1和2024-01-01的字符串排序结果不一致而出错。我验证过一组数据SELECT typeof(date(now)), typeof(datetime(now));两个都返回text没有悬念。所以我的习惯是时间字段一律用 UTF 文本加统一格式存储排序用字符串序比较用julianday()转换后再比。实际测试中julianday(2024-01-01 12:00:00)转出来是连续实数语义清晰且没有地域时区概念的影响。2.2 布尔值和日期“无原生类型”的应对方案SQLite 里没有 BOOLEAN你写BOOLEAN会被当作 NUMERIC 亲和性存进去的TRUE/FALSE会被转成1/0。如果你在代码里拿到的值不是1或0而是true字符串那一定是写了 TEXT 类型的列。为了避免这个我一般在建表时写flag INTEGER NOT NULL DEFAULT 0代码里用0/1表达布尔并在读取层统一转换。这样做还有额外好处查询WHERE flag 1的时候走整数索引比文本比较更高效。再说到日期SQLite 官方推荐用 TEXT 存储 ISO8601 格式。我一开始用datetime(now)存后来发现一个隐患datetime(now)依赖系统时区设置容器的时区一变存进去的时间就跟着变。现在我全部改成应用层生成 UTC 时间字符串传入SQLite 只负责存和排序。这套方案在跨国项目中尤其重要否则你按“本地时间”存的数据换个环境看全是偏移的。3. 改字段类型和动态类型实操中必遇的检查与转换3.1 想改列类型ALTER TABLE 的局限性很多从 MySQL 过来的人上来就写ALTER TABLE users MODIFY age BIGINT;SQLite 会直接给你语法错误。它只支持ALTER TABLE ... ADD COLUMN和RENAME COLUMN不支持修改已有列的类型。热词里“sqlite修改字段的类型”搜得很多这确实是新手最常遇到的硬墙。改类型的正路是“表重建大法”我演练过无数次流程总结如下用旧表结构创建新表字段名和目标类型按需调整。拷贝数据转换逻辑写在 SQL 里能转的转不能转的用 CASE 兜底。删旧表把新表改名。重建索引和触发器。CREATE TABLE users_new ( id INTEGER PRIMARY KEY, age INTEGER, name TEXT ); INSERT INTO users_new (id, age, name) SELECT id, CAST(age AS INTEGER), name FROM users; DROP TABLE users; ALTER TABLE users_new RENAME TO users;注意第 2 步如果数据量很大不能在一个事务里完成太久建议先BEGIN插完再COMMIT中间出错直接ROLLBACK。我们测过 90 万行的表这条流程在三秒内完成普通 SSD。什么时候必须走这条路比如统计代码里要求COUNT(DISTINCT id_card)的结果必须按文本匹配而旧表把身份证号建成了 INTEGER导致000123和123混存。这时候不改列类型后续所有查询都要靠CAST兜底索引还会失效改表的收益非常明确。3.2 用 typeof() 和 CAST 做运行时排查排查数据类型问题最直接的工具是typeof()。它能返回每个值的实际存储类SELECT typeof(id), typeof(name), typeof(age) FROM users LIMIT 10;这个方法帮我在生产库里抓出过“藏在整数列里的字符串日期”和“藏在实数列里的整数 ID”。一旦发现混存先用 CAST 统一SELECT CAST(age AS INTEGER) FROM users WHERE CAST(age AS TEXT) abc;CAST 的底层规则要记牢CAST(expr AS INTEGER)会先把文本按“能解析的数字开头”转换解析不了的返回 0。比如CAST(abc AS INTEGER)结果是0CAST(12abc AS INTEGER)结果是12。这是很多隐性 bug 的源头不要凭直觉判断。注意CAST 到 INTEGER 时12abc会被解析为 12不会报错。如果你的文本里混着单位和数字查询结果会默默出错。4. 与编程语言和外部工具的数据类型交互4.1 Python / Pandas 的读取转换Python 的 sqlite3 模块读取数据时默认返回 Python 原生类型INTEGER 映射 intREAL 映射 floatTEXT 映射 strBLOB 映射 bytes。这个映射总体直观但有一个经典问题从 SQLite 读出来的DECIMAL(10,2)到了 Pandas 里可能变成 object 类型尤其是混存了 TEXT 和 INTEGER 时。热词里“pandas 数据类型转换”搜得多就是因为这个。我常用的姿势是DataFrame 读取之后统一做一次pd.to_numericpd.to_datetimeimport sqlite3 import pandas as pd conn sqlite3.connect(app.db) df pd.read_sql_query(SELECT id, price, created_at FROM orders, conn) df[price] pd.to_numeric(df[price], errorscoerce) df[created_at] pd.to_datetime(df[created_at], errorscoerce)errorscoerce很关键转换失败会变NaN而不是抛异常。我们线上出现过一批脏数据因为少了这个参数整个分析任务中断加了之后不仅稳定还能顺便统计isnull().sum()来圈定脏数据范围。4.2 C#、Java、JavaScript 的连接与类型适配在 C# 里用 Microsoft.Data.Sqlite读 SQLite 的 INTEGER 会映射为longREAL 映射为doubleTEXT 映射为string。这里有一个 C# 特性如果你的实体属性是int而 SQLite 返回long会因为未装箱转换报InvalidCastException。应对方式是在映射层强转var age reader.IsDBNull(1) ? 0 : Convert.ToInt32(reader.GetInt64(1));Java 用sqlite-jdbc时SQLite 的 NULL 对应null整型对应int或long而日期默认是字符串需要手动SimpleDateFormat解析。我见过很多人被这个“日期解析”搞到头疼建议还是用 Joda-Time 或者java.time配合解析。JavaScript 侧如果用better-sqlite3默认返回的也是 JS 原生类型但对 BIGINT 默认会丢精度。应对方案是把sqlite3的bigInt模式打开或者查询时CAST(id AS TEXT)再传。实测 900 万行记录时直接用 JS number 处理自增 ID 超过Number.MAX_SAFE_INTEGER完全没问题但如果在主键上用了 UUID 字符串一定确保不要走BLOB存储否则不同的 Buffer 序列化方式会让同一 UUID 查不出来。热词里还提到“rocky linux c# vscode sqlite读写例子”这个场景我在 Linux 上跑过只要安装了libsqlite3-dev和 .NET SDKVisual Studio Code 里配合 C# 扩展读写 SQLite 完全顺畅。唯一容易漏的是连接字符串里要指定ModeReadWriteCreate否则 Linux 下默认只读会让人摸不着头脑。4.3 DB Browser for SQLite 的实用技巧DB Browser for SQLitedb4s是跨平台最顺手的图形化管理工具。它有一个容易被忽略的功能查看字段的实际存储类。在“数据库结构”表里右键“复制 CREATE 语句”可以看声明类型在“浏览数据”标签页里单元格会显示类型图标区分整数、文本、实数、二进制。这个细节对排查混存非常有用。还有一个实用技巧db4s 里可以直接执行typeof()查询并且支持可视化查看结果比命令行直观得多。我自己维护生产库时通常先用 db4s 打开写一段SELECT typeof(...)探查数据再决定是否需要表重建。省了在终端反复敲命令的时间。5. 十万条数据到底要多久性能与类型的关联5.1 查询时长的真实测试热词里“十万条数据 sqlite查询需要多久”是一个高质量的实操问题。我直接给数据普通机械硬盘下单表单索引十万行SELECT *带WHERE条件SQLite 通常在 30~80 ms 内返回如果查询条件命中了索引10 万行SELECT COUNT(*) WHERE indexed_col ?大约在 10 ms 内。SSD 上还能再快近一倍。我拿自己笔记本NVMe SSD i7实测过 10 万行数据全表扫描SELECT *耗时 25 ms。不过这个数字有一个前提别把类型搞乱。如果你在 WHERE 子句里写了WHERE age 18而 age 列是 INTEGER 且建了索引SQLite 会在执行阶段做隐式 CAST索引失效直接全表扫描速度掉一个数量级。我实测的一个 90 万行日志表本应 20 ms 的查询因为一个字符串和整数的错配跑到了 800 ms整整 40 倍差距。5.2 索引与类型匹配的最佳实践索引跟类型是强绑定的混存类型会让索引的作用大打折扣。最佳实践是建表时明确统一类型查询时参数类型尽量和存储类一致。如果一个字段存储的全是 INTEGER查询参数就不要传字符串如果万不得已要传也先在 SQL 里CAST(? AS INTEGER)。另外复合索引要注意前缀原则。SQLite 的复合索引用最左前缀匹配类型不匹配会让优化器放弃索引。比如CREATE INDEX idx_user_created ON users(created_at)然后查询写WHERE created_at 2024-01-01 10:00:00如果列里存的是 REALjulianday形式就完全走不上索引。我踩过的另一个坑是把索引建在低基数列上比如只有 true/false 的字段SQLite 优化器会直接忽略不走索引。后来改成“布尔 时间”复合索引查询效率才有明显提升。这个经验分享给做设备状态表的朋友别被“索引万能论”带偏。6. 常见问题速查与实战排查清单实际操作中我把最常出问题的场景整理成一张表每次排查优先按表里逻辑走现象可能原因排查方法解决方案日期比较结果不对列内 TEXT 和 INTEGER 混存SELECT typeof(created_at) FROM t LIMIT 5统一 ISO8601 文本格式或全转julianday实数写入自增字段报错主键类型不符传了字符串检查 INSERT 参数绑定类型代码层强转int/long或在 SQL 里CASTALTER TABLE 语法错误使用了 MySQL 的MODIFY COLUMN确认 SQLite 版本支持范围改用表重建流程索引失效、查询变慢WHERE 参数和列存储类不匹配EXPLAIN QUERY PLAN SELECT ...参数类型与存储类一致或显式 CASTPandas 读到 object 列列内混存整数、文本df.dtypes查看pd.to_numeric(errorscoerce)C# 报 InvalidCastExceptionSQLite long 映射到 C# int断点看 reader 返回类型Convert.ToInt32(reader.GetInt64(...))查不到 UUIDBLOB 序列化方式不一致typeof(uuid)查存储结构改成 TEXT 存储 UUID排查时还有一个管用的命令EXPLAIN QUERY PLAN。它会把查询计划打印出来你一眼就能看到SEARCH走索引还是SCAN全表扫描。全表扫描不是一定不好但如果你的表到了几十万行、每次查询都 SCAN就该回去检查类型匹配了。提示生产环境改表前一定先备份sqlite3 app.db .backup backup.db一行搞定别嫌土它比导出 SQL 再导入更快更稳。我个人在实践中的一个体会是SQLite 的类型系统是“信任程序员”的设计。它不像 MySQL 那样替你挡掉错误而是把灵活性完全交给你代价是稍不注意就积累脏数据。反过来只要你在建表层把存储类统一定义清楚、在查询层做好参数绑定SQLite 的稳定性和速度表现会超出你的预期。尤其是嵌入式设备、桌面工具、小规模 Web 服务的场景它能省掉的数据库运维成本非常可观。