sqlite 使用

发布时间： 更新时间： 总字数：3662 阅读时间：8m 作者：IP：上海 网址

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

安装

下载地址：

Linux

  • apt
apt install sqlite3
  • 源码安装

请访问 SQLite 下载页面，从源代码区下载 sqlite-autoconf-*.tar.gz，编译步骤如下：

$ tar xvfz sqlite-autoconf-3071502.tar.gz
$ cd sqlite-autoconf-3071502
$ ./configure --prefix=/usr/local
$ make
$ make install

Windows

下载 sqlite-tools-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。

创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。

添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果：

C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

源码编译

apt install gcc make tcl-dev  ;#  Make sure you have all the necessary build tools
tar xzf sqlite.tar.gz         ;#  Unpack the source tree into "sqlite"
mkdir bld                     ;#  Build will occur in a sibling directory
cd bld                        ;#  Change to the build directory
../sqlite/configure           ;#  Run the configure script
make sqlite3                  ;#  Builds the "sqlite3" command-line tool
make sqlite3.c                ;#  Build the "amalgamation" source file
make sqldiff                  ;#  Builds the "sqldiff" command-line tool
# Makefile targets below this point require tcl-dev
make tclextension-install     ;#  Build and install the SQLite TCL extension
make devtest                  ;#  Run development tests
make releasetest              ;#  Run full release tests
make sqlite3_analyzer         ;#  Builds the "sqlite3_analyzer" tool

安装 go-sqlite3

先安装mingw-w64-install.exe，下载地址：https://sourceforge.net/projects/mingw-w64/files/

安装后，配置 path 路径，然后执行安装：

go build github.com/mattn/go-sqlite3

sqlite 管理工具

snap install sqlitebrowser
  • DBhub share Public and private databases
    • API 提供 golang SDK

命令行

SQLite 命令被称为 SQLite 的点命令，这些命令的不同之处在于它们不以分号 ; 结束。

让我们在命令提示符下键入一个简单的 sqlite3 命令，在 SQLite 命令提示符下，您可以使用各种 SQLite 命令。

sqlite3 ...

查看默认配置

.show 命令查看 SQLite 命令提示符的默认设置：

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: :memory:

# 查看数据库信息
sqlite> .dbinfo

sqlite> SELECT sqlite_version();

确保：提示符与点命令之间没有空格，否则将无法正常工作。

打开数据库

sqlite3 <sqlite3 db name>

sqlite3 <sqlite3 db name> [cmd]

# demo
sqlite3 /tmp/test.db

sqlite3 /tmp/test.db ".dbinfo"
  • SQLite3 数据库文件在多个服务或进程同时访问时的行为取决于具体的操作类型和并发控制机制
    • SQLite 允许多个进程同时读取数据库，所有读取操作可以并行执行，取时获取共享锁（SHARED lock），不阻塞其他读取
    • 同一时间仅允许一个写入操作，写入进程会获取排他锁（EXCLUSIVE lock），期间其他所有读写操作被阻塞

格式化输出

您可以使用下列的点命令来格式化输出为本教程下面所列出的格式：

sqlite> .header on
sqlite> .mode column
sqlite> .timer on
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: column
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: :memory:
sqlite>

数据类型

  • NULL
  • INTEGER 整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中
  • REAL 浮点值，存储为 8 字节的 IEEE 浮点数字
  • TEXT 文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储
  • BLOB blob 数据

创建表

sqlite> CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

# 出入数据
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

查表

sqlite> .tables
account_user                   djcelery_workerstate

sqlite> .tables '%user%'

查数据

sqlite> select * from account_user;
id          password    last_login                  is_superuser  username    chname      company     qq          phone       is_staff    date_joined                 email
----------  ----------  --------------------------  ------------  ----------  ----------  ----------  ----------  ----------  ----------  --------------------------  ----------
1                       2019-05-24 11:14:22.363068  1             admin                                                       1           2019-05-24 11:14:22.363068
Run Time: real 0.001 user 0.000131 sys 0.000073

查表结构

sqlite> .schema account_user
CREATE TABLE IF NOT EXISTS "account_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "password" varchar(128) NOT NULL, "last_login" datetime NULL, "is_superuser" bool NOT NULL, "username" varchar(128) NOT NULL UNIQUE, "chname" varchar(254) NOT NULL, "company" varchar(128) NOT NULL, "qq" varchar(32) NOT NULL, "phone" varchar(64) NOT NULL, "is_staff" bool NOT NULL, "date_joined" datetime NOT NULL, "email" varchar(254) NOT NULL);

退出

sqlite> .exit

PRAGMA

SQLite 的 PRAGMA 命令用来设置 SQLite 环境内控制各种环境变量和状态标志，一般用法

PRAGMA pragma_name;
PRAGMA pragma_name = value;

auto_vacuum

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = <mode>;

model 值：

  • 0 或 NONE 禁用 Auto-vacuum。这是默认模式，意味着数据库文件尺寸大小不会缩小，除非手动使用 VACUUM 命令
  • 1 或 FULL 启用 Auto-vacuum，是全自动的。在该模式下，允许数据库文件随着数据从数据库移除而缩小
  • 2 或 INCREMENTAL 启用 Auto-vacuum，但是必须手动激活。在该模式下，引用数据被维持，自由页面只放在自由列表中
    • 这些页面可在任何时候使用 incremental_vacuum pragma 进行覆盖。

检查 sqlite 文件

# ok 表示正常
PRAGMA integrity_check;

# quick_check 是 integrity_check 的简化版，速度更快，但可能无法检测所有问题
PRAGMA quick_check;

# 修复命令
sqlite3_recover your_database.sqlite > recovered.sql

table_list

  • displaying table metadata
PRAGMA table_list;

PRAGMA table_list('main');

user_version

  • user_version 用来获取或设置存储在数据库头的用户自定义的版本值
PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

Write ahead logging

  • https://sqlite.org/wal.html
  • WAL(Write ahead logging) 是一种日志模式，每个事务执行变更时，修改数据页，同时会产生日志，在事务提交后，不需要将修改的脏页刷盘，只需要将事务产生的日志落盘即可返回
# 命令行
sqlite3 db.sqlite3 'PRAGMA journal_mode=WAL;'

# 交互
sqlite3 db.sqlite3
> pragma journal_mode=wal
> PRAGMA journal_mode=DELETE;

# python3 代码实现
conn = sqlite3.connect('app.db', isolation_level=None)
conn.execute('pragma journal_mode=wal')
conn.execute('PRAGMA journal_mode = WAL')

journal_mode 参数值：

  • DELETE 原始数据页存放在日志文件中，事务提交时，将文件删除
  • TRUNCATE 与 DELETE 模式的区别是，清空日志文件，但不删除文件清空文件往往比删除文件要快
  • PERSIST 与 DELETE 和 TRUNCATE 模式区别是，既不删除文件，也不清空文件，而是将日志文件第一个页设置标记(置 0)，这个也是为了提高性能
  • MEMORY 内存模式，修改不落盘，无法保证事务的原子性
  • OFF 不开启日志，没法保证事务的原子性
  • WAL(write ahead log) 日志中记录修改页，提交时只需刷修改页，3.7.0 引入

开启后会产生两个文件：

  • .db-shm 共享内存文件，仅当 SQLite 以 WAL（预写日志）模式运行时才存在
    • 在 WAL 模式下，共享同一个 db 文件的数据库连接必须全部更新同一存储位置（用作 WAL 文件的索引），以防止发生冲突
  • .db-wal wal 文件

避免空间暴涨的参数（参考 Avoiding Excessively Large WAL Files）：

  • wal_autocheckpoint 用来设置触发检查点的时机，默认是 1000 页，当日志增长到 1000 页时，开始做检查点操作，SQLite 中没有单独的检查点线程，如果设置 1000，则触发写 1000 页的事务来进行检查点操作。此时，这个事务的响应时间会比较长，而其它事务则不受影响
  • journal_size_limit 用来设置日志文件的大小，默认情况为-1，当这个参数设置时，若累计更新页大小超过 journal_size_limit，也会导致检查点触发，用以重复利用日志文件，避免日志继续增长

Vacuum

VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件

sqlite3 database_name "VACUUM;"

内置的 tables

  • 在 SQLite 中，以 sqlite_ 开头的系统表是数据库引擎内部用于存储元数据和统计信息的特殊表
  • 查看内置的表
    • 在 SQLite 3.33.0 之前称为 sqlite_master，之后更名为 sqlite_schema，但旧名称仍兼容
SELECT name FROM sqlite_schema WHERE type ='table' AND name LIKE 'sqlite_%';

sqlite_schema

sqlite_schema 是 SQLite 数据库中的一个 系统表（也称为元数据表），它存储了当前数据库的结构信息（即数据库对象的定义）。通过查询 sqlite_master，可以获取数据库中所有表、索引、视图和触发器的元数据。

SELECT * FROM sqlite_schema;

SELECT * FROM sqlite_schema WHERE type = 'table';

sqlite_schema 表包含以下字段：

字段名 类型 说明
type TEXT 对象类型（如 table、index、view、trigger）
name TEXT 对象名称（如表名、索引名）
tbl_name TEXT 对象关联的表名（对于索引和触发器，表示其所属的表）
rootpage INTEGER 对象在数据库文件中的根页号（内部存储结构，通常无需直接操作）
sql TEXT 创建该对象的 SQL 语句（即 CREATE TABLE、CREATE INDEX 等语句）

sqlite_temp_master / sqlite_temp_schema

作用：存储临时数据库的元数据（临时表、索引等）

sqlite_sequence

  • 作用：管理 AUTOINCREMENT 字段的当前最大值
  • 字段：
    • name：表名
    • seq：当前自增序列值

sqlite_stat1

  • 作用：存储表的统计信息，帮助查询优化器生成高效执行计划
  • 字段：
    • tbl：表名
    • idx：索引名（若为表统计，则为 NULL）
    • stat：统计信息（格式为逗号分隔的值，如样本数量、列分布）
  • 生成方式：需手动执行 ANALYZE 命令生成

其他

文件格式

$ file <sqlite3.db>
  • version-valid-for 是 SQLite 文件头中的一个字段（偏移量 0x5C），它与 change_counter（偏移量 0x60）配合使用，用于确保数据库文件的一致性
    • 当 SQLite 写入数据页时，会更新 change_counter（每次事务提交递增）
    • version_valid_for 记录了当前文件头元数据（如 change_counter）与数据页的一致性状态
    • 如果 version_valid_for 与 change_counter 不匹配，说明文件头和数据页可能不一致（例如写入被中断）

远程复制

F&Q

sqlite3.DatabaseError: database disk image is malformed

  • 原因：sqlite 没有被正常关闭导致
  • 解决：重新常见库
# 导出数据
sqlite3 my.sqlite3
sqlite>.output tmp.sql
sqlite>.dump
sqlite>.quit

# 导入新库
sqlite3 mynew.sqlite3
sqlite>.read tmp.sql
sqlite>.quit

Unable to open database “sqlite.db”: file is encrypted or is not a database

sqlite 打开的版本不对

file sqlite.db # 查看当前 sqlite 的版本，如果是 sqlite3 的版本，使用如下命令打开
sqlite3 sqlite.db
    最新评论
    本文总阅读量 次 本站总访问量 次 本站总访客数 人