网站com域名上不去cn能,开网店卖什么最赚钱,wordpress wpscan,有哪些网站可以做ppt目录 摘要Sqlite3SQLite 命令SQLite 语法SQLite 数据类型列亲和类型——优先选择机制 SQLite 创建数据库SQLite 附加数据库SQLite 分离数据库 SQLite 创建表SQLite 删除表 SQLite Insert 语句SQLite Select 语句SQLite 运算符SQLite 算术运算符SQLite 比较运算符SQLite 逻辑运算… 目录 摘要Sqlite3SQLite 命令SQLite 语法SQLite 数据类型列亲和类型——优先选择机制 SQLite 创建数据库SQLite 附加数据库SQLite 分离数据库 SQLite 创建表SQLite 删除表 SQLite Insert 语句SQLite Select 语句SQLite 运算符SQLite 算术运算符SQLite 比较运算符SQLite 逻辑运算符SQLite 位运算符 SQLite 表达式 golang操作sqlitegolang操作sqlite3加速本地结构化数据查询SimpleReaderMapReadersqlReader 总结轶闻趣事SQLite 背后的故事 摘要
在没有网络支持且仅能使用 Go 语言包的开发环境中如果需要频繁查询一个较大的 CSV 文件有几种优化方案可以考虑。 传统的遍历方法 使用 Go 的 csv 库针对每次查询都遍历整个 CSV 文件。这种方法简单易懂但性能较差尤其在数据量较大时每次查询都需要重新读取整个文件效率低下。 将数据加载到内存Map 存储 通过一次性读取 CSV 文件并将其存储在内存中的 map 结构中可以大幅提高查询性能。利用 map 提供的 O(1) 查找时间查询操作会变得非常迅速。这种方法适用于数据集较小到中等的情况且查询操作较为简单。需要注意的是数据量过大会导致内存消耗过高。 使用 SQLite 存储数据 如果需要更高效的查询特别是支持更复杂筛选的场景可以考虑将 CSV 文件的数据导入到 SQLite 中。SQLite 提供了轻量级的 SQL 查询功能可以通过创建表和索引来加速数据检索同时支持复杂的查询操作如筛选、排序、聚合等。SQLite 不需要单独安装任何服务直接通过 Go 包 github.com/mattn/go-sqlite3 即可进行操作适合在 Golang 环境中使用。
每种方法都有其适用场景对于小数据集和简单查询map 存储足够快速且轻量而对于较大数据集或复杂查询SQLite 提供了更好的查询灵活性和性能。
Sqlite3
SQLite3 是一个开源的、轻量级的关系型数据库管理系统 (RDBMS)它的主要特点是将数据库引擎嵌入到应用程序中而无需依赖服务器端数据库系统。SQLite3 以其小巧、高效、易于集成和高可靠性成为许多桌面、移动设备及嵌入式系统中常用的数据库解决方案。
SQLite3 的基本特点 嵌入式数据库 SQLite3 是一个 嵌入式数据库意味着它是一个库.dll 或 .so 文件而非独立的服务器程序。数据库的所有数据都存储在单一的文件中应用程序通过链接该库来操作数据库。这使得 SQLite3 在轻量级应用和小型项目中非常流行。 跨平台支持 SQLite3 支持几乎所有的操作系统包括但不限于 Windows、Linux、macOS、iOS、Android 等。由于它是 C 语言编写的可以轻松地跨平台移植。 无服务器架构 SQLite3 是 无服务器的这意味着没有独立的数据库进程或服务。应用程序直接访问数据库文件且访问数据库不依赖网络连接。它通过文件系统进行 I/O 操作查询和写入数据速度非常快。 事务支持 SQLite3 完全支持 ACID原子性、一致性、隔离性、持久性事务这意味着它可以确保数据的完整性。在发生崩溃时SQLite3 会自动回滚到事务开始之前的状态确保数据的可靠性。 轻量级 SQLite3 的二进制文件非常小通常只有几百 KB具体大小取决于编译选项。它不需要配置或安装复杂的数据库服务器适合于嵌入式应用、小型应用、单用户应用和其他资源有限的系统。 零配置 SQLite3 无需安装或配置数据库引擎。只需要包含数据库文件和链接库即可完全适合于需要零配置的场景。所有数据库设置都是在数据库文件级别进行的。 自包含 SQLite3 是 自包含的它包含了所有的功能。SQLite 的操作和配置不依赖于外部软件或库。SQLite3 将数据库及其管理功能直接集成到应用程序中进一步简化了使用过程。
SQLite3 的工作原理
SQLite3 将数据存储在单一的文件中这个文件中不仅存储了数据还包括了数据库的元数据如表结构、索引等。这种设计使得 SQLite3 对于需要将数据库存储在单一文件中的场景非常合适。
SQLite3 的数据库文件通常具有 .sqlite 或 .db 扩展名且它是 按页存储数据的。SQLite3 使用 1024 字节默认为基本存储单元每个页面可以存储一个数据记录、索引节点、事务日志等。
当应用程序需要执行 SQL 查询时它通过 SQLite3 的 API 来与数据库文件进行交互。SQLite3 会执行 SQL 语句操作数据库文件然后返回结果。
SQLite3 的优势 性能优越 SQLite3 在单机应用中表现出色。由于 SQLite3 是直接对磁盘文件进行操作并且没有复杂的数据库管理层因此它的 I/O 性能相对较高尤其在查询操作时速度较快。对于中小型数据集SQLite3 几乎能够提供与传统数据库相媲美的性能。 高效的内存使用 SQLite3 将数据直接存储到文件中避免了传统数据库系统的内存消耗。在单个数据库连接上SQLite3 能高效地使用内存并且能在内存不足时通过分页操作优化性能。 简单易用 SQLite3 的使用极为简单不需要任何配置或启动额外的服务。只需要导入相应的 Go 包如 github.com/mattn/go-sqlite3即可与 SQLite3 交互。同时它也支持完整的 SQL 语法使得它对于熟悉 SQL 的开发人员来说非常直观。 事务支持和原子性 SQLite3 支持完整的事务机制它会自动进行事务的回滚确保数据的一致性。在事务期间的所有操作都是原子的要么全部成功要么全部回滚避免部分提交导致的数据不一致。 支持嵌入式环境 由于 SQLite3 是嵌入式数据库它非常适合在嵌入式设备、移动应用、桌面应用、物联网设备等资源有限的环境中使用。
SQLite3 的常见用途
移动应用SQLite3 是 Android 和 iOS 移动操作系统的默认数据库。它允许开发者在本地存储数据无需依赖外部数据库服务。桌面应用许多桌面应用程序如浏览器、邮件客户端等都使用 SQLite3 来存储用户数据和配置文件。嵌入式设备SQLite3 被广泛用于嵌入式设备和物联网设备中能够在有限的资源下提供数据库功能。数据存储它也常常作为数据存储的解决方案例如用于存储日志、历史记录、配置文件等。快速原型开发SQLite3 适合快速原型开发和小型项目开发者可以快速实现数据库功能而不必担心数据库服务器的配置和维护。
SQLite3 与其他数据库的比较
特性SQLite3MySQL/PostgreSQL安装与配置零配置直接使用数据库文件需要安装和配置数据库服务服务器需求无需服务器嵌入式数据库需要独立的数据库服务器性能对小型数据集性能优越大型数据集查询更高效事务支持完全支持 ACID 事务完全支持 ACID 事务扩展性适合小到中型应用单用户查询支持高并发、多用户、大数据量使用场景本地存储、嵌入式、移动应用等企业级应用、复杂查询等
总而言之SQLite3 是一个轻量级的、无服务器的数据库系统非常适合嵌入式应用、桌面应用和移动应用。它通过将数据库引擎嵌入到应用程序中使得开发者能够快速实现数据存储功能且不需要额外的数据库服务器或配置。对于小型到中型的数据存储需求SQLite3 提供了高效、可靠的解决方案同时也支持事务、ACID 原则等关系型数据库的重要特性。
SQLite 命令
如果你在sqlite3官网下载了一个sqlite引擎那么就可以像python一样在cmd敲下一个sqlite3命令然后在交互式命令行中使用sqlite3。笔者没有下载
在 sqlite3 中SQL 语句需以分号 ; 结尾才会执行允许跨行输入。特殊的点命令如 .help 和 .tables以小数点 . 开头不需要分号结尾。
$ sqlite3
SQLite version 3.3.6
Enter .help for instructions
sqlitesqlite3的命令以.开头如需获取可用的点命令的清单可以输入 “.help”。例如
sqlite.help上面的命令会显示各种重要的 SQLite 点命令的列表如下所示
命令描述.backup ?DB? FILE备份 DB 数据库默认是 main到 FILE 文件。.bail ON|OFF发生错误后停止。默认为 OFF。.databases列出数据库的名称及其所依附的文件。.dump ?TABLE?以 SQL 文本格式转储数据库。如果指定了 TABLE 表则只转储匹配 LIKE 模式的 TABLE 表。.echo ON|OFF开启或关闭 echo 命令。.exit退出 SQLite 提示符。.explain ON|OFF开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数则为 EXPLAIN on即开启 EXPLAIN。.header(s) ON|OFF开启或关闭头部显示。.help显示消息。.import FILE TABLE导入来自 FILE 文件的数据到 TABLE 表中。.indices ?TABLE?显示所有索引的名称。如果指定了 TABLE 表则只显示匹配 LIKE 模式的 TABLE 表的索引。.load FILE ?ENTRY?加载一个扩展库。.log FILE|off开启或关闭日志。FILE 文件可以是 stderr标准错误/stdout标准输出。.mode MODE设置输出模式MODE 可以是下列之一 csv 逗号分隔的值column 左对齐的列html HTML 的 table 代码insert TABLE 表的 SQL 插入insert语句line 每行一个值list 由 .separator 字符串分隔的值tabs 由 Tab 分隔的值tcl TCL 列表元素 .nullvalue STRING在 NULL 值的地方输出 STRING 字符串。.output FILENAME发送输出到 FILENAME 文件。.output stdout发送输出到屏幕。.print STRING...逐字地输出 STRING 字符串。.prompt MAIN CONTINUE替换标准提示符。.quit退出 SQLite 提示符。.read FILENAME执行 FILENAME 文件中的 SQL。.schema ?TABLE?显示 CREATE 语句。如果指定了 TABLE 表则只显示匹配 LIKE 模式的 TABLE 表。.separator STRING改变输出模式和 .import 所使用的分隔符。.show显示各种设置的当前值。.stats ON|OFF开启或关闭统计。.tables ?PATTERN?列出匹配 LIKE 模式的表的名称。.timeout MS尝试打开锁定的表 MS 毫秒。.width NUM NUM为 column 模式设置列宽度。.timer ON|OFF 开启或关闭 CPU 定时器。
使用 .show 命令来查看 SQLite 命令提示符的默认设置。
sqlite.showecho: offexplain: offheaders: offmode: column
nullvalue: output: stdout
separator: |width:
sqlite可以使用下列的点命令来格式化输出为本教程下面所列出的格式
sqlite.header on
sqlite.mode column
sqlite.timer on
sqlite上面设置将产生如下格式的输出
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
CPU Time: user 0.000000 sys 0.000000SQLite 语法
SQLite 是不区分大小写的但也有一些命令是大小写敏感的比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
SQLite 注释是附加的注释可以在 SQLite 代码中添加注释以增加其可读性他们可以出现在任何空白处包括在表达式内和其他 SQL 语句的中间但它们不能嵌套。
SQL 注释以两个连续的 “-” 字符ASCII 0x2d开始并扩展至下一个换行符ASCII 0x0a或直到输入结束以先到者为准。
您也可以使用 C 风格的注释以 “/ 开始并扩展至下一个 /” 字符对或直到输入结束以先到者为准。SQLite的注释可以跨越多行。
sqlite.help -- 这是一个简单的注释所有的 SQLite 语句可以以任何关键字开始如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等所有的语句以分号 ; 结束如
SQLite ANALYZE 语句
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;SQLite AND/OR 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;SQLite ALTER TABLE 语句
ALTER TABLE table_name ADD COLUMN column_def...;SQLite ALTER TABLE 语句Rename
ALTER TABLE table_name RENAME TO new_table_name;SQLite ATTACH DATABASE 语句
ATTACH DATABASE DatabaseName As Alias-Name;SQLite BEGIN TRANSACTION 语句
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;SQLite BETWEEN 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;SQLite COMMIT 语句
COMMIT;SQLite CREATE INDEX 语句
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );SQLite CREATE UNIQUE INDEX 语句
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);SQLite CREATE TABLE 语句
CREATE TABLE table_name(column1 datatype,column2 datatype,column3 datatype,.....columnN datatype,PRIMARY KEY( one or more columns )
);SQLite CREATE TRIGGER 语句
CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN stmt1; stmt2;....
END;SQLite CREATE VIEW 语句
CREATE VIEW database_name.view_name AS
SELECT statement....;SQLite CREATE VIRTUAL TABLE 语句
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );SQLite COMMIT TRANSACTION 语句
COMMIT;SQLite COUNT 子句
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;SQLite DELETE 语句
DELETE FROM table_name
WHERE {CONDITION};SQLite DETACH DATABASE 语句
DETACH DATABASE Alias-Name;SQLite DISTINCT 子句
SELECT DISTINCT column1, column2....columnN
FROM table_name;SQLite DROP INDEX 语句
DROP INDEX database_name.index_name;SQLite DROP TABLE 语句
DROP TABLE database_name.table_name;SQLite DROP VIEW 语句
DROP VIEW view_name;SQLite DROP TRIGGER 语句
DROP TRIGGER trigger_nameSQLite EXISTS 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name );SQLite EXPLAIN 语句
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;SQLite GLOB 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };SQLite GROUP BY 子句
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;SQLite HAVING 子句
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);SQLite INSERT INTO 语句
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);SQLite IN 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);SQLite Like 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };SQLite NOT IN 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);SQLite ORDER BY 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};SQLite PRAGMA 语句
PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size 1024;
PRAGMA table_info(table_name);SQLite RELEASE SAVEPOINT 语句
RELEASE savepoint_name;SQLite REINDEX 语句
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;SQLite ROLLBACK 语句
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;SQLite SAVEPOINT 语句
SAVEPOINT savepoint_name;SQLite SELECT 语句
SELECT column1, column2....columnN
FROM table_name;SQLite UPDATE 语句
UPDATE table_name
SET column1 value1, column2 value2....columnNvalueN
[ WHERE CONDITION ];SQLite VACUUM 语句
VACUUM;SQLite WHERE 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;SQLite 数据类型
每个存储在 SQLite 数据库中的值都具有以下存储类之一、
存储类描述NULL值是一个 NULL 值。INTEGER值是一个带符号的整数根据值的大小存储在 1、2、3、4、6 或 8 字节中。REAL值是一个浮点值存储为 8 字节的 IEEE 浮点数字。TEXT值是一个文本字符串使用数据库编码UTF-8、UTF-16BE 或 UTF-16LE存储。BLOB值是一个 blob 数据完全根据它的输入存储。
SQLite 的数据类型并不是严格的类型约束。每一列的数据类型更准确地说是 存储类storage class。存储类定义了数据在 SQLite 中如何存储。
亲和类型描述TEXT数值型数据在被插入之前需要先被转换为文本格式之后再插入到目标字段中。NUMERIC当文本数据被插入到亲缘性为NUMERIC的字段中时如果转换操作不会导致数据信息丢失以及完全可逆那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据如果转换失败SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据SQLite将不做任何转换直接以NULL或BLOB的方式存储该数据。需要额外说明的是对于浮点格式的常量文本如30000.0如果该值可以转换为INTEGER同时又不会丢失数值信息那么SQLite就会将其转换为INTEGER的存储方式。INTEGER对于亲缘类型为INTEGER的字段其规则等同于NUMERIC唯一差别是在执行CAST表达式时。REAL其规则基本等同于NUMERIC唯一的差别是不会将30000.0这样的文本数据转换为INTEGER存储方式。NONE不做任何的转换直接以该数据所属的数据类型进行存储。
SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据当数据插入时该字段的数据将会优先采用亲缘类型作为该值的存储方式怎么理解呢
列亲和类型——优先选择机制
SQLite 支持的 列的亲和类型Affinity概念是它独特的设计之一它使得数据库系统具有非常灵活的类型管理机制。你可以将它理解为 类型优先级规则它规定了在数据插入时SQLite 会根据列的亲和类型来决定如何存储数据。尽管 SQLite 中的每列可以存储任何类型的数据但它会尽可能根据列的亲和类型来决定数据的存储方式。这使得 SQLite 的数据类型系统相对宽松也为灵活的数据存储提供了支持。
在 SQLite 中每个列都有一个亲和类型这并不是强制要求列只能存储某种数据类型而是提供了一种“优先选择”机制。SQLite 会尽量将数据存储为列亲和类型所指定的类型但如果存储的类型与亲和类型不匹配SQLite 会尝试做必要的类型转换最终以最适合的方式存储数据。
亲和类型与数据类型的区别
SQLite 的列本身并没有严格的数据类型约束它的“类型”更多的是与 亲和类型 相关而不是实际存储的数据类型。亲和类型 是一种类型约束它定义了 SQLite 如何优先处理列中的数据。
数据类型 是插入到列中的实际数据类型。亲和类型 是该列的“类型优先级”它影响数据存储时的转换方式。
SQLite 的数据类型系统是 动态类型 的这意味着列的数据类型并不严格限制于某种类型而是具有灵活性。每列的亲和类型告诉 SQLite 在数据插入时该使用什么优先类型来存储数据但这并不意味着强制要求必须遵循某种数据类型。
亲和类型的优先级
SQLite 中支持的亲和类型包括
TEXT优先存储为文本字符串UTF-8 或 UTF-16 编码。NUMERIC优先存储为数字整数或浮动点数如果不能以数字格式存储将尝试将数据转换为文本。INTEGER优先存储为整数。REAL优先存储为浮动点数。BLOB优先存储为二进制数据。
当你插入数据时SQLite 会根据列的亲和类型来确定数据存储的方式
亲和类型的转换规则
SQLite 对每一列的存储类型提供了 类型亲和规则。这些规则决定了当你插入数据时SQLite 会如何将数据转换为与列的亲和类型相匹配的格式。具体规则如下 TEXT 亲和类型 如果插入的是一个文本值如字符串直接存储为 TEXT。如果插入的是一个整数或浮动点数SQLite 会将其转换为文本。 NUMERIC 亲和类型 如果插入的是一个有效的整数或浮动点数SQLite 会存储为 INTEGER 或 REAL。如果插入的数据无法转换为数字如字符串 “abc”则 SQLite 会将其作为 TEXT 存储。 INTEGER 亲和类型 如果插入的数据是整数SQLite 会直接存储为 INTEGER。如果插入的是浮动点数或文本数据SQLite 会尽力转换为整数。 REAL 亲和类型 如果插入的是浮动点数直接存储为 REAL。如果插入的是整数SQLite 会将其转换为浮动点数。如果插入的是文本数据且它是有效的浮动点数SQLite 会将其转换为浮动点数。 BLOB 亲和类型 BLOB 数据不进行类型转换它将按原始二进制数据存储。
亲和类型示例 文本插入到整数列假设你有一个列定义如下 CREATE TABLE example (id INTEGER);你可以插入任意数据比如文本SQLite 会根据 INTEGER 亲和类型来转换数据 INSERT INTO example (id) VALUES (123);在这个例子中尽管插入的值是文本 123SQLite 会将其转换为 INTEGER 类型并存储为整数 123。 插入浮动点数到 TEXT 列假设你有一个列定义如下 CREATE TABLE example (name TEXT);你可以插入一个浮动点数SQLite 会将其转换为 TEXT INSERT INTO example (name) VALUES (123.45);SQLite 会将浮动点数 123.45 存储为文本字符串 123.45。 插入非数字文本到 NUMERIC 列假设你有一个列定义如下 CREATE TABLE example (value NUMERIC);你插入一个无法转换为数字的文本 INSERT INTO example (value) VALUES (abc);SQLite 会将 abc 存储为 TEXT因为它无法转换为数字。 插入二进制数据到 BLOB 列如果你有一个 BLOB 列数据会被存储为原始二进制数据 CREATE TABLE example (image BLOB);你插入一些二进制数据 INSERT INTO example (image) VALUES (X89504E470D0A1A0A0000000D494844520000);这将插入原始二进制数据而不进行任何转换。
SQLite 创建数据库
SQLite 本身 不支持用户权限管理 或 权限控制 机制因此不需要任何特殊的权限即可创建一个数据库。
$ sqlite3 DatabaseName.db通常情况下数据库名称在 RDBMS 内应该是唯一的。
另外我们也可以在sqlite交互界面使用 .open 来建立新的数据库文件
sqlite.open test.db打开已存在数据库也是用 .open 命令以上命令如果 test.db 存在则直接会打开不存在就创建它。
一旦数据库被创建就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中如下所示
sqlite.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中如下所示
$sqlite3 testDB.db .dump testDB.sql上面的命令将转换整个 testDB.db 数据库的内容到 SQLite 的语句中并将其转储到 ASCII 文本文件 testDB.sql 中。您可以通过简单的方式从生成的 testDB.sql 恢复如下所示
$sqlite3 testDB.db testDB.sqlSQLite 附加数据库
在 SQLite 中附加数据库Attach Database允许在一个数据库连接中打开多个数据库文件。这个特性非常有用尤其是在需要跨多个数据库进行查询的场景下。通过附加数据库SQLite 允许你在一个查询中访问多个数据库而不需要分别打开多个连接。
SQLite 使用 ATTACH DATABASE 命令将一个新的数据库文件附加到当前的数据库连接中。附加的数据库将拥有一个别名后续的 SQL 查询可以通过该别名来引用附加的数据库。
ATTACH DATABASE file_name AS database_name;如果想附加一个现有的数据库 testDB.db则 ATTACH DATABASE 语句将如下所示
sqlite ATTACH DATABASE testDB.db as TEST;使用 SQLite .database 命令来显示附加的数据库。
sqlite .database
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接且不应该被用于附加否则将得到一个警告消息如下所示
sqlite ATTACH DATABASE testDB.db as TEMP;
Error: database TEMP is already in use
sqlite ATTACH DATABASE testDB.db as main;
Error: database main is already in use然后你可以在查询时通过数据库别名来引用附加的数据库
-- 从主数据库和附加数据库中查询数据
SELECT * FROM main.users;
SELECT * FROM TEST.orders;也可以进行跨表查询
-- 打开两个数据库并附加
ATTACH DATABASE main.db AS main;
ATTACH DATABASE secondary.db AS secondary;-- 从不同的数据库查询数据
SELECT users.name, orders.order_date
FROM main.users
JOIN secondary.orders ON users.user_id orders.user_id;SQLite 分离数据库
SQLite 的 DETACH DATABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名DETACH 命令将只断开给定名称的连接而其余的仍然有效。您无法分离 main 或 temp 数据库。
DETACH DATABASE Alias-Name;假设在前面的章节中您已经创建了一个数据库并给它附加了 ‘test’ 和 ‘currentDB’使用 .database 命令我们可以看到
sqlite.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db
3 currentDB /home/sqlite/testDB.db现在让我们尝试把 ‘currentDB’ 从 testDB.db 中分离出来如下所示
sqlite DETACH DATABASE currentDB;现在如果检查当前附加的数据库您会发现testDB.db 仍与 ‘test’ 和 ‘main’ 保持连接。
sqlite.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.dbSQLite 创建表
SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表涉及到命名表、定义列及每一列的数据类型。
CREATE TABLE database_name.table_name(column1 datatype PRIMARY KEY(one or more columns),column2 datatype,column3 datatype,.....columnN datatype,
);CREATE TABLE 是告诉数据库系统创建一个新表的关键字。CREATE TABLE 语句后跟着表的唯一的名称或标识。您也可以选择指定带有 table_name 的 database_name。
而对于约束项sqlite和mysql都差不多可以参考mysql的建表语句。
下面是一个实例它创建了一个 COMPANY 表ID 作为主键NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL
sqlite CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL
);可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建该命令用于列出附加数据库中的所有表。
sqlite.tables
COMPANY DEPARTMENT可以使用 SQLite .schema 命令得到表的完整信息如下所示
sqlite.schema COMPANY
CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL
);SQLite 删除表
SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
DROP TABLE database_name.table_name;SQLite Insert 语句
INSERT INTO 语句有两种基本语法如下所示
# 在这里column1, column2,...columnN 是要插入数据的表中的列的名称。
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);# 如果要为表中的所有列添加值您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);假设已经在 testDB.db 中创建了 COMPANY表如下所示
sqlite CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL
);现在下面的语句将在 COMPANY 表中创建六个记录
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, Paul, 32, California, 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, Allen, 25, Texas, 15000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, Teddy, 23, Norway, 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, Mark, 25, Rich-Mond , 65000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, David, 27, Texas, 85000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, Kim, 22, South-Hall, 45000.00 );也可以使用第二种语法在 COMPANY 表中创建一个记录如下所示
INSERT INTO COMPANY VALUES (7, James, 24, Houston, 10000.00 );您可以通过在一个有一组字段的表上使用 select 语句填充数据到另一个表中。下面是语法
INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name[WHERE condition];SQLite Select 语句
SQLite 的 SELECT 语句的基本语法如下
SELECT column1, column2, columnN FROM table_name;因为所有的点命令只在 SQLite 提示符中可用所以当您进行带有 SQLite 的编程时您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表
sqlite SELECT tbl_name FROM sqlite_master WHERE type table;可以列出关于 COMPANY 表的完整信息如下所示
sqlite SELECT sql FROM sqlite_master WHERE type table AND tbl_name COMPANY;CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL
)SQLite 运算符
运算符是一个保留字或字符主要用于 SQLite 语句的 WHERE 子句中执行操作如比较和算术运算。
SQLite 算术运算符
假设变量 a10变量 b20则
运算符描述实例加法 - 把运算符两边的值相加 a b 将得到 30-减法 - 左操作数减去右操作数 a - b 将得到 -10*乘法 - 把运算符两边的值相乘 a * b 将得到 200/除法 - 左操作数除以右操作数 b / a 将得到 2%取模 - 左操作数除以右操作数后得到的余数 b % a 将得到 0
下面是 SQLite 算术运算符的简单实例
sqlite .mode line
sqlite select 10 20;
10 20 30sqlite select 10 - 20;
10 - 20 -10sqlite select 10 * 20;
10 * 20 200sqlite select 10 / 5;
10 / 5 2sqlite select 12 % 5;
12 % 5 2SQLite 比较运算符
假设变量 a10变量 b20则
运算符描述实例检查两个操作数的值是否相等如果相等则条件为真。 (a b) 不为真。检查两个操作数的值是否相等如果相等则条件为真。 (a b) 不为真。!检查两个操作数的值是否相等如果不相等则条件为真。 (a ! b) 为真。检查两个操作数的值是否相等如果不相等则条件为真。 (a b) 为真。检查左操作数的值是否大于右操作数的值如果是则条件为真。 (a b) 不为真。检查左操作数的值是否小于右操作数的值如果是则条件为真。 (a b) 为真。检查左操作数的值是否大于等于右操作数的值如果是则条件为真。 (a b) 不为真。检查左操作数的值是否小于等于右操作数的值如果是则条件为真。 (a b) 为真。!检查左操作数的值是否不小于右操作数的值如果是则条件为真。 (a ! b) 为假。!检查左操作数的值是否不大于右操作数的值如果是则条件为真。 (a ! b) 为真。
SQLite 逻辑运算符
下面是 SQLite 中所有的逻辑运算符列表。
运算符描述ANDAND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。BETWEENBETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。EXISTSEXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。ININ 运算符用于把某个值与一系列指定列表的值进行比较。NOT ININ 运算符的对立面用于把某个值与不在一系列指定列表的值进行比较。LIKELIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOBGLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于它是大小写敏感的。NOTNOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN等等。它是否定运算符。OROR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。IS NULLNULL 运算符用于把某个值与 NULL 值进行比较。ISIS 运算符与 相似。IS NOTIS NOT 运算符与 ! 相似。||连接两个不同的字符串得到一个新的字符串。UNIQUEUNIQUE 运算符搜索指定表中的每一行确保唯一性无重复。
假设 COMPANY 表有以下记录
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录
sqlite SELECT * FROM COMPANY WHERE AGE 25 AND SALARY 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录结果显示所有的记录意味着没有一个记录的 AGE 等于 NULL
sqlite SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0下面的 SELECT 语句列出了 NAME 以 ‘Ki’ 开始的所有记录‘Ki’ 之后的字符不做限制
sqlite SELECT * FROM COMPANY WHERE NAME LIKE Ki%;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0下面的 SELECT 语句列出了 NAME 以 ‘Ki’ 开始的所有记录‘Ki’ 之后的字符不做限制
sqlite SELECT * FROM COMPANY WHERE NAME GLOB Ki*;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0下面的 SELECT 语句列出了 AGE 的值为 25 或 27 的所有记录
sqlite SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0面的 SELECT 语句列出了 AGE 的值既不是 25 也不是 27 的所有记录
sqlite SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0下面的 SELECT 语句列出了 AGE 的值在 25 与 27 之间的所有记录
sqlite SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0下面的 SELECT 语句使用 SQL 子查询子查询查找 SALARY 65000 的带有 AGE 字段的所有记录后边的 WHERE 子句与 EXISTS 运算符一起使用列出了外查询中的 AGE 存在于子查询返回的结果中的所有记录
sqlite SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY 65000);
AGE
----------
32
25
23
25
27
22
24SQLite 位运算符
位运算符作用于位并逐位执行操作。真值表 和 | 如下
pqp qp | q0000010111111001
假设如果 A 60且 B 13现在以二进制格式它们如下所示
A 0011 1100B 0000 1101AB 0000 1100A|B 0011 1101~A 1100 0011下表中列出了 SQLite 语言支持的位运算符。假设变量 A60变量 B13则
运算符描述实例如果同时存在于两个操作数中二进制 AND 运算符复制一位到结果中。 (A B) 将得到 12即为 0000 1100|如果存在于任一操作数中二进制 OR 运算符复制一位到结果中。 (A | B) 将得到 61即为 0011 1101~二进制补码运算符是一元运算符具有翻转位效应即0变成11变成0。 (~A ) 将得到 -61即为 1100 0011一个有符号二进制数的补码形式。二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 A 2 将得到 240即为 1111 0000 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 A 2 将得到 15即为 0000 1111
下面的实例演示了 SQLite 位运算符的用法
sqlite .mode line
sqlite select 60 | 13;
60 | 13 61sqlite select 60 13;
60 13 12sqlite select (~60);
(~60) -61sqlite select (60 2);
(60 2) 240sqlite select (60 2);
(60 2) 15SQLite 表达式
…更多内容可以参考官方文档或者菜鸟教程SQLite 表达式
golang操作sqlite
golang操作sqlite有两个比较知名的库modernc.org/sqlite 和 github.com/mattn/go-sqlite3
modernc.org/sqlite 和 github.com/mattn/go-sqlite3 都是 Go 语言中的 SQLite 库用于与 SQLite 数据库交互。这两个库尤其各自的特性 依赖性和编译 modernc.org/sqlite: 这是一个纯 Go 实现的 SQLite 库意味着它不依赖于 C 编译器或者 SQLite 的 C 库。可以轻松地在没有 C 编译器的环境中使用适合跨平台开发。它还支持 SQLite 的大部分功能。没有 C 语言依赖避免了 C/C 编译的复杂性。 github.com/mattn/go-sqlite3: 这是一个基于 C 的 SQLite 实现需要在编译时链接 SQLite 的 C 库。它会依赖 C 编译器和 SQLite 的 C 库因此在一些没有 C 编译器的环境下使用起来可能较为复杂。需要在目标环境中安装 SQLite C 库可能会遇到与平台或环境相关的问题例如在某些系统中可能需要特定版本的 SQLite。 性能 modernc.org/sqlite: 由于它是纯 Go 实现可能会在性能上稍逊色于基于 C 的实现尤其是在需要处理大量数据或高并发时。但是它的性能仍然可以满足大多数应用的需求尤其是对于小型和中型应用。 github.com/mattn/go-sqlite3: 基于 C 实现的 SQLite 库通常会比纯 Go 实现更快特别是在大规模数据处理和复杂查询时。由于使用了原生 C 库它通常能够提供更高的性能尤其是当涉及到复杂的 SQL 操作时。 功能支持 modernc.org/sqlite: 支持大多数 SQLite 的功能包括事务、索引、查询等。支持SQLite3的大部分标准特性。但可能在某些细节实现上与 SQLite 原生版本有些差距。 github.com/mattn/go-sqlite3: 完全实现 SQLite 的功能并且通常会在新版本的 SQLite 发布后迅速进行更新。因为它是基于 SQLite C 库所以对于 SQLite 原生功能的支持更完整更新也更及时。 社区和维护 modernc.org/sqlite: 作为一个较新的库它的社区支持和文档可能不如 go-sqlite3 庞大。由于它是纯 Go 实现它在一些特殊场景下可能会出现一些性能瓶颈但这些问题通常会在未来的版本中逐步得到解决。 github.com/mattn/go-sqlite3: 这是 Go 中最常用的 SQLite 库之一社区活跃维护频繁。因为它依赖于 SQLite 的 C 库它的功能和性能通常比其他 Go SQLite 库更为可靠。 跨平台支持 modernc.org/sqlite: 由于是纯 Go 实现modernc.org/sqlite 具有更好的跨平台支持可以在没有 C 编译器的环境中运行适合容器化和某些轻量级环境。 github.com/mattn/go-sqlite3: 需要 C 编译器因此在某些系统或平台上特别是轻量级系统或容器环境可能会遇到安装和配置的问题。
选择哪个库取决于你的项目需求、环境限制以及对性能和功能的具体要求
modernc.org/sqlite 更适合需要完全 Go 实现、避免 C 依赖的项目尤其是在跨平台开发和不依赖 C 编译器的情况下。github.com/mattn/go-sqlite3 更适合需要 SQLite 原生支持、追求高性能并且没有 C 编译器限制的项目。
golang操作sqlite3加速本地结构化数据查询
回归正题假设现在有一个CSV文件现在为了测试其查询性能我们准备两个大小不一的CSV并且实现开头提到的三种查询方式。 在此之前我们先用golang实现一个测时的装饰器
package mainimport (fmttime
)func WithTimer(f func()) func() {return func() {start : time.Now()// do somethingf()// 打印输出耗时fmt.Printf(%.2fs elapsed\n, time.Since(start).Seconds())}
}func A() {time.Sleep(time.Second * 3)
}func main() {WithTimer(A)()
}// 程序输出
// 3.00s elapsed然后我们新建一个文件夹准备实现Reader接口 需要用到的三方库如下
github.com/mattn/go-sqlite3Cgo实现的sqlite3 driver更快github.com/jmoiron/sqlx强化版的database/sql库封装了一些常用方法
为了功能的实现代码健壮性暂时不在考虑范围内因此笔者会忽略掉大部分error处理。
SimpleReader
简单的遍历查询
// simpleReader实现的是普通遍历的方法
type SimpleReader struct {fd *os.Filerd *csv.Readerhead []string
}func NewSimpleReader(filename string) *SimpleReader {fp, _ : os.Open(filename)reader : csv.NewReader(fp)head, _ : reader.Read()return SimpleReader{fd: fp, rd: reader, head: head}
}func (r *SimpleReader) Search(field string, value string) []string {fieldIndex : slices.Index(r.head, field)for {record, err : r.rd.Read()if err io.EOF {break}if err ! nil {log.Fatal(err)}if record[fieldIndex] value {return record}}return nil
}func (r *SimpleReader) Close() {_ r.fd.Close()
}我们来测试一下它的1000次查询耗时
const (smallField field9smallValue value6_8bigField field9bigValue value99855_8searchCount 1000
)func main() {WithTimer(func() {for i : 0; i searchCount; i {rd : reader.NewSimpleReader(small.csv)_ rd.Search(smallField, smallValue)rd.Close()}})()WithTimer(func() {for i : 0; i searchCount; i {rd2 : reader.NewSimpleReader(big.csv)_ rd2.Search(bigField, bigValue)rd2.Close()}})()
}0.03s elapsed
33.64s elapsed因为simpleReader没有持久化数据所以每次查询都得将数据重新加载后遍历对于大文件来说查询速度显著下降。
MapReader
相较于SimpleReaderMapReader增加了一个内存缓存
type MapReader struct {head []stringcache map[int][]string
}func NewMapReader(filename string) *MapReader {fp, _ : os.Open(filename)defer fp.Close()reader : csv.NewReader(fp)head, _ : reader.Read()cache : make(map[int][]string)index : 0for {record, err : reader.Read()if err io.EOF {break}if err ! nil {log.Fatal(err)}cache[index] recordindex}return MapReader{head: head, cache: cache}
}func (reader *MapReader) Search(field string, value string) []string {index : slices.Index(reader.head, field)for _, line : range reader.cache {if line[index] value {return line}}return nil
}func (reader *MapReader) Close() {reader.head nilreader.cache nil
}我们来测试一下它的1000次查询耗时
const (smallField field9smallValue value6_8bigField field9bigValue value99855_8searchCount 1000
)func main() {WithTimer(func() {rd : reader.NewMapReader(small.csv)for i : 0; i searchCount; i {rd.Search(smallField, smallValue)}rd.Close()})()WithTimer(func() {rd2 : reader.NewMapReader(big.csv)for i : 0; i searchCount; i {_ rd2.Search(bigField, bigValue)}rd2.Close()})()
}0.05s elapsed
3.04s elapsed可以看到在大文件的查询上速度显著变快这归功于运行运行内存的高速运转和redis一样在内存中遍历自然比不断从硬盘中取出数据再遍历快的多缺陷就是占用大量内存在小内存的机器上可能触发系统级别的“内存逐出”。
sqlReader
sqlReader则将数据存储到sqlite中
type SqliteReader struct {rd *csv.ReadersqliteHandler *sqlx.DB // Sql操作抓手lineLength int // 行长度head []string // 头信息
}const (sqlitePath csv.dbsqliteTableName test
)func NewSqliteReader(filename string) *SqliteReader {// 读权限打开csvfp, _ : os.Open(filename)defer fp.Close()reader : csv.NewReader(fp)// 增加csv reader容错性reader.LazyQuotes truereader.TrimLeadingSpace true// 读取头信息head, _ : reader.Read()// 打开sqlitedb, _ : sqlx.Open(sqlite3, sqlitePath)rd : SqliteReader{rd: reader,head: head,sqliteHandler: db,lineLength: len(head),}if err : rd.initDB(); err ! nil {log.Fatal(err)}fmt.Println(DB构建结束)return rd
}func (reader *SqliteReader) initDB() error {// 拼接建库语句fieldDefine : make([]string, reader.lineLength)for index, field : range reader.head {fieldDefine[index] fmt.Sprintf(%s TEXT, field)}fieldDefinedString : strings.Join(fieldDefine, ,)// 建立数据库var createTableSQL fmt.Sprintf(CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT,%s);, sqliteTableName, fieldDefinedString)fmt.Println(createTableSQL)_, err : reader.sqliteHandler.Exec(createTableSQL)if err ! nil {return err}// 拼接插入语句insertSQL : fmt.Sprintf(INSERT INTO %s VALUES (NULL,, sqliteTableName)for i : 0; i reader.lineLength; i {insertSQL ?,}insertSQL strings.TrimSuffix(insertSQL, ,) );// 预编译提速stmt, _ : reader.sqliteHandler.Prepare(insertSQL)// 使用事务插入数据tx, _ : reader.sqliteHandler.Begin()// 写入数据for {line, err : reader.rd.Read()if err io.EOF {break}if err ! nil {return err}// 转为接口类型values : make([]interface{}, reader.lineLength)for i, v : range line {values[i] v}// 插入sql_, err stmt.Exec(values...)if err ! nil {return err}}_ tx.Commit()return nil
}func (reader *SqliteReader) Search(field string, value string) []any {query : fmt.Sprintf(SELECT * FROM %s WHERE %s? LIMIT 1, sqliteTableName, field)// 查询row, _ : reader.sqliteHandler.Queryx(query, value)defer row.Close()// 读取结果for row.Next() {r, _ : row.SliceScan()return r}return nil
}func (reader *SqliteReader) Close() {_ reader.sqliteHandler.Close()
}我们来测试一下它的1000次查询耗时
func main() {//WithTimer(func() {// rd : reader.NewSqliteReader(small.csv)// for i : 0; i searchCount; i {// r : rd.Search(smallField, smallValue)// fmt.Println(r)// }// rd.Close()//})()WithTimer(func() {rd2 : reader.NewSqliteReader(big.csv)for i : 0; i searchCount; i {_ rd2.Search(bigField, bigValue)}rd2.Close()})()
}0.14s elapsed
143.41s elapsed总结
从上文测试结果来看sqlite优化后的查询效率甚至都不如遍历查询而缓存查询的效果虽然最好但最费内存尤其是在某些场景下可能机器只有区区1-2G根本跑不起来。
我们对sqlite进行优化 批量插入在大量数据写入的情况下通过事务和预编译语句批量插入数据避免频繁的磁盘 I/O。 tx, err : db.Begin()
stmt, err : tx.Prepare(INSERT INTO ...)
for _, row : range data {_, err : stmt.Exec(row[0], row[1])if err ! nil {log.Fatalf(Failed to execute statement: %v, err)}
}
err tx.Commit()配置性能优化在 Go 程序中通过 PRAGMA 配置 SQLite 参数以提升性能。 _, err db.Exec(PRAGMA synchronous OFF; -- 关闭同步机制PRAGMA journal_mode MEMORY; -- 将日志存储在内存中PRAGMA temp_store MEMORY; -- 临时表存储在内存中PRAGMA cache_size 100000; -- 设置较大的缓存PRAGMA locking_mode EXCLUSIVE;-- 独占锁模式
)
if err ! nil {log.Fatalf(Failed to set PRAGMA options: %v, err)
}参数解析 PRAGMA synchronous OFF 作用控制 SQLite 如何在写入数据时与磁盘同步影响事务的持久性和性能。 解释 OFF关闭同步机制。写入操作会更快但在断电或系统崩溃时可能会丢失一些数据因为数据尚未完全写入磁盘。NORMAL这是默认值SQLite 会确保数据在写入前与磁盘同步较安全但稍慢。FULL最安全的模式确保所有写入操作都被完全同步到磁盘性能最差。 适用场景如果对数据的可靠性要求不高可以选择 OFF以换取写入速度的提升。 PRAGMA journal_mode MEMORY 作用设置事务日志的存储方式影响数据库的事务处理效率和恢复能力。 解释 MEMORY将事务日志存储在内存中避免磁盘 I/O 操作通常在内存数据库或短时间操作时使用。WALWrite-Ahead Logging将日志存储在磁盘文件中适合多用户并发读写时使用。DELETE默认模式事务日志存储在磁盘文件中每次写入操作后会删除日志文件。TRUNCATE类似于 DELETE但不会删除整个日志文件而是将文件截断。 适用场景如果数据持久性不重要如仅进行短期数据操作可以选择 MEMORY 来提高性能。注意这种模式不适合数据库重启后恢复数据。 PRAGMA temp_store MEMORY 作用设置 SQLite 临时表的存储位置影响临时表的存储方式。 解释 MEMORY临时表存储在内存中性能较快因为避免了磁盘 I/O 操作。FILE临时表存储在磁盘中适用于大规模的数据操作或内存不足的情况。 适用场景如果数据库内存足够并且希望加速临时数据处理可以选择 MEMORY。但对于大数据量的临时表可能需要使用 FILE。 PRAGMA cache_size 100000 作用控制 SQLite 数据库的页面缓存大小影响数据库的 I/O 性能。 解释 该参数控制 SQLite 在内存中使用的页面数量。SQLite 在查询时会缓存数据页cache_size 越大数据库可以在内存中保持更多数据减少磁盘 I/O。默认情况下SQLite 使用的缓存大小为 2000 页每页的大小通常为 1024 字节。设置 cache_size 为 100000 时数据库可以缓存更多的数据适用于内存充足的情况。 适用场景如果你有足够的内存增加缓存大小可以提升查询性能。对于大数据量操作可以提高缓存命中率减少磁盘读取次数。 PRAGMA locking_mode EXCLUSIVE 作用设置数据库的锁定模式影响并发访问和事务的锁定方式。 解释 EXCLUSIVE独占锁模式表示一个事务在执行期间其他事务无法对数据库进行写操作。适用于写操作密集的场景能够减少冲突但会降低并发性。NORMAL默认锁模式允许多个事务同时读取但只允许一个事务写入。SHARED允许多个进程或线程共享读取操作但只允许一个进程或线程执行写操作。 适用场景如果你希望一个事务能够独占对数据库的写入操作避免其他写操作干扰可以使用 EXCLUSIVE。但如果系统有多个并发写操作可能会限制并发性能。 创建索引在大量查询时为频繁使用的查询条件列创建索引。创建索引后sqlite Reader的瓶颈仅仅就在构建数据库上了查询效率得到了大大提高。 _, err db.Exec(CREATE INDEX IF NOT EXISTS idx_col1 ON my_table (col1))
if err ! nil {log.Fatalf(Failed to create index: %v, err)
}使用内存模式如果数据量适中且无需长期存储可以将 SQLite 数据库存储在内存中以避免磁盘 I/O。这个方法不考虑 db, err : sql.Open(sqlite3, file::memory:?cacheshared)
if err ! nil {log.Fatalf(Failed to open memory database: %v, err)
}
defer db.Close()最后优化如下
手动操作事务防止每次都提交造成插入缓慢使用sqlx提供的In方法批量插入数据
func (reader *SqliteReader) initDB(handler *csv.Reader) (err error) {// 拼接建库语句fieldDefine : make([]string, reader.lineLength)for index, field : range reader.head {fieldDefine[index] fmt.Sprintf(%s TEXT, field)}fieldDefinedString : strings.Join(fieldDefine, ,)// 建立数据库var createTableSQL fmt.Sprintf(CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT,%s);, sqliteTableName, fieldDefinedString)csvLogger.Infof(create database [%s] : %s.\n, sqliteTableName, createTableSQL)_, err reader.sqliteHandler.Exec(createTableSQL)if err ! nil {return err}// 开启事务tx, err : reader.sqliteHandler.Begin()if err ! nil {return err}// 此处不能用内部声明的err,因此defer关键词会把此时的快照入栈// 此时err必然不为空,因此需要声明返回值的err// golang执行顺序// 1. returned_value return 此处是err// 2. defer出栈// 3. 返回returned_value 此处是errdefer func() {if err ! nil {_ tx.Rollback()} else {_ tx.Commit()}}()// 拼接插入语句bulkSize, currentSize : 1000, 0values : make([]any, 0, bulkSize)for {line, err : handler.Read()if err io.EOF {break}if err ! nil {return err}// 追加批量插入的值values append(values, line)// 计数自增currentSize// 判断是否需要批量新增if currentSize bulkSize {condition, args, err : sqlx.In(fmt.Sprintf(INSERT INTO %s (%s) VALUES %s,sqliteTableName,strings.Join(reader.head, ,),strings.Repeat((?),, currentSize),),values...,)if err ! nil {csvLogger.Errorln(err)return err}// 构建正确sql语句condition strings.TrimSuffix(condition, ,) ;if _, err reader.sqliteHandler.Exec(condition, args...); err ! nil {return err}// 归零计数器currentSize 0// 清空valuevalues make([]any, 0, bulkSize)}}// 判断漏网之鱼if currentSize 0 {condition, args, err : sqlx.In(fmt.Sprintf(INSERT INTO %s (%s) VALUES %s,sqliteTableName,strings.Join(reader.head, ,),strings.Repeat((?),, currentSize),),values...,)if err ! nil {csvLogger.Errorln(err)return err}// 构建正确sql语句condition strings.TrimSuffix(condition, ,) ;if _, err reader.sqliteHandler.Exec(condition, args...); err ! nil {return err}// 清空valuevalues nil}return nil
}然后根据我的场景增大查询次数到10W我数据量准备了10W左右的我的场景是数量等于查询次数
CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT,field1 TEXT,field2 TEXT,field3 TEXT,field4 TEXT,field5 TEXT,field6 TEXT,field7 TEXT,field8 TEXT,field9 TEXT,field10 TEXT);
DB构建结束
2.28s elapsed可以看到查询效果非常不错提速了百倍有余而遍历查询此时因为太久不出结果被我CtrlC掉了。。。
计算一下二者查询速率的决定因素假设查询次数为n单次查询时间为t
遍历查询 n * t(遍历)sqlite查询 建库建表t0 批量插入数据t1 创建索引t2 n * t(sqlite)t(sqlite) t(遍历
轶闻趣事SQLite 背后的故事
SQLite 现在已经是全球用户最多的数据库产品。它非常小巧以及单文件无单独操作系统进程就像病毒一样依附在宿主程序的进程里运行。你看不到它但它却无处不在。汽车手机浏览器以及各类 app 里都能见到 .db 结尾的 SQLite 数据库文件。 假如 SQLite 出现重大 bug或者像平常的数据库那样无法连接整个地球都会乱套。你身边用的几乎所有电子产品手机电脑iPad笔记本和嵌入式设备全部都会出问题。它的诞生到大范围全球流行的过程和一般软件有着不太一样的发展历程。
SQLite 诞生的契机就是典型的程序员开发的故事剧本。作者 Richard 最开始在一艘军舰上做 contractor就是我们说的外包。他们程序跑在军舰安装的电脑上电脑上装的是 informix。Richard 的工作就是把 informix 的数据拿出来进行计算然后展示到电脑屏幕上这和我们今天的 CRUD 工作类似。比较令人恼火的是 informix 很不稳定经常崩溃连不上。部队里的铁拳长官可不懂啥 TCP/IP 或者数据库系统知识。他们只看到软件的报错 dialog对话框 经常弹出来而这个 dialog 又是 SQLite 的作者 Richard 写的软件画出来的锅自然从天而降。于是 Richard 决定自己从头写一个无需外部连接的数据库来解决这个问题。
…文章来源https://liyafu.com/2022-07-31-sqlite-untold-story/
