SQLite 的数据类型光看官方文档那几页你会觉得简单到没什么可讲无非就是 NULL、INTEGER、REAL、TEXT、BLOB 五种存储类。但真到了实际项目里你大概率会碰上“我明明存的整数查出来怎么是文本”“为什么 LIKE 查询十万行要两秒”“ALTER TABLE 改不了字段类型”之类的糟心事。这篇就是想把这些坑一次性讲透顺便把类型亲缘性、跨语言读取、性能优化和修改字段类型这些经常被一笔带过的细节也补全。这篇文章适合三类人准备在项目里落地 SQLite、但还没搞清楚存储类设计的后端开发者经常用 pandas 或 Python 读 SQLite 做数据分析、被 dtype 折磨过的数据人以及从 MySQL 迁过来、习惯强类型约束、遇到 SQLite 过于“佛系”而不知所措的朋友。看完之后你至少能回答三个问题SQLite 为什么允许往 INTEGER 列里塞文本十万条数据查询到底该不该建索引字段类型写错了怎么低成本改掉1. 上手之前先把 SQLite 的定位和工具搞清楚1.1 SQLite 到底是个什么东西SQLite 不是传统意义上的“数据库服务器”它是一个嵌入式关系型数据库引擎。没有独立的守护进程没有端口没有连接配置你的“数据库”就是一个普通的磁盘文件。程序通过 SQLite 的 C 库直接读写这个文件整个引擎只有几百 KB。这种设计让它特别适合移动端应用、桌面软件本地存储、嵌入式设备以及开发调试阶段快速起一个临时数据库。但正因为轻量很多人会低估它的复杂度。SQLite 完整实现了 SQL 标准的大部分功能支持事务ACID、索引、视图、触发器、窗口函数甚至 JSON 函数。它不是玩具但在数据类型这件事上它的设计哲学和 MySQL、PostgreSQL 这些主流关系库有明显差异。SQLite 的核心思路是“动态类型 类型亲缘性”也就是字段声明了类型但存进去的值不一定会严格遵循这个类型。这一点是理解整个 SQLite 数据类型体系的关键也是日常踩坑的主要来源。1.2 强烈建议先用这个工具观察类型在深入原理之前我建议你先装一个 DB Browser for SQLite简称 DB4S。这是目前最常用的开源跨平台 SQLite 管理工具Windows、macOS、Linux 都有安装包免注册打开 .db 或 .sqlite 文件就能直接浏览表结构、执行 SQL、导出 CSV。为什么特别强调用工具因为 SQLite 的“类型”分两个层面建表时写的“声明类型”和实际存储时的“存储类”。这两个概念不一致光看建表语句根本发现不了问题。DB4S 的“数据库结构”页签能列出每列声明类型和亲缘性“浏览数据”页签则能直观看到每一个单元格实际存的是什么形态的值。比如同是 TEXT 列你可能在里面看到数字 123存储为文本和“abc”存储为文本光靠肉眼就能分辨出来。用顺手之后再回来看后面的内容你会更有感觉。2. 五大存储类逐个拆解SQLite 类型体系的地基2.1 存储类与数据类型的区别首先要建立一个概念SQLite 内部只有五种“存储类”Storage Class而不是像 MySQL 那样有几十种“数据类型”。这五个存储类分别是 NULL、INTEGER、REAL、TEXT、BLOB它们代表的是值在磁盘上真正保存的形态。NULL表示空值占用 0 字节没有其他信息。INTEGER有符号整数根据数值大小可选用 1、2、3、4、6 或 8 字节存储。注意没有固定长度SQLite 会挑最省空间的方式存。REALIEEE 754 双精度浮点数固定 8 字节。TEXT文本字符串默认使用 UTF-8 编码也支持 UTF-16按内容长度存。BLOB二进制大对象原样保存输入字节不做任何编码转换和校验。这里最值得记的是 INTEGER 的变长存储。比如 100 这个值可能只占了 1 字节但 30000 就要占 2 字节超过 32767 可能需要 4 字节或更多。这个机制让 SQLite 在存储大量小整数时比固定长度类型的数据库更省空间也是它底层文件可以做到很小巧的原因之一。2.2 没有布尔类型没有日期类型很多新手最不适应的就是这两个缺失。SQLite 没有独立的 BOOLEAN 存储类按官方推荐布尔值用 INTEGER 存0 表示假1 表示真。虽然 3.23 版本之后 SQLite 引入了 TRUE 和 FALSE 关键字但它们本质上只是 INTEGER 1 和 0 的语法糖存入数据库后仍然是整数。日期时间也没有专属类型官方给出的方案是三选一用 TEXT 存 ISO8601 格式的字符串比如“2024-05-01 12:30:00”用 INTEGER 存 Unix 时间戳秒或毫秒或者用 REAL 存儒略日Julian Day。哪一种都不算错但一旦混着用就会出问题比如同一个表里有的行存的是文本日期有的行存的是时间戳排序和区间查询会直接翻车。后面我会专门讲这个坑。2.3 为什么 SQLite 要把类型设计得这么“懒”SQLite 采用动态类型很大程度上是为了保持灵活性和兼容性。它的定位是嵌入式数据库使用场景千差万别有的语言里数字就是数字有的语言里数字可能是字符串有的应用存 JSON 字段结构不固定。如果像 MySQL 那样对类型做强校验很多场景反而会被卡死。所以 SQLite 的默认策略是声明类型主要作“倾向建议”不强制拒绝。你把“abc”写进 INTEGER 列它不会报错照存不误。这种设计非常宽容配合极低的上手成本适合做原型、做本地缓存、做嵌入式存储。但宽容的另一面就是数据质量全靠自觉这是后面所有坑的根源。存储类内部占用常见来源对应的“数据类型”NULL0字节空值NULLINTEGER1~8字节变长整数、布尔值INT, INTEGER, BOOLEAN, BIGINT 等REAL8字节浮点数REAL, DOUBLE, FLOAT 等TEXT按字符数扩展字符串、日期文本TEXT, VARCHAR, CHAR, DATE, DATETIME 等BLOB原始字节图片、序列化对象BLOB, 无类型列3. 类型亲缘性为什么你写了 INTEGER 列存进去的却是文本3.1 亲缘性的官方规则你以为建表时写了score INTEGER那这一列就一定是整数不一定。SQLite 会先根据你声明的类型给每一列算一个“亲缘性”Type Affinity然后只在“无损转换”的情况下尝试把插入的值转换成对应形态。如果转换不了就保留原样存进任意存储类。官方亲缘性推导规则有五条按优先级排列声明类型里包含INT该列亲缘性为 INTEGER。INTEGER、BIGINT、SMALLINT都属于这一类。声明类型里包含CHAR、CLOB或TEXT亲缘性为 TEXT。VARCHAR(255)、CHAR(10)、TEXT都算。声明类型里包含BLOB或者不写任何类型亲缘性为 BLOB也就是无亲缘不做转换。声明类型里包含REAL、FLOA或DOUB亲缘性为 REAL。FLOAT、DOUBLE都命中。以上都不满足亲缘性为 NUMERIC。这就是为什么NUMERIC(10,2)、DECIMAL(10,2)、BOOLEAN、DATE、DATETIME这些类型最终都被归到 NUMERIC 亲缘性下。注意规则的优先级包含INT的类型会优先被归为 INTEGER 亲缘即便它同时也包含其他关键字。比如INTEGER没有歧义但VARCHAR因为包含CHAR归 TEXTBIGINT因为包含INT归 INTEGER。3.2 亲缘性到底转换哪些值有了亲缘性SQLite 在插入数据时会有下面这些行为TEXT 亲缘列如果插入的是数字 123SQLite 会尝试把它转成文本“123”再存。反过来插入文本时保持原样。INTEGER 亲缘列插入文本“abc”时因为无法无损转换原样存 TEXT插入文本“123”时无损则转成 INTEGER 123 存储插入浮点数 1.5如果无损转换成整数则转否则存为 REAL。这一点很容易记混建议实际执行一下SELECT typeof(col)观察。REAL 亲缘列插入带有小数部分的数字直接存 REAL整数如果无损可以转成实数也会转成 REAL 存储。NUMERIC 亲缘列规则更宽松文本如果看起来像整数或实数会尝试转成 INTEGER 或 REAL。这就是为什么DATE类型列里塞整数日期会变成数字。另外 NUMERIC 亲缘列还有一个特性0 会被转成整数 0而不是文本“0”。BLOB 或无类型列完全不做转换插入什么存储类就是什么存储类。3.3 用 typeof 函数验证亲缘性与其死记规则不如直接在 SQLite 命令行或 DB4S 里跑一条验证语句CREATE TABLE t ( c_int INTEGER, c_text TEXT, c_decimal DECIMAL(10,2), c_none ); INSERT INTO t VALUES (1.5, 123, 45.6, hello); SELECT c_int, typeof(c_int), c_text, typeof(c_text), c_decimal, typeof(c_decimal), c_none, typeof(c_none) FROM t;我实测过的结果是c_int存储为 REAL 1.5因为没法无损变成整数c_text存储为 TEXT数字文本“123”原样保留c_decimal存储为 REAL 45.6因为 NUMERIC 亲缘会做转换c_none没有任何类型保留为 TEXT“hello”。执行完这组语句你对亲缘性的理解会比看十篇文章都管用。4. 和 MySQL、Pandas、Redis 对比SQLite 类型凭什么这么“松”4.1 与 MySQL 对比强约束与弱约束的天壤之别MySQL 对类型有强约束。INT列插入“abc”在严格模式下直接报错插入超出范围的整数可能截断或告警。这种设计能保证数据一致性避免脏数据混入表里。代价是 schema 必须提前设计好迁移成本相对高。SQLite 反过来把“约束”的职责交给了应用层。它的默认行为是尽量容纳而不是拒绝。从 MySQL 迁过来的团队最容易出问题原来靠数据库类型保证的数据质量迁到 SQLite 后全部失效。比如DATETIME列在 MySQL 里只能存合法日期在 SQLite 里却能存“not-a-date”这样的文本而且不会报错。所以迁移后必须补一层应用校验或者在建表后用 STRICT 表我后面会讲强制类型。4.2 与 Pandas 对比dtype 推断和 SQLite 存储类的关系Pandas 的 dtype 体系object、int64、float64、datetime64 等和 SQLite 存储类不是一回事但经常需要相互转换。用pd.read_sql_query()读 SQLite 时pandas 会根据实际读到的值来推断 dtype全部是 INTEGER 存储类的列通常读成 int64。混合了文本和数字的列会被读成 object数字不再参与数值计算。TEXT 存储类里如果恰好全是“看起来像日期的字符串”pandas 有时会自动推断为 datetime64但这取决于版本和参数设置。这里有个经典坑同一个 VARCHAR 列因为少数行存了非数字文本整个列读出来就是 object导致df[price].mean()报错。我的习惯是在读完之后显式做一次pd.to_numeric(..., errorscoerce)不要依赖默认推断。反过来如果要把 DataFrame 写回 SQLite默认的to_sql()会根据 DataFrame 的 dtype 自动推断 SQLite 类型但 object 类型的列往往会变成 TEXT原来的整数形态就丢了。4.3 与 Redis 对比存储结构和类型语义完全不同Redis 的“类型”指 string、list、hash、set、zset 这些数据结构类型强调的是数据结构层面的差异每个 key 都带类型标识你用错命令操作某种类型会直接报错。SQLite 的存储类则是单个字段值的底层形态两者维度完全不一样但都叫“数据类型”经常被新手拿来做对比。如果用一句话区分Redis 的类型决定“这个值能做什么操作”SQLite 的存储类决定“这个值在磁盘上长什么样”。Redis 的 string 类型不管存的是“123”还是“hello”本质上都是字节串SQLite 的 INTEGER 存储类则明确表示这是一个整数可以被数学函数直接处理。理解这个区别你就不会在做缓存层设计时把两者的类型规则混为一谈。5. 跨语言实操Python、Pandas、JavaScript、C、Java 怎么读对类型5.1 Python 内置 sqlite3 模块Python 的 sqlite3 模块是官方自带的读取 SQLite 时尽量使用sqlite3.Row作为行工厂这样既支持字段名访问又能方便地看到字段对应的 Python 原生类型import sqlite3 conn sqlite3.connect(test.db) conn.row_factory sqlite3.Row cur conn.execute(SELECT id, name, score FROM user LIMIT 5) for row in cur: print(row[id], type(row[id]), row[name], type(row[name]))默认映射规则是NULL 变 NoneINTEGER 变 intREAL 变 floatTEXT 变 strBLOB 变 bytes。这个映射非常符合直觉但要注意如果你的列存的是文本“123”这里读出来就是 str “123”不是 int 123。用 DB4S 看着明明是数字Python 读出来却不是数字这类问题十有八九是插入时它就被存成了 TEXT而不是读取端的问题。5.2 pandas 读取时的 dtype 陷阱用 pandas 读取 SQLite 是数据分析场景的高频操作import pandas as pd import sqlite3 conn sqlite3.connect(test.db) df pd.read_sql_query(SELECT * FROM order_detail, conn) print(df.dtypes)我遇到过最典型的问题订单金额字段在 SQLite 表里声明为 DECIMAL(10,2)但因为亲缘性规则实际存成 REAL 或 INTEGER 都正常可一旦某几行因为历史原因被写成了文本“100.5”整个列 pandas 就会推断成 object。处理方式也不复杂读取后统一转换df[amount] pd.to_numeric(df[amount], errorscoerce)另外to_sql()写回时如果 DataFrame 某列是datetime64pandas 默认会映射成 DATETIMESQLite 按亲缘性归为 NUMERIC最终存成文本还是数字取决于具体驱动版本。建议写回前先自己把 datetime 列统一成 ISO 格式字符串或时间戳整数避免两边推断不一致。5.3 JavaScript 和 better-sqlite3 的 typeof 检查Node 生态下我用得比较多的是 better-sqlite3它默认会做类型转换NULL 变 nullINTEGER/REAL 变 numberTEXT 变 stringBLOB 变 Bufferconst Database require(better-sqlite3); const db new Database(test.db); const row db.prepare(SELECT * FROM user WHERE id ?).get(1); console.log(typeof row.id, typeof row.name);如果发现某个数字列返回的是字符串别怀疑是 better-sqlite3 的问题先用SELECT typeof(列名)看原始存储类。因为 SQLite 返回时底层 API 是根据实际存储类给出数据的驱动只是把底层不同类型映射到 JavaScript 原生类型而已。5.4 C 接口里的 sqlite3_column_type 是唯一权威所有语言驱动都只是对 C 接口的封装。C 接口判断类型时不要依赖建表时声明的类型而应该直接查列的存储类int column_type sqlite3_column_type(stmt, 0); switch (column_type) { case SQLITE_INTEGER: /* sqlite3_column_int */ break; case SQLITE_FLOAT: /* sqlite3_column_double */ break; case SQLITE_TEXT: /* sqlite3_column_text */ break; case SQLITE_BLOB: /* sqlite3_column_blob */ break; case SQLITE_NULL: /* 空值 */ break; }这段代码几乎是跨语言读写 SQLite 的通用准则判断类型请认准运行时存储类而不是建表 schema。Java 里用ResultSet.getObject()也能拿到最贴近存储类的对象只是要留意 SQLite JDBC 对 NULL 列调用getInt()会返回 0 而不是 null容易出现隐患。6. 十万条数据查询实测类型选不对慢到怀疑人生6.1 全表扫描不一定慢但别拿它当默认网上很多人问“十万条数据SQLite 查询需要多久”这个问题其实没标准答案。我本地实测过一次单表 10 万行无索引按主键id 99999精确查询耗时在 2 毫秒左右但执行SELECT * FROM user WHERE remark LIKE %重要%因为要全表扫描每一行的文本耗时直接到 80 毫秒以上。如果表里有几十列、每列都有大文本慢的就不只是查询连统计 COUNT 都会让人着急。十万行这个量级SQLite 完全扛得住但前提是别做无索引的模糊查询。建索引不仅能加速等值查询也能加速前缀 LIKE。你的字段类型必须统一如果是 TEXT 存储的数字索引排序会按字典序来10 会排在 9 前面范围查询的结果会让你怀疑人生。6.2 用 EXPLAIN QUERY PLAN 检查是否走索引给高频查询的列建索引CREATE INDEX idx_user_name ON user(name); EXPLAIN QUERY PLAN SELECT * FROM user WHERE name LIKE 张%;如果结果里出现SCAN user说明是全表扫描出现SEARCH user USING INDEX idx_user_name说明走索引了。这里有个 SQLite 的细节LIKE 张%可以走索引LIKE %张不行因为后缀匹配无法利用 B-Tree 索引的有序性。另外索引也不宜过多。每建一个索引写入时都要同步维护一棵独立的 B-Tree。十万行数据你建三五个索引还能接受但如果超过十个写入性能会明显下降。索引的列顺序也有讲究比如优先把选择性高的列放在联合索引前面能显著减少回表行数。6.3 类型不一致导致的索引失效最隐蔽的性能问题是“声明了 INTEGER其实存了一堆文本”。SQLite 的索引会记录实际存储类的信息当同一列既有 INTEGER 1、2 又有 TEXT“1”“2”时查询条件WHERE status 1只会命中 INTEGER 存储的那部分数据TEXT“1”的那部分经常被漏掉。另一个常见场景是日期列混存了文本日期和时间戳整数区间查询BETWEEN 2024-01-01 AND 2024-12-31时时间戳整数完全不会匹配。所以我的建议很直接建表时把每个字段的存储类规划清楚应用层写入时做强制转换不要指望 SQLite 替你做。宁可写入时多写一个转换函数也不要让查询时去猜这一列到底混了什么。7. 想改字段类型怎么办SQLite 的 ALTER TABLE 到底能做什么7.1 SQLite 原生 ALTER TABLE 的边界很多数据库支持ALTER TABLE ... MODIFY COLUMN直接改字段类型SQLite 不行。它的 ALTER TABLE 只支持三件事RENAME TO重命名表、RENAME COLUMN重命名列、ADD COLUMN增加列新版也支持DROP COLUMN但都不能直接修改已存在列的类型。这是因为 SQLite 的存储引擎直接操作磁盘页文件列类型基本只存在于 schemasqlite_schema 表里的 CREATE TABLE 语句中数据本身已经被存储类固定住了。强行改类型要么重写所有数据页要么会产生类型声明与实际数据不一致的中间状态SQLite 选择不做这件事把重写数据的操作留给用户自己掌控。7.2 标准迁移套路建新表 复制数据 改名实际项目中要修改字段类型比如把score从 TEXT 改成 REAL标准做法是重建表PRAGMA foreign_keysOFF; BEGIN; CREATE TABLE user_new ( id INTEGER PRIMARY KEY, name TEXT, score REAL ); INSERT INTO user_new(id, name, score) SELECT id, name, CAST(score AS REAL) FROM user; DROP TABLE user; ALTER TABLE user_new RENAME TO user; COMMIT; PRAGMA foreign_keysON;这段流程里最容易忽略的是索引、触发器和视图。直接 DROP 原表会把关联的索引全丢掉触发器也会失效视图引用旧表名也会崩。正确做法是先把原表的索引定义查出来重建新表后逐个补建触发器可以先临时候禁用迁移完再按原定义挂回去。如果表是自增主键还要注意 sqlite_sequence 里的序列值避免新表的主键重新从 1 开始导致业务层主键冲突。7.3 使用 CAST 转换时要注意的精度问题上面示例里的CAST(score AS REAL)能把文本“88.5”转成浮点数但“abc”这种文本会直接变成 0。转换结果是否符合预期建议先跑一条只读 SELECT 看一眼SELECT score, CAST(score AS REAL) FROM user WHERE CAST(score AS REAL) 0;把所有转换后为 0、但原值不是 0 的行筛出来人工确认这些是不是脏数据。这个步骤看起来繁琐恰恰能避免迁移后隐藏的数据污染。实际项目中我习惯先在测试库上完整跑一遍迁移脚本再比对迁移前后总行数、关键字段求和值确认一致后再在生产环境执行。8. 类型相关常见问题速查这 8 个坑我基本都踩过8.1 快查表症状与对策症状根因排查与解决办法整数列查出字符串插入时被存为 TEXT或应用层驱动转换不同SELECT typeof(col)查实际存储类修正写入逻辑布尔查询WHERE flag1查不到flag 列存了文本“1”或“0”重建数据或统一用 INTEGER 0/1 写入日期排序不对TEXT 日期和 INTEGER 时间戳混存统一存一种格式建议全线使用 ISO8601 文本拼接字段变成文本拼接数字列实际存储类是 TEXT对列执行 CAST 或修复底层存储类pandas 读出 object 后无法计算SQLite 同一列混了多种存储类pd.to_numeric(..., errorscoerce)强转LIKE %关键词%特别慢后缀模糊查询不走索引改前缀 LIKE、加全文搜索功能或减少扫描行中文乱码写入端编码和 SQLite 默认 UTF-8 不一致连接层强制指定 utf-8导出导入统一编码ALTER TABLE 改类型报错SQLite 原生不支持修改列类型按 7.2 节重建表迁移8.2 两个容易忽略的写入细节第一个是“无类型列”。建表时如果漏写了字段类型比如CREATE TABLE t (a, b)这两个列没有类型定义亲缘性为 BLOB也就是不做任何转换。很多新手觉得“没写类型就能随便存”没错但带来的副作用是索引无法按数值比较统计函数 SUM、AVG 也会遇到文本内容而直接报错或忽略尽量别这么设计。第二个是 STRICT 表。SQLite 从 3.37.0 开始支持严格表CREATE TABLE user ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER ) STRICT;STRICT 表里只允许写 INTEGER、REAL、TEXT、BLOB 和 ANY 这几种类型而且会严格执行存储类不允许“往 INTEGER 列塞文本”这种操作。如果你实在受够了 SQLite 的松散团队又能保证运行在 3.37 以上版本STRICT 表是一个不错的强约束方案。但要注意老版本打不开这种库迁移和备份工具也可能不兼容生产环境使用前一定要确认依赖链。最后分享一段个人经验从我这些年接触的项目来看SQLite 的数据类型问题几乎从来不是“它不能做什么”而是“使用者没想清楚就让它随便存什么”。给你的建议很直白建表时把每个字段写成明确的INTEGER、REAL、TEXT、BLOB或ANY不要写VARCHAR(255)、DATETIME、DECIMAL(10,2)这种看着熟悉、实际上会绕一圈亲缘性的类型。写入数据前在应用层做一次类型校验或转换哪怕只是简单的int()、str()都能省掉后面大量排查的时间。这些小习惯坚持下来SQLite 完全可以像正规军一样给你提供稳定可靠的数据存储而不是一个随时可能埋雷的本地文件。