SQLite3 打败了 PostgreSQL ——世界最大装机量数据库，低调但不小众

接下来咱们继续学习SQLite，我们对一些简单的SQLite的命令进行简单的学习。

SQLite 作为轻量级的数据库，其数据库类型体系与传统的数据类型差异是很签注的额，从数据库类型上看，他的主要核心是围绕动态类型，和兼容类型他的数据类型并不精准，而是围绕灵活性而来。

SQLite的第一个区别就是动态类型，比如我们PostgreSQL中INT，我们只能存储数据类型，而SQLite不是的，即使你声明的表类型是INT，他也可以存储字符串类型，浮点等，列定义仅仅作为参考，不做强制。

后面在SQLite中又有strict表，这类表才是严格和传统数据库类型强制一致的表，如果有此类需求需要建表的时候建立strict表。

SQlite的数据类型主要有五种，NULL, INTEGER ,REAL, TEXT, BLOB 等，这里SQLite里面是没有时间类型的，时间类型需要转化，通过date, strftime函数来进行。

日期时间类型：无独立存储类，需通过以下 3 种格式存储，再用内置日期函数（如 date()、strftime()）转换：

TEXT：ISO8601 格式（如 2024-11-14 12:00:00）； REAL：儒略日（自公元前 4714 年 11 月 24 日格林威治正午起的天数）； INTEGER：Unix 时间戳（自 1970-01-01 00:00:00 UTC 起的秒数）

这里需要注意如果建立不同表，那么你输入的类型如果是5.0 会自动转化为 5整型，而不是浮点类型 real。

可能是随着SQLite使用的越来越多，作为移动端的数据越来越重要，那种数据库把你的数据类型转错的情况就发生了，所以后续的SQLite有新的一个表类型strict，建立表需要建表的末尾添加strict 也就是严格模式。

列类型强制要求

所有列必须显式指定类型，不能省略； 仅允许 6 种类型：INT、INTEGER、REAL（浮点）、TEXT（字符串）、BLOB（二进制）、ANY（任意类型），无其他可选类型（未来可能新增）。

（2）数据插入规则

非ANY类型列：插入数据需是NULL（无NOT NULL约束时）或匹配列类型；SQLite 会按 “类型亲和性规则” 尝试转换（与其他数据库一致），无法无损转换则抛出SQLITE_CONSTRAINT_DATATYPE错误（例：INTEGER列插入'xyz'会报错）。 ANY类型列：可接受任意类型数据（NOT NULL约束时拒绝NULL），不做任何类型转换，完全保留原始数据类型与值。

（3）主键与完整性检查

主键列隐式包含NOT NULL约束；但INTEGER PRIMARY KEY列插入NULL时，会自动转为唯一整数（与非严格表规则一致）。 PRAGMA integrity_check/quick_check命令会检查 STRICT 表的列类型，异常时提示错误。

（4）与非严格表的共性 除上述差异外，STRICT 表的其他特性与普通表完全一致，包括：CHECK/NOT NULL/FOREIGN KEY/UNIQUE约束、DEFAULT/COLLATE子句、生成列、ON CONFLICT处理、索引、AUTOINCREMENT、INTEGER PRIMARY KEY作为rowid别名、磁盘存储格式等。

1. ANY 数据类型：特殊规则 设计目的：在严格模式下，仍支持 “单列存储任意类型数据” 的灵活能力（SQLite 独有特性）。

STRICT 表 vs 非严格表的差异： STRICT 表的ANY列：完全保留原始数据（例：插入'000123'，存储为text类型的'000123'）； 非严格表的ANY列：会尝试将 “类数字字符串” 转为数值（例：插入'000123'，存储为integer类型的123）。

1. 向后兼容性

（1）版本限制 仅 SQLite 3.37.0 及以上版本能识别STRICT关键字；低版本打开含STRICT表的数据库时，默认报错（除特殊情况外）。 不含STRICT表的数据库，3.37.0 及以上版本创建后，仍可被低至 3.0.0（2004 年）的版本读写。

（2）低版本访问 STRICT 表的特殊方式 低版本可通过打开数据库后立即执行PRAGMA writable_schema=ON（关闭 schema 解析错误），忽略STRICT关键字并读写表，但不会触发严格类型检查（可能导致数据类型错误，需用高版本PRAGMA quick_check检测）。 SQLite CLI 的.dump命令默认启用writable_schema=ON，低版本用.dump可读写 STRICT 表，但同样存在数据损坏风险。

1. 其他表选项 CREATE TABLE语句末尾（闭合括号后）可接逗号分隔的表选项，目前仅支持 2 种： STRICT（严格类型）、WITHOUT ROWID（无rowid表）； 选项顺序不限，当前版本允许重复选项（未来可能取消，不建议依赖）。

所以针对业务，希望开发人员在3.37版本后的SQLite应该这对业务中的一些精确的数字，或者数字就是文字的部分应该严格，建表就要建立一个严格的表。

CREATE TABLE orders (order_id  INTEGER PRIMARY KEY,order_no TEXT NOT NULL UNIQUE,customer_id  INTEGER NOT NULL,amount REAL NOT NULL,pay_type  TEXT CHECK(pay_type IN ('CARD','CASH','WALLET')),created_at TEXT NOT NULL,raw_payload  ANY,note TEXT,FOREIGN KEY (customer_id) REFERENCES customers(id)) STRICT;

SQLite 并不需要启动，你可以认为他是一个程序，通过SQLite 数据库文件名，就可以进入到数据库进行操作。

查看系统的版本 sqlite> .version SQLite 3.47.1 2024-11-25 12:07:48 b95d11e958643b969c47a8e5857f3793b9e69700b8f1469371386369a26e577e zlib version 1.2.11 gcc-8.5.0 20210514 (Red Hat 8.5.0-28) (64-bit) sqlite>

对Sqlite的日常命令不熟悉，可以通过.help进行更详细的命令的问询

对sqlite中的有多少表进行询问

查看有多少表使用 .table .schema 表名 查看表结构

sqlite> 
sqlite> .tables
id         test_data
sqlite> .schema test_data
CREATE TABLE test_data (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT,
 value INTEGER,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
sqlite> 

sqlite> select * from sqlite_master;
table|id|id|2|CREATE TABLE id (id int)
table|sqlite_sequence|sqlite_sequence|4|CREATE TABLE sqlite_sequence(name,seq)
table|test_data|test_data|3|CREATE TABLE test_data (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  value INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
Run Time: real 0.000 user 0.000020 sys 0.000039

遇到此问题，目前最快速的方案是，退出SQLite然后在进入进行命令的操作。

总结: SQLite在我使用中，经常有database lock的错误产生，（因为我还是用传统数据库思维去操作的问题），解决也很简单，查杀链接，或把.quit数据库即可，解决库锁的问题。在操作中命令不支持上下箭头来进行历史命令的查找，建表要注意宽松模式和严苛模式，其实我逐步对SQLite的深入，我其实对他有很多的问题。

1 SQLLite数据支持数据压缩吗，因为我现在一个表，几百万的数据就270MB的文件大小了，说明这个数据库本身数据的文件的控制有问题，应该有可以进行碎片整理的命令，这个我看到了，但我还没有深入。

2 在研发中，使用SQLite3 如何进行开发和库表的使用，用传统数据库使用思路是一定不行的，还需要打破原有的设计思路来避免库锁的问题。

3 因为我曾经有一次服务器重启，然后进入SQLite特别慢的经历，说明断电对第一次SQLite启动是有阻碍的，应该是wal模式下数据在重做导致的，但是一个关键的问题，SQLite 没有日志，没有错误的报告等等，这些还需要研究。