Better-SQLite3 参数绑定详解

在使用 better-sqlite3 进行数据库操作时，参数绑定是一个非常重要的概念。它不仅提高了代码的可读性和安全性，还能有效防止 SQL 注入攻击。本文将详细介绍如何在 better-sqlite3 中使用匿名参数和命名参数，并展示一些实际应用示例。

匿名参数

匿名参数使用问号（?）作为占位符，并通过位置来绑定参数值。你可以直接传递参数值，或者将它们放在数组中传递。

示例代码

const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');// 创建表
db.exec(`CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT,first_name TEXT NOT NULL,last_name TEXT NOT NULL,age INTEGER NOT NULL)
`);// 准备插入语句
const stmt = db.prepare('INSERT INTO people (first_name, last_name, age) VALUES (?, ?, ?)');// 插入数据 - 直接传递参数
stmt.run('John', 'Smith', 45);
console.log('Inserted John Smith.');// 插入数据 - 使用数组传递参数
stmt.run(['Jane', 'Doe', 30]);
console.log('Inserted Jane Doe.');// 插入数据 - 混合方式传递参数
stmt.run(['Alice'], ['Brown', 28]);
console.log('Inserted Alice Brown.');// 释放资源
stmt.finalize();// 查询所有记录以验证插入结果
const selectStmt = db.prepare("SELECT * FROM people");
const rows = selectStmt.all();
rows.forEach((row) => {console.log(`ID: ${row.id}, First Name: ${row.first_name}, Last Name: ${row.last_name}, Age: ${row.age}`);
});
selectStmt.finalize();// 关闭数据库连接
db.close();
console.log('Database connection closed.');

在这个示例中，我们创建了一个 people 表，并使用匿名参数插入了几条记录。可以看到，无论是直接传递参数还是通过数组传递参数，都可以实现同样的效果。

命名参数

命名参数允许你为每个参数指定一个名称，这样可以更清晰地标识每个参数的作用。SQLite 支持三种命名参数语法：@foo、:foo 和 $foo，这些都受 better-sqlite3 支持。

示例代码

const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');// 创建表
db.exec(`CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT,first_name TEXT NOT NULL,last_name TEXT NOT NULL,age INTEGER NOT NULL)
`);// 准备插入语句 - 使用 @ 符号命名参数
const stmt1 = db.prepare('INSERT INTO people (first_name, last_name, age) VALUES (@firstName, @lastName, @age)');
stmt1.run({firstName: 'John',lastName: 'Smith',age: 45
});
console.log('Inserted John Smith using @ notation.');// 准备插入语句 - 使用 : 符号命名参数
const stmt2 = db.prepare('INSERT INTO people (first_name, last_name, age) VALUES (:firstName, :lastName, :age)');
stmt2.run({firstName: 'Jane',lastName: 'Doe',age: 30
});
console.log('Inserted Jane Doe using : notation.');// 准备插入语句 - 使用 $ 符号命名参数
const stmt3 = db.prepare('INSERT INTO people (first_name, last_name, age) VALUES ($firstName, $lastName, $age)');
stmt3.run({firstName: 'Alice',lastName: 'Brown',age: 28
});
console.log('Inserted Alice Brown using $ notation.');// 查询所有记录以验证插入结果
const selectStmt = db.prepare("SELECT * FROM people");
const rows = selectStmt.all();
rows.forEach((row) => {console.log(`ID: ${row.id}, First Name: ${row.first_name}, Last Name: ${row.last_name}, Age: ${row.age}`);
});// 关闭数据库连接
db.close();
console.log('Database connection closed.');

在这个示例中，我们展示了如何使用不同符号的命名参数插入数据。无论使用哪种符号，都能达到相同的效果。

混合匿名参数与命名参数

你可以在同一个 SQL 语句中混合使用匿名参数和命名参数。在这种情况下，匿名参数按顺序绑定，而命名参数通过对象传递。

示例代码

const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');// 创建表
db.exec(`CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER NOT NULL)
`);// 准备插入语句 - 混合使用匿名参数和命名参数
const stmt = db.prepare('INSERT INTO people (name, age) VALUES (@name, ?)');// 插入数据 - 先传递匿名参数，再传递命名参数
stmt.run(30, { name: 'Henry' });
console.log('Inserted Henry with age 30.');// 查询所有记录以验证插入结果
const selectStmt = db.prepare("SELECT * FROM people");
const rows = selectStmt.all();
rows.forEach((row) => {console.log(`ID: ${row.id}, Name: ${row.name}, Age: ${row.age}`);
});// 关闭数据库连接
db.close();
console.log('Database connection closed.');

在这个示例中，我们展示了如何在一个 SQL 语句中同时使用匿名参数和命名参数。

数据类型转换

better-sqlite3 在 JavaScript 和 SQLite 之间自动进行数据类型的转换，具体如下：

SQLite	JavaScript
NULL	null
REAL	number
INTEGER	number or BigInt
TEXT	string
BLOB	Buffer

示例代码

以下是一个展示不同类型数据转换的示例：

const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');// 创建表
db.exec(`CREATE TABLE IF NOT EXISTS data_types (id INTEGER PRIMARY KEY AUTOINCREMENT,real_value REAL,integer_value INTEGER,text_value TEXT,blob_value BLOB)
`);// 准备插入语句
const insertStmt = db.prepare('INSERT INTO data_types (real_value, integer_value, text_value, blob_value) VALUES (?, ?, ?, ?)');// 插入不同类型的值
insertStmt.run(3.14, // REAL42,   // INTEGER'Hello, World!', // TEXTBuffer.from('Binary Data') // BLOB
);// 释放资源
insertStmt.finalize();// 查询所有记录以验证插入结果
const selectStmt = db.prepare("SELECT * FROM data_types");
const rows = selectStmt.all();
rows.forEach((row) => {console.log(`ID: ${row.id}, Real Value: ${row.real_value}, Integer Value: ${row.integer_value}, Text Value: ${row.text_value}, Blob Value: ${row.blob_value.toString()}`);
});// 关闭数据库连接
db.close();
console.log('Database connection closed.');