SQLite
- 数据类型
- 存储类
- NULL
- INTEGER - 0, 1, 2, 3, 4, 6, 8 bytes - 内存中都为 int64
- REAL - 8 byte
- TEXT - UTF-8, UTF-16BE, UTF-16LE
- BLOB
- BOOLEAN -> 0,1
- DATETIME -> TEXT ISO8601, REAL, INTEGER Unix Timestamp
- 类型亲和 - 类型之间会进行内部转换
- INT,INTEGER,TINYINT,SMALLINT,MEDIUMINT,BIGINT,UNSIGNED BIG INT, INT2, INT8 -> INTEGER
- 其他类似
- 存储类
- STRICT Tables
CREATE TABLE t1(a ANY) STRICT;
PRAGMA strict=ON;
- 强制类型 - INT, INTEGER, REAL, TEXT, BLOB, ANY
- ANY 和旧的类型类似
- PRAGMA integrity_check, quick_check 可检测类型
- 不会提升性能 - 运行时检测,对存储无影响,核心性能在于 IO
- ROWID - int64
- rowid, oid,
_rowid_
- 建表时可 WITHOUT ROWID
- 如果表使用 INT 作为 pkey,则该列会被用于 rowid
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
- btree 的 key
- 通过 rowid 搜索和排序是普通 PRIMARY KEY 的两倍速度
- rowid, oid,
- 语法
- 限制
- 不可以 ALTER COLUMN - 只能 rename table/column 形式去变更
- 不可以移除 UNIQUE - 推荐使用 UNIQUE INDEX - 可以被 drop
- altertable
- ALTER TABLE DROP COLUMN 不支持被引用的列
- PRIMARY KEY, UNIQUE, 索引列, 包含在部分索引, 包含在 CHECK, 外键, 被用于生成列, 用于 trigger 和 view
- ALTER TABLE DROP COLUMN 不支持被引用的列
- Limits In SQLite
- SQLITE_MAX_LENGTH=1,000,000,000 - 1G - text,blob 最大长度
- SQLITE_MAX_COLUMN=2000 - 列
- SQLITE_MAX_SQL_LENGTH=1G - sql 最大长度 - 使用 prepare 避免长 sql
- JOIN 最多 64 个表
- SQLITE_MAX_EXPR_DEPTH=1000
- SQLITE_MAX_FUNCTION_ARG=127
- SQLITE_MAX_COMPOUND_SELECT=500
- SQLITE_MAX_LIKE_PATTERN_LENGTH=50000
- SQLITE_MAX_VARIABLE_NUMBER=999 - v3.32.0 32766
- SQLITE_MAX_TRIGGER_DEPTH=1000
- SQLITE_MAX_ATTACHED=10
- SQLITE_MAX_PAGE_COUNT=1073741823 - 1Gi - PRAGMA max_page_count
- 281TB 库
- 2^64 行
- c_limit_attached
- 不可以 ALTER COLUMN - 只能 rename table/column 形式去变更
- 参考
caution
- SQLite 的类型是动态的 - 不强制 - 新版本支持严格类型
- SQLite 的核心限制是并发写入性能 - 如果需要并发写入,不建议用 SQLite
- DATETIME 不会存储毫秒
- 没有 Base64 函数
- BLOB 作为二进制存储部分语言的 Driver 支持不太好
- 不支持 INSERT DEFAULT
- column DEFAULT 表达式需要写为
(expr)
tip
- 默认最多 999 个变量
- WAL 模式可提升 单写多读 性能 -
PRAGMA journal_mode=WAL
- 可以配合 zfs 进行压缩 - 注意使用相同的 page size
- page_size 一般 4096, cache_size 一般 -2000 即 2000 kb
PRAGMA schema.page_size
,PRAGMA schema.cache_size
- compressed sqlite3 database file?
- vacuum 是先写入临时文件,然后替换 - 需要很大空间
# macOS 安装
brew install sqlite3
# 因为系统自带 - 所以默认不会添加到 PATH
$(brew --prefix sqlite3)/bin/sqlite3
# 添加安装的 sqlite3 到 PATH
export PATH="/usr/local/opt/sqlite/bin:$PATH"
-- 日期函数
-- https://sqlite.org/lang_datefunc.html
-- 转换时间戳
select datetime( 1323648000, 'unixepoch' );
-- 获取时间戳, 带毫秒
SELECT CAST((julianday('now') - 2440587.5) * 86400000 AS INTEGER);
-- 默认值带毫秒
CREATE TABLE IF NOT EXISTS event
(
create_at DATETIME DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
);
-- 附加其他文件到数据库
ATTACH 'cache.db' AS cache;
-- 所有表
.tables
-- 显示 create 表
.schema urls