rust语言（1.88.0）sqlite数据库rusqlite库（0.37.0）学习笔记

一、rusqlite库

# cargo add rusqlite
rusqlite = { version = "0.37.0", features = ["bundled"] }

1、基本代码

use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    // 连接到SQLite数据库（如果不存在，则创建）
    let conn = Connection::open("test.db")?;

    // 创建表
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        )",
        [],
    )?;

    // 插入数据到表
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["小明", "alice@example.com"],
    )?;

    // 查询
    let mut stmt = conn.prepare("SELECT id, name, email FROM users")?;
    let user_iter = stmt.query_map([], |row| {
        Ok((
            row.get::<_, i32>(0)?,
            row.get::<_, String>(1)?,
            row.get::<_, String>(2)?,
        ))
    })?;

    // 显示查询结果
    for user in user_iter {
        println!("{:?}", user?);
    }

    Ok(())
}

2、结构体获取查询结果

• 接收数据时，不用显示声明类型

#[derive(Debug)]
struct Person{
    id: i32,
    name: String,
    email: String,
}

// 查询数据
let mut stmt = conn.prepare("SELECT id, name, email FROM users")?;
let user_iter = stmt.query_map([], |row| {
    Ok(Person{
        id:row.get(0)?,
        name:row.get(1)?,
        email:row.get(2)?,}

    )
})?;

// 显示查询结果
for user in user_iter {
    println!("{:?}", user?);
}

3、修改数据

conn.execute(
    "update users set name = ? where id = ?",
    params!["小刘", 4],
)?;

4、删除数据

conn.execute(
    "delete from users where name = ?",
    params!["小李"],
)?;

5、事务处理

// 创建事务
let tx = conn.transaction()?;
tx.execute("INSERT INTO users (name, email) VALUES (?1, ?2)",["小王","ssaleice@example.com"])?;
tx.execute("INSERT INTO users (name, email) VALUES (?1, ?2)",["小白","arrlice@example.com"])?;
// 提交事务
tx.commit()?;

6、注意事项

1‌、?1与?的区别‌

• ?1和?2是‌命名参数占位符‌，数字表示参数的索引位置（从1开始），需严格按顺序绑定参数。例如：

conn.execute("INSERT INTO users (name, email) VALUES (?1, ?2)", ["Alice", "alice@example.com"])?;

​ 这里?1对应"Alice"，?2对应"alice@example.com"。

• 单独的?是‌匿名参数占位符，按出现顺序自动匹配参数列表。例如：

conn.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"])?;

第一个?绑定"Bob"，第二个绑定"bob@example.com"。

• ‌区别‌：命名参数占位符更明确，适合复杂查询；匿名占位符更简洁，适合简单场景。

2‌、&["小王", ...]与不加&的区别‌

• 不加&时，参数是Vec或数组的‌所有权传递‌，会消耗原始数据。

• 加&表示传递‌切片引用（&[&dyn ToSql]），避免所有权转移，适合复用参数或临时数据。例如：

  let params = ["小王", "ssaleice@example.com"];
  conn.execute("INSERT ... VALUES (?, ?)", ¶ms)?; // 引用传递
  

3、params![]宏的作用‌

• params![]是rusqlite提供的宏，用于‌类型安全的参数绑定‌，自动将值转换为&dyn ToSql类。例如：

  conn.execute("INSERT ... VALUES (?, ?)", params!["小王", "ssaleice@example.com"])?;
  

• ‌优势‌：

  • 编译时检查参数类型，避免运行时错误。
  • 支持混合类型（如字符串、整数、浮点数等）。
  • 语法更简洁，无需手动构造数组或切片。

‌总结‌：

• 命名占位符（?1）提高可读性，匿名占位符（?）简化代码。
• 加&避免所有权转移，适合临时数据；不加则直接传递所有权。
• params![]宏增强类型安全，推荐优先使用。

二、sqlite 相关sql

1、插入数据冲突解决算法

1. ‌ROLLBACK‌：发生冲突时立即回滚整个事务，中止命令并返回错误
2. ‌ABORT‌（默认）：撤销当前语句的更改并返回错误，但保留之前语句的修改
3. ‌FAIL‌：中止当前语句但保留已执行的修改，仅返回错误
4. ‌IGNORE‌：静默忽略冲突行，继续执行后续操作
5. ‌REPLACE‌：删除冲突行后插入新行（注意会改变自增ID）

2、如果数据不存在则插入INSERT OR IGNORE

• 必须定义定义主键PRIMARY KEY或唯一索引UNIQUE，来限定数据。

• 如果数据不存在则插入，数据存在则忽略。

INSERT OR IGNORE INTO table_name (column1, column2) VALUES (?, ?)

3、如果数据存在则删除后插入INSERT OR REPLACE

• 必须定义定义主键PRIMARY KEY或唯一索引UNIQUE，来限定数据。
• 当违反唯一性约束时，先删除已存在的记录再插入新记录。会导致自增主键的值变化（原记录被删除，新记录获得新ID）。
• 不是真正的"更新"操作，而是删除+插入

INSERT OR REPLACE INTO table_name (column1, column2) VALUES (?, ?)

4、ON CONFLICT子句

• SQLite 3.24.0+

INSERT INTO 表名 (列名) VALUES (值) 
ON CONFLICT(冲突列) DO 处理动作;

• 忽略重复输入，等同于INSERT OR IGNORE

INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT(id) DO NOTHING;   

• 冲突时更新部分字段

INSERT INTO products (sku, name, price) 
VALUES ('X123', 'Widget', 9.99)
ON CONFLICT(sku) DO UPDATE SET 
    price = excluded.price,
    updated_at = CURRENT_TIMESTAMP;

5、UPSERT

• SQLite 3.35.0+

INSERT INTO 表名 (列名) VALUES (值)
[ON CONFLICT(冲突列) DO UPDATE SET 列=值 [WHERE 条件]]
[ON CONFLICT(冲突列) DO NOTHING];

6、日期时间

7、默认自动添加日期时间

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    token TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

8、插入日期时间

INSERT INTO sessions (id, token, created_at) 
VALUES (123, 'abc123', CURRENT_TIMESTAMP);

9、更新记录时‌自动更新时间

• 配合触发器自动更新时间戳

-- 创建名为update_timestamp的触发器，不存在时才创建
CREATE TRIGGER IF NOT EXISTS update_timestamp
-- 触发器将在sessions表发生更新后触发
AFTER UPDATE ON sessions
-- 每行更新都触发执行
FOR EACH ROW
-- 开始触发器逻辑块
BEGIN
    -- 更新当前被修改记录的updated_at字段为当前时间戳
    -- OLD.id引用被更新记录的原始ID值
    UPDATE sessions SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
-- 结束触发器逻辑块
END;

10、**时区处理（本地时间）**‌

• 如需本地时间，可在应用层转换或使用SQLite的datetime函数调整

-- 直接显示本地当前时间
SELECT datetime(CURRENT_TIMESTAMP, 'localtime');

-- 查询结果改为本地时间
SELECT datetime(created_at, 'localtime') FROM user_actions;