1.13使用 Node.js 操作 SQLite
SQLite 是一个轻量级的嵌入式关系型数据库，适合小型项目、原型开发和本地数据存储。在 Node.js 中使用 SQLite 非常方便，常用的第三方库是 sqlite3。
一、安装依赖
首先，在项目中安装 sqlite3：
npm init -y npm install sqlite3
二、创建并连接数据库
创建一个 db.js 文件：
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./mydb.sqlite', (err) => {if (err) {console.error(err.message);}console.log('Connected to the mydb SQlite database.');
});这段代码首先导入了 sqlite3 模块，并设置了为 verbose 模式以便于调试。然后创建了一个新的 SQLite 数据库连接，如果指定的数据库文件不存在，SQLite 将自动创建一个新的。
三、创建表
db.run(`CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE
)`, function(err) {if (err) {console.error(err.message);}console.log('Table created');
});这里我们创建了一个名为 users 的表，包含三个字段：id、name 和 email。
四、插入/更新/删除 数据
let sql = `INSERT INTO users(name, email) VALUES(?, ?)`;
db.run(sql, ["John Doe", "johndoe@example.com"], function(err) {if (err) {console.error(err.message);}console.log(`A new row has been inserted with rowid ${this.lastID}`);
});let updateSql = `UPDATE users SET email = ? WHERE name = ?`;
db.run(updateSql, ["newemail@example.com", "John Doe"], function(err) {if (err) {console.error(err.message);}console.log(`Row(s) updated: ${this.changes}`);
});let deleteSql = `DELETE FROM users WHERE name = ?`;
db.run(deleteSql, ["John Doe"], function(err) {if (err) {console.error(err.message);}console.log(`Row(s) deleted: ${this.changes}`);
});五、查询数据
查询数据库中的数据可以使用 db.all() 方法：
db.all(`SELECT * FROM users`, [], (err, rows) => {if (err) {throw err;}rows.forEach((row) => {console.log(row.id, row.name, row.email);});
});分页查询的基本原理
分页查询主要依靠两个 SQL 关键字：
- LIMIT：限制返回的记录数。
- OFFSET：跳过指定数量的记录。
-- 第 1 页（每页 10 条）
SELECT * FROM users LIMIT 10 OFFSET 0;-- 第 2 页
SELECT * FROM users LIMIT 10 OFFSET 10;-- 第 3 页
SELECT * FROM users LIMIT 10 OFFSET 20;
基础实现方法
使用 db.all() 方法
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./mydb.sqlite');// 分页参数
const page = 1;
const pageSize = 10;
const offset = (page - 1) * pageSize;// 分页查询
db.all('SELECT * FROM users LIMIT ? OFFSET ?',[pageSize, offset],(err, rows) => {if (err) {console.error('查询失败:', err.message);} else {console.log('查询结果:', rows);}}
);
计算总页数
// 查询总记录数
db.get('SELECT COUNT(*) as count FROM users', (err, result) => {if (err) {console.error('查询总记录数失败:', err.message);} else {const totalCount = result.count;const totalPages = Math.ceil(totalCount / pageSize);console.log(`总记录数: ${totalCount}, 总页数: ${totalPages}`);}
});db.all 和 db.get 是两个常用的查询方法，但它们的功能和用途有明显区别：
1. 返回结果数量
-
db.get：
返回查询结果的第一条记录（单个对象）。如果没有匹配的记录，则返回null。 -
db.all：
返回查询结果的所有记录（数组）。如果没有匹配的记录，则返回空数组[]。
2. 回调参数
-
db.get：
回调函数接收(err, row)，其中row是单个对象。 -
db.all：
回调函数接收(err, rows)，其中rows是数组。
3. db.get 的适用场景
- 查询唯一记录（如通过 ID 查询用户）。
- 聚合查询（如
COUNT,SUM,AVG）。 - 只需获取第一条记录的场景。
// 查询单个用户
db.get('SELECT * FROM users WHERE id = ?', [1], (err, row) => {if (err) {console.error(err);} else {console.log(row); // 返回单个用户对象或 null}
});// 聚合查询
db.get('SELECT COUNT(*) as total FROM users', (err, row) => {console.log(row.total); // 返回总数
});
4. db.all 的适用场景
- 查询多条记录（如获取所有用户）。
- 分页查询（结合
LIMIT和OFFSET）。 - 需要遍历所有结果的场景。
// 查询所有用户
db.all('SELECT * FROM users', (err, rows) => {if (err) {console.error(err);} else {console.log(rows); // 返回用户数组rows.forEach(user => {console.log(user.name);});}
});// 分页查询
db.all('SELECT * FROM users LIMIT ? OFFSET ?', [10, 0], (err, rows) => {// 返回第一页的 10 条记录
});db.serialize() 是一个重要的方法，用于控制 SQL 语句的执行顺序
1. SQLite 的并发特性
- SQLite 是文件型数据库，同一时间只能有一个写操作。
- 多个读操作可以并行，但写操作会阻塞所有其他操作。
2. db.serialize() 的作用
- 确保同一连接上的 SQL 语句按顺序执行。
- 防止并发执行导致的数据不一致问题。
db.serialize(() => {// 这里的 SQL 语句会按顺序执行db.run('SQL 语句 1');db.run('SQL 语句 2');// ...
});
db.serialize(() => {// 创建表db.run('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)');// 插入数据db.run('INSERT INTO users (name) VALUES (?)', ['张三']);// 查询数据db.get('SELECT * FROM users WHERE id = 1', (err, row) => {console.log(row); // 可以确保数据已插入});
});
事务需要顺序执行
db.serialize(() => {// 开始事务db.run('BEGIN TRANSACTION');// 执行多个操作db.run('INSERT INTO users (name) VALUES (?)', ['李四']);db.run('UPDATE accounts SET balance = balance - 100 WHERE user_id = 1');// 提交事务db.run('COMMIT', (err) => {if (err) {db.run('ROLLBACK'); // 出错时回滚}});
});
- 事务中的 SQL 必须按顺序执行，否则可能导致数据不一致。
- 例如，
COMMIT必须在所有操作完成后执行。
六、关闭数据库连接
db.close((err) => {if (err) {console.error(err.message);}console.log('Close the database connection.');
});