🦀 第23课：数据库操作

实战项目第23/25课

Rust通过SQLx、Diesel等库支持各种数据库。本课将学习数据库操作的核心模式，使用SQLite作为示例。

🗃️ 数据库操作模式

use std::collections::HashMap;

// 模拟数据库操作（无需真实数据库）
#[derive(Debug, Clone)]
struct User {
    id: u64,
    name: String,
    email: String,
    age: u32,
}

struct Database {
    users: HashMap<u64, User>,
    next_id: u64,
}

impl Database {
    fn new() -> Self { Database { users: HashMap::new(), next_id: 1 } }
    
    // CREATE
    fn insert_user(&mut self, name: &str, email: &str, age: u32) -> u64 {
        let id = self.next_id;
        self.next_id += 1;
        self.users.insert(id, User { id, name: name.into(), email: email.into(), age });
        id
    }
    
    // READ
    fn get_user(&self, id: u64) -> Option<&User> { self.users.get(&id) }
    
    fn find_by_name(&self, name: &str) -> Vec<&User> {
        self.users.values().filter(|u| u.name.contains(name)).collect()
    }
    
    fn find_by_age_range(&self, min: u32, max: u32) -> Vec<&User> {
        self.users.values().filter(|u| u.age >= min && u.age <= max).collect()
    }
    
    // UPDATE
    fn update_age(&mut self, id: u64, new_age: u32) -> bool {
        if let Some(user) = self.users.get_mut(&id) {
            user.age = new_age;
            true
        } else { false }
    }
    
    // DELETE
    fn delete_user(&mut self, id: u64) -> bool { self.users.remove(&id).is_some() }
    
    // AGGREGATE
    fn count(&self) -> usize { self.users.len() }
    fn average_age(&self) -> f64 {
        if self.users.is_empty() { return 0.0; }
        self.users.values().map(|u| u.age as f64).sum::() / self.users.len() as f64
    }
}

fn main() {
    let mut db = Database::new();
    
    // 插入
    let id1 = db.insert_user("Alice", "alice@rust.lang", 28);
    let id2 = db.insert_user("Bob", "bob@rust.lang", 35);
    let id3 = db.insert_user("Charlie", "charlie@rust.lang", 22);
    let id4 = db.insert_user("David", "david@rust.lang", 40);
    
    // 查询
    if let Some(user) = db.get_user(id1) {
        println!("查询ID {}: {:?}", id1, user);
    }
    
    // 条件查询
    println!("\n年龄25-35:");
    for u in db.find_by_age_range(25, 35) {
        println!("  {} ({}岁)", u.name, u.age);
    }
    
    // 更新
    db.update_age(id1, 29);
    println!("\nAlice新年龄: {}", db.get_user(id1).unwrap().age);
    
    // 统计
    println!("\n用户数: {}", db.count());
    println!("平均年龄: {:.1}", db.average_age());
    
    // 删除
    db.delete_user(id3);
    println!("删除后用户数: {}", db.count());
}

查询ID 1: User { id: 1, name: "Alice", email: "alice@rust.lang", age: 28 } 年龄25-35: Alice (28岁) Bob (35岁) Alice新年龄: 29 用户数: 4 平均年龄: 31.2 删除后用户数: 3

✅ 验证通过

📝 练习

练习1：SQLx实战

使用SQLx连接SQLite，实现用户表的CRUD操作。

练习2：迁移系统

实现简单的数据库迁移系统：版本管理、up/down迁移。

🏆 本课成就

✅ 掌握CRUD操作模式
✅ 学会条件查询和聚合
✅ 理解ORM vs 原生SQL的选择
�>实现了内存数据库

🔧 数据库事务

use std::collections::HashMap;

#[derive(Debug, Clone)]
struct User { id: u64, name: String, age: u32 }

struct Database { users: HashMap, next_id: u64 }
impl Database {
    fn new() -> Self { Database { users: HashMap::new(), next_id: 1 } }
    fn insert(&mut self, name: &str, age: u32) -> u64 {
        let id = self.next_id; self.next_id += 1;
        self.users.insert(id, User { id, name: name.into(), age }); id
    }
    fn update_age(&mut self, id: u64, age: u32) -> Result<(),()> {
        self.users.get_mut(&id).ok_or(())?.age = age; Ok(())
    }
}

struct Transaction<'a> { db: &'a mut Database, backup: Vec<(u64, User)>, committed: bool }
impl<'a> Transaction<'a> {
    fn new(db: &'a mut Database) -> Self { Transaction { db, backup: vec![], committed: false } }
    fn update_age(&mut self, id: u64, age: u32) -> Result<(),()> {
        if let Some(u) = self.db.users.get(&id) { self.backup.push((id, u.clone())); }
        self.db.update_age(id, age)
    }
    fn commit(mut self) { self.committed = true; }
}
impl<'a> Drop for Transaction<'a> {
    fn drop(&mut self) {
        if !self.committed { for (id, u) in self.backup.drain(..) { self.db.users.insert(id, u); } }
    }
}

fn main() {
    let mut db = Database::new();
    db.insert("Alice", 25); db.insert("Bob", 30);
    { let mut tx = Transaction::new(&mut db); tx.update_age(1, 26).unwrap(); tx.commit(); }
    { let mut tx = Transaction::new(&mut db); tx.update_age(2, 99).unwrap(); /* no commit → rollback */ }
    println!("Alice: {} Bob: {}", db.users.get(&1).unwrap().age, db.users.get(&2).unwrap().age);
}

Alice: 26 Bob: 30

✅ 验证通过

📊 数据库选型指南

fn main() {
    println!("Rust数据库生态:");
    println!();
    println!("SQL数据库:");
    println!("  SQLite  - sqlx/diesel/rusqlite");
    println!("  PostgreSQL - sqlx/diesel/tokio-postgres");
    println!("  MySQL   - sqlx/diesel");
    println!();
    println!("NoSQL数据库:");
    println!("  Redis   - redis/deadpool-redis");
    println!("  MongoDB - mongodb");
    println!("  DynamoDB - aws-sdk-dynamodb");
    println!();
    println!("ORM vs 原生SQL:");
    println!("  Diesel   - 编译期SQL检查, 强类型");
    println!("  SQLx     - 异步, 编译期SQL验证");
    println!("  SeaORM   - 异步ORM, 动态查询");
    println!();
    println!("连接池:");
    println!("  deadpool - 简单连接池");
    println!("  bb8      - 异步连接池");
    println!("  r2d2     - 同步连接池");
    println!();
    println!("迁移工具:");
    println!("  sqlx-cli - SQLx迁移");
    println!("  diesel_cli - Diesel迁移");
    println!("  refinery - 独立迁移工具");
}

🏗️ 综合实战：ORM模拟器

use std::collections::HashMap;

#[derive(Debug, Clone)]
struct Product { id: u64, name: String, price: f64, stock: u32, category: String }

struct ProductDb { products: HashMap, next_id: u64 }
impl ProductDb {
    fn new() -> Self { ProductDb { products: HashMap::new(), next_id: 1 } }
    fn insert(&mut self, name: &str, price: f64, stock: u32, category: &str) -> u64 {
        let id = self.next_id; self.next_id += 1;
        self.products.insert(id, Product { id, name: name.into(), price, stock, category: category.into() }); id
    }
    fn find_by_category(&self, cat: &str) -> Vec<&Product> {
        self.products.values().filter(|p| p.category == cat).collect()
    }
    fn find_in_price_range(&self, min: f64, max: f64) -> Vec<&Product> {
        self.products.values().filter(|p| p.price >= min && p.price <= max).collect()
    }
    fn total_stock_value(&self) -> f64 {
        self.products.values().map(|p| p.price * p.stock as f64).sum()
    }
    fn category_summary(&self) -> HashMap<&str, (usize, f64)> {
        let mut map: HashMap<&str, (usize, f64)> = HashMap::new();
        for p in self.products.values() {
            let e = map.entry(&p.category).or_insert((0, 0.0));
            e.0 += 1; e.1 += p.price * p.stock as f64;
        }
        map
    }
}

fn main() {
    let mut db = ProductDb::new();
    db.insert("Rust编程", 89.0, 100, "书籍");
    db.insert("机械键盘", 599.0, 50, "外设");
    db.insert("显示器", 2999.0, 20, "外设");
    db.insert("Python入门", 59.0, 200, "书籍");
    db.insert("鼠标垫", 49.0, 500, "配件");
    
    println!("📚 书籍类:");
    for p in db.find_by_category("书籍") { println!("  {} ¥{}", p.name, p.price); }
    
    println!("
💰 100-600元商品:");
    for p in db.find_in_price_range(100.0, 600.0) { println!("  {} ¥{}", p.name, p.price); }
    
    println!("
📊 库存总价值: ¥{:.2}", db.total_stock_value());
    println!("分类统计: {:?}", db.category_summary());
}

📚 书籍类: Rust编程 ¥89 Python入门 ¥59 💰 100-600元商品: 机械键盘 ¥599 📊 库存总价值: ¥111050.00

✅ 验证通过

📋 数据库操作清单

数据库最佳实践

✅ 使用连接池管理连接
✅ 事务保证ACID
✅ 预编译语句防SQL注入
✅ 迁移管理数据库版本
✅ 索引优化查询性能
✅ 备份与恢复策略

🔧 SQLx完整示例

// Cargo.toml:
// sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "sqlite"] }
// 
// 迁移:
// sqlx migrate add create_users_table
// sqlx migrate run
//
// 编译期SQL检查:
// let user = sqlx::query_as!(
//     User,
//     "SELECT id, name, email FROM users WHERE id = $1",
//     user_id
// ).fetch_one(&pool).await?;

fn main() {
    println!("SQLx核心特性:");
    println!("  ✅ 编译期SQL验证");
    println!("  ✅ 异步API");
    println!("  ✅ 连接池(deadpool/bb8)");
    println!("  ✅ 迁移系统");
    println!("  ✅ 多数据库支持");
    
    println!("
Diesel vs SQLx:");
    println!("  Diesel: 同步, 强类型ORM, 编译期检查");
    println!("  SQLx:   异步, 原生SQL, 编译期检查");
    println!("  SeaORM: 异步, 动态查询ORM");
    
    println!("
连接字符串示例:");
    println!("  SQLite:   sqlite://database.db");
    println!("  Postgres: postgres://user:pass@localhost/db");
    println!("  MySQL:    mysql://user:pass@localhost/db");
}

🏗️ 数据库实战：查询构建器

use std::collections::HashMap;

struct QueryBuilder {
    table: String,
    select_cols: Vec,
    where_clauses: Vec,
    order_by: Option,
    limit: Option,
    params: HashMap,
}

impl QueryBuilder {
    fn new(table: &str) -> Self { QueryBuilder { table: table.into(), select_cols: vec!["*".into()], where_clauses: Vec::new(), order_by: None, limit: None, params: HashMap::new() } }
    fn select(mut self, cols: &[&str]) -> Self { self.select_cols = cols.iter().map(|s| s.to_string()).collect(); self }
    fn where_eq(mut self, col: &str, val: &str) -> Self { 
        let key = format!(":{}", col);
        self.where_clauses.push(format!("{} = {}", col, key));
        self.params.insert(key, val.to_string());
        self
    }
    fn where_gt(mut self, col: &str, val: &str) -> Self {
        let key = format!(":{}_gt", col);
        self.where_clauses.push(format!("{} > {}", col, key));
        self.params.insert(key, val.to_string());
        self
    }
    fn order_by(mut self, col: &str, desc: bool) -> Self { self.order_by = Some(format!("{} {}", col, if desc {"DESC"} else {"ASC"})); self }
    fn limit(mut self, n: usize) -> Self { self.limit = Some(n); self }
    fn build(&self) -> String {
        let mut sql = format!("SELECT {} FROM {}", self.select_cols.join(", "), self.table);
        if !self.where_clauses.is_empty() { sql.push_str(&format!(" WHERE {}", self.where_clauses.join(" AND "))); }
        if let Some(ref order) = self.order_by { sql.push_str(&format!(" ORDER BY {}", order)); }
        if let Some(n) = self.limit { sql.push_str(&format!(" LIMIT {}", n)); }
        sql
    }
    fn params(&self) -> &HashMap { &self.params }
}

fn main() {
    let q1 = QueryBuilder::new("users").where_eq("active", "true").order_by("created_at", true).limit(10);
    println!("SQL: {}", q1.build());
    println!("参数: {:?}", q1.params());
    
    let q2 = QueryBuilder::new("products").select(&["name", "price"]).where_gt("price", "100").where_eq("category", "electronics").order_by("price", false);
    println!("
SQL: {}", q2.build());
    println!("参数: {:?}", q2.params());
}

SQL: SELECT * FROM users WHERE active = :active ORDER BY created_at DESC LIMIT 10 参数: {":active": "true"} SQL: SELECT name, price FROM products WHERE price > :price_gt AND category = :category ORDER BY price ASC 参数: {":price_gt": "100", ":category": "electronics"}

✅ 验证通过

← 上一课 📖 目录下一课 →