两双筷子 - SQLite
type: channel
link: https://www.dbkuaizi.com/tag/SQLite/
items: SQLite 函数
items: SQLite 基础
items: ThinkPHP 快速插入 SQLite

SQLite 函数
type: item
date: 2024-02-09T19:22:00+00:00
description: 聚合函数函数作用AVG(x)返回 x 列的平均值COUNT(x) / COUNT(*)统计条数，x 表示统计 x 列 不为null 的条数，* 表示统计总条数GROUP_CONCAT(X [,y])使用 "," 拼接 x 列，若传递 y，则使用 x 拼接 y 列MAX(x)返回 x 列中的最大值MIN(x)返回 x 列中的最小值SUM(x)返回 x 列的和，如果输入全是null，则返回 nullTOTAL(x)返回 x 列的和，如果输入全是null，则返回 0.00数值函数更多函数参考：https://www.sqlite.org/lang_mathfunc.html函数作用ABS(x)返回 x 的绝对值random()返回 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数MOD(x,y)返回 x/y 的模pi()返回 π 的近似值MAX(X1,X2[,...Xn])返回传入参数中的最大值MIN(X1,X2[,...Xn])返回传入参数中的最小值CEIL(x)返回大于X的最小整数值，3.35.0+FLOOR(x)返回小于x的最大整数值，3.35.0+ROUND(x,y)返回参数x的四舍五入到小数点y位的值，若忽略 y 则默认为 0POW(x,y) / POWER(x,y)返回 x 的 y 次方逻辑函数函数作用coalesce(X1,X2[,...Xn]) 返回列表中第一个非 NULL 的值，若全是NULL，则返回NULL，至少需要两个参数IFNULL(x,y) 如果 X 为 null，返回 y ,否则返回 xNULLIF(x,y)如果 x 与 y 不相同，则返回 x ，否则返回 NULLIIF(x,y,z)如果 x 为真 则返回 y 否则返回 z字符串函数函数作用replace(str,x,y)用字符串 y 替换字符串 str 中出现的所有字符串 xinstr(x,y)返回字符串 y 在 x 中出现的位置，不存在返回0 ，x、y 任意一个为NULL 则返回nulllength(str)返回字符串 str 的长度substr(str,x,y) 返回字符串 str 中 x 位置开始的 y 个字符，y 省略则返回剩下全部upper(str)返回 str 所有字符的大写lower(str)返回 str 所有字符的小写ltrim(str[,x])删除字符串 str 左侧的空格，如果传递字符串 x 则删除左侧的 x 字符串rtrim(str[,x])删除字符串 str 右侧的空格，如果传递字符串 x 则删除右侧的 x 字符串trim(str[,x])删除字符串 str 左右两侧的空格，如果传递字符串 x 则删除左右两侧的 x 字符串like('%x%', str)模糊查询字符串 x 在字符串 y 中是否存在时间函数函数作用date(timestring, modifier, modifier...)以 YYYY-MM-DD 格式返回日期time(timestring, modifier, modifier...)以 HH:MM:SS 格式返回日期datetime(timestring, modifier, modifier...)以 YYYY-MM-DD HH:MM:SS 格式返回日期julianday(timestring, modifier, modifier...)返回从格林尼治时间的公元前 4714年 11 月 24日 正午算起的天数unixepoch(timestring, modifier...)返回 自 1970-01-01 00:00:00 算起的秒数strftime(format, timestring, modifier, modifier)根据指定字符串格式化日期timediff(timestringA, timestringB)返回 从时间B 到时间A 相差时间的修饰符timestring 时间字符串modifier 修饰符format 格式化上面的几个时间函数，将时间字符串作为参数，时间字符串后面跟零个或多个 modifier 修饰符。时间字符串时间字符串可以使用下面任意一种格式：时间字符串示例YYYY-MM-DD2022-01-15YYYY-MM-DD HH:MM2022-01-15 22:30YYYY-MM-DD HH:MM:SS.SSS2022-01-15 22:35:02.100MM-DD-YYYY HH:MM15-01-2022 12:30HH:MM12:10YYYY-MM-DDTHH:MM2022-01-15 22:36HH:MM:SS22:30:01YYYYMMDD HHMMSS20220115 223701now当前日期 2022-01-15…

SQLite 基础
type: item
date: 2023-10-06T14:21:00+00:00
description: 数据库操作SQLite 官方给出一系列命令行工具（简称 CLP），可以通过命令行来操作 SQLite，下载地址：https://sqlite.org/download.html下载名为：sqlite-tools-xxx的文件，下面以 windows 为例：解压后可以使用 目录中的 sqlite3.exe 进入数据库操作。基础命令.open 打开一个 SQLite 文件，若没有则创建。.h 查看帮助.exit 退出命令行工具创建数据库数据库可以使用 Navicat 等数据库管理工具，直接创建一个文件，也可以使用命令行工具创建：PS E:\Tools> sqlite3 test.db虽然我们指定了数据库的名称，但目前位置SQLite 并未创建该数据库，直到数据库内部创建一些内容时，Sqlite 才会创建该数据库。这样做的好处是在数据库保存在磁盘之前，可以进行各种永久性设置，数据库一旦创建，一些设置例如页面大小、字符集是不能轻易改变的。要将数据库文件写入磁盘，只需要创建一个表：sqlite> create table test(id integer primary key,value text);现在创建了一个 test.db 数据库文件，数据库包含 test 表，表中有两列：id 主键列，当定义一个整形的主键列之后，SQLite 会在该列上应用自增函数。value ，一个简单的文本域插入数据sqlite> insert into test(value) values('张三'); sqlite> insert into test(value) values('李四'); sqlite> insert into test(value) values('王五'); sqlite> insert into test(value) values('赵六');查询数据sqlite> .mode column sqlite> select * from test; id value -- ----- 1 张三 2 李四 3 王五 4 赵六退出前创建一个索引和视图sqlite> create index test_idx on test(value); sqlite> create view schema as select * FROM sqlite_master;退出 SQLite 命令行.exitwindows 快捷键 Ctrl+CLinux 快捷键 Ctrl + D数据库信息获取表与视图： .tables查看索引： indices [表名称]查看DDL：使用 .schema [table name] 可以得到一个表或视图的定义语句，如果没有提供表名，则返回所有数据库对象 (table、index、view、trigger) 的定义语句：sqlite> .schema test CREATE TABLE test(id integer primary key,value text); CREATE INDEX test_idx on test(value); sqlite> .schema CREATE TABLE test(id integer primary key,value text); CREATE INDEX test_idx on test(value); CREATE VIEW schema as select * FROM sqlite_mastersqlite_master通过查询系统视图 sqlite_master 表可以获取更详细的信息：字段名说明type对象类型 （table、index、trigger、view）name对象名称tbl_name对象关联的表Rootpage对象跟页面所在的数据库索引sql对象的 SQL 定义查询返回结果如下：sqlite> .mode column sqlite> .headers on sqlite> select * from sqlite_master; type name tbl_name rootpage sql ----- -------- -------- -------- ---------------------------------------------------- table test test 2 CREATE TABLE test(id integer primary key,value text) index test_idx test 3 CREATE INDEX test_idx on test(value) view schema schema 0 CREATE VIEW schema as select * FROM sqlite_mas…

ThinkPHP 快速插入 SQLite
type: item
date: 2022-01-15T07:55:00+00:00
description: 因为 SQLite 是无服务数据库，所以每次操作数据都需要消耗磁盘 IO。而想要快速插入数据，本质上就是想要考虑如何减少磁盘 IO 次数。减少 io 有很多种方法，比如 批量插入、使用事务、内存模式等，这里的优化方案选择批量插入和事务这次测试使用的是一个15W 条数据的 CSV文件，将读入的数据写入数据库。需要注意的是，本文只是优化方案的记录，并非严格的性能测试，所以这些性能结果在不同设备上会有所差异，不过这不重要。单条插入我们先测试一下单条插入的性能：因单条插入性能实在太低，我们只测试一分钟的插入条数。$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); foreach ($data as $item){ Db::table('bank_test')->insert($item); // 执行 60 秒退出 if((microtime(true) - $stime) > 60) { exit; } }经过测试一分钟插入数据 665 条，因为每次写入都是一次磁盘操作，所以性能非常低。批量插入$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); $data_chunk = array_chunk($data,300); foreach ($data_chunk as $item) { Db::table('bank_test')->insertAll($item); } $etime=microtime(true);//获取程序执行结束的时间 $total=$etime-$stime; //计算差值 // 指令输出 $output->writeln("插入:".count($data)."数据，耗时：{$total}");结果：插入 150792 数据，耗时 64.941246032715 秒可以看出来，即使只用上了批量插入，性能的提升也是非常明显的。需要注意的是，单次批量插入条数有限制，有的文章说单次不能超过 999 条，但我测试不能超过 300 条。使用事务使用事务提升插入性能是 SQLite 的一个特性，原理就是将先将插入数据缓存到内存，最后提交事务的时候将数据写入磁盘，这样操作只会有一次磁盘IO。$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); Db::startTrans(); foreach ($data as $item) { Db::table('bank_test')->insert($item); } Db::commit(); $etime=microtime(true);//获取程序执行结束的时间 $total=$etime-$stime; //计算差值 // 指令输出 $output->writeln("插入:".count($data)."数据，耗时：{$total}");结果：插入 150792 数据，耗时 62.787980079651 秒可以看到 使用事务之后，即使是单条插入，性能也是很高的。批量+事务那么我们是否可以将上面的 事务和批量插入结合在一起使用呢？$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $t…