PHP上的SQLite初体验

发表于 | 分类于 ，

这两天在折腾一个小的推广页面，里边有个需求是提交表单和数据汇总，估了下压力应该不大，懒得再折腾MySQL服务了，就想试试SQLite。我后端主要是用PHP打配合，查了下文档，SQLite在PHP上主要有纯原生的SQLite扩展和数据库抽象层PDO(PHP数据对象)两种调用方式。由于之前写MySQL的时候接触过PDO，所以这次也优先选择了这种方式 （主要是换个DSN就行了，可以节省一些学习成本😜）。
数据库还需要一个图形界面来打配合（需要展示数据的场景，我个人认为GUI会比CLI有优势；当然如果换成命令行也行），MySQL的时候有个现成的NaviCat for MySQL，但并不通用；所以我就上网去搜有什么软件可以用。最先映入眼帘的是SQLiteStudio，开源、绿色（Portable版本）、多平台，搜到的讨论也很多，就去下载下来试了下。喏：
SQLiteStudio
第一感觉就是：丑！丑爆了……不过丑可能是Qt的通病吧，暂且按下不表。但我选的语言配置是中文诶，连菜单都没覆盖到是什么鬼😤……好吧抛开这些不谈，软件基本能用，就先开工吧。

既然有了工具辅助，那么就可以开始建库了。此处建议先行补一下SQLite的基础知识，我是直接看的菜鸟教程……权威倒不敢说，贵在简单。SQLite大概是最迷你的关系数据库系统了，小就小在没有很多大型数据库的各种高级功能，自然也可以做到相对轻量和快速。SQLite的类型系统不像MySQL那样默认支持SQL的所有数据类型，它使用一种类似解释型语言的动态类型系统，默认类型(即Storage Class，存储类型)只有NULL、INTEGER(整型)、REAL(浮点型)、TEXT(字符串)和BLOB(二进制)这几种；而在核心类型以外，还存在一种被称为类型关联(Type Affinity)的概念(菜鸟将此处译做“亲和类型”)，即将多个类型映射成几个近似的基本类型，以确保SQLite可以支持更多的类型。在具体实现上，相关的映射关系：

来自语句中的类型 最终关联类型
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8		 INTEGER
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB		 TEXT
BLOB
no datatype specified		 NONE
REAL
DOUBLE
DOUBLE PRECISION
FLOAT		 REAL
NUMERIC
DECIMAL(10,5)
BOOLEAN
DATE
DATETIME		 NUMERIC

可以看到也是5个类型。不过Type Affinity并不是必须的，最终的类型
另外还有两个类型，布尔型和日期/时间类型，前者会被转换为整型的0(FALSE)和1(TRUE)，后者则会被存储为TEXT、REAL或INTEGER。

好，到这时，就可以到SQLiteStudio中实践一下了。打开软件，找到”Database->Add a database”，类型选SQLite3就可以，点绿加号选择文件的保存位置，下边的Name其实是在左侧列表中显示的名字，点确定就可以了。
一开始还没建表，所以一片灰什么都没……右击”Tables”选择”Create a table”，打开列表设计页，输入”Table name”之后就可以建表了。双击空白处打开字段编辑框，然后按一般套路去搞就行了。这里我需要提交的表单信息很少，所以除了必要的姓名手机号以外，就多建了个id(多说一句，勾上主键点开配置，勾上”Autoincrement”就是自增)。

插一句，在折腾的过程中又发现了一个GUI程序DB Browser for SQLite，体验比SQLiteStudio好太多：
DB Browser for SQLite
DB Browser很符合我心目中对数据库浏览器的要求：足够简单(就SQLite而言)、界面友好(全中文覆盖完全)、功能实际(有个细节，在新建字段时，默认只提供INTEGER、TEXT、NONE、REAL、NUMERIC这5种，起码在我看来这降低了复杂度，且符合原有设计)，所以建议换这个。

表设计的差不多，就可以开搞PHP这边了。
数据库操作选择了用PDO来实现，因为之前写过……PDO的好处就是对于实现了驱动的数据库，换个DSN就行了，后边再用SQL指令、事务操作什么的都是通用的。所以有：

<?php

header('content-type: text/html;charset=utf-8');
header('Cache-Control: no-cache');

try {
    $pdo = new PDO('sqlite:../data/test.db'); // 指向数据库文件
    $pdo->exec('set names utf8'); // 设置操作编码
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 遇错误抛出异常
} catch (PDOExceiption $e) {
    exit('ERROR!');
}

当然这只是完成了最基本的功能：连接，如果没有错误发生的话，它连报错也不会。所以在后边，需要加一点SQL语句进去，鉴于我的需求主要是写入数据，所以我们需要在这里加上预处理语句。什么是预处理语句呢？可以把它看作是想要运行的SQL的一种编译过的模板，它可以使用变量参数进行定制。
那么接下来：

<?php

header('content-type: text/html;charset=utf-8');
header('Cache-Control: no-cache');

try {
    $pdo = new PDO('sqlite:../data/test.db'); // 指向数据库文件
    $pdo->exec('set names utf8'); // 设置操作编码
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 遇错误抛出异常

    $sql = 'INSERT INTO user (user, phone, city) VALUES (:user, :phone, :city)'; // 这里用的是冒号式的具名占位符，也可以用`?`来做匿名占位符，下边绑定时改用数字序号就行

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':user', $user); //绑定占位符对应的值
    $stmt->bindParam(':phone', $phone);
    $stmt->bindParam(':city', $city);
    $stmt->execute();

} catch (PDOExceiption $e) {
    exit('ERROR!');
}

到这里，前边的代码基本能跑了。但是可以留意到错误处理这块还是很简陋，没有error信息也没有留下log，那么把这里完善一下：

<?php

header('content-type: text/html;charset=utf-8');
header('Cache-Control: no-cache');

try {
    $pdo = new PDO('sqlite:../data/test.db'); // 指向数据库文件
    $pdo->exec('set names utf8'); // 设置操作编码
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 遇错误抛出异常

    $sql = 'INSERT INTO user (user, phone, city) VALUES (:user, :phone, :city)'; // 这里用的是冒号式的具名占位符，也可以用`?`来做匿名占位符，下边绑定时改用数字序号就行

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':user', $user); //绑定占位符对应的值
    $stmt->bindParam(':phone', $phone);
    $stmt->bindParam(':city', $city);
    $stmt->execute();

} catch (PDOExceiption $e) {
    $now = date('Y-m-d H:m:s');
    $msg = '[time][' . $now . '][msg][' . $e->getMessage() . ']' . "\n";
    error_log($msg, 3, '../log/error.log');
}

那么到了这里，这段代码基本就算能开跑了。当然还是建议根据需要加上return语句，这里不多赘述。