Why Sqlite

相对于 MySQL, 少了许多需要维护的东西，比如说不用关心用户、权限和备份语句，只要保证自己的数据库文件是安全的就好。

数据类型

Sqlite 是动态数据类型的，也就是实际上他并不会限定每一列一定要存什么数据。sqlite 中定义数据类型仅仅是为了兼容 sql 语句。

Sqlite 中有五种数据类型：null, integer, real(float), text, blob. Sqlite 使用了亲和度来表示一个列倾向于存储什么类型。也分为了五种：integer, read, numeric, text, blob. 其中 numeric 表示浮点和整数都可以。

Mysql 中的 varchar, text, integer, datetime 等都是支持的，分别会对应到这五种类型中对应的亲和度。值得注意的是，datetime 对应到的是数字类型。

另一点需要注意的是，即使定义了类型，sqlite 依然可以存储任意数据，比如说往 int 列中插入一个 str，并不会报错。sqlite 的数据类型是和数据本身绑定的，而不是列。

主键

使用 primary key 定义主键。默认情况下，主键就是自增的，但是有可能会复用被删除的 id, 使用 autoincrement 关键字之后就会严格自增，而不会复用之前的 id.

Sqlite 中的索引可以使用 create index 语句创建：

CREATE [UNIQUE] INDEX if not exists index_name
on table_name (column_name1, column_name2);

或者在 create table 语句的字段后面直接添加 index 关键字。

插入数据

可以直接采用 MySQL 的多行语法批量插入数据：

INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data1', 'data2');

upsert

sqlite 采用了 postgres 的 upsert 语句

INSERT INTO players (user_name, age)
  VALUES('steven', 32)
  ON CONFLICT(user_name)
  DO UPDATE SET age=excluded.age;  -- 这里使用 excluded 引用本来要添加的字段，如果是原来的字段直接使用，比如：count=count+1

命令行操作

内置管理命令

% sqlite3 DATABASE # 打开一个数据库

.databases    show databases connected to
.dump TABLE    dump table in SQL format
.import FILE ?TABLE?    import SQL data into table
.indices ?TABLE?    show indices of table
.mode MODE    set output mode(csv, column
.read FILE    excute FILE
.schema ?TABLE?    show create statement
.restore  ?DB? FILE    restore db from file
.headers ON|OFF    show headers

美化输出

让 sqlite 的输出更美观，使用 .mode column 和 .headers on 两个命令

sqlite> select * from foo;
234|kshitiz|dba.se

sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se

sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se

对于字段更多的表，使用 .headers line 竖排显示更合理。

可以把这两个命令放到 ~/.sqliterc 文件中，这样每次就自动执行啦

导入导出 csv

导出

sqlite> .mode csv   -- use ".separator SOME_STRING" for other separator
sqlite> .headers on
sqlite> .out file.csv
sqlite> select * from MyTable;

导入

sqlite> .mode csv
sqlite> .import CSV_FILE TABLE_NAME

导入导出 sql

sqlite3 DATABASE .dump > backup.sql
sqlite3 DATABASE < backup.sql

或者

sqlite3> .read backup.sql
sqlite3> .dump backup.sql

Gotchas

实现自增主键，必须使用 Integer Primary Key，不能使用 Int Primary Key 等其它类型

并发使用

如果两个进程同时尝试写入同一个 Sqlite 数据库，可能会触发 Database is locked 异常。

必须启用 Write Ahead Logging 模式才能使用并发写入

参考

1. https://www.sqlite.org/datatype3.html
2. https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database
3. https://stackoverflow.com/questions/15277373/sqlite-upsert-update-or-insert
4. https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/