操作 MySQL 数据库

测试数据表结构

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

安装 mysql 工具包

[dependencies]
mysql = "25.0.1"

操作步骤

1. 创建 mysql 连接池
2. 获取连接
3. 执行 sql 命令

在代码中引入 mysql 模块，并创建与 MySQL 数据库的连接。你需要提供数据库的 URL，包括用户名、密码、主机地址和数据库名称等信息。

use mysql::Pool;
use mysql::OptsBuilder;
use mysql::prelude::*;

fn main() {
    // 初始化连接池
    let opts = OptsBuilder::new()
        .ip_or_hostname(Some("localhost"))
        .tcp_port(3306)
        .user(Some("root"))
        .pass(Some("root"))
        .db_name(Some("test"));
    let pool: Result<Pool, mysql::Error> = Pool::new(opts);
    let mut conn;
    if let Ok(p) = pool {
        conn = p.get_conn().unwrap();
    } else {
        panic!("Failed to connect to MySQL.");
    }

    // 插入数据
    let name = "test ' or 1 = 1";
    let email = "alice@example.com";
    let insert_query = "INSERT INTO users (name, email) VALUES (?, ?)";
    let params: (&str, &str) = (name, email);
    match conn.exec_drop(insert_query, params) {
        Ok(_) => println!("Data inserted successfully."),
        Err(e) => println!("Failed to insert data: {}", e),
    }

    // 查询数据
    // {
    //     let select_query = "SELECT id, name, email FROM users";
    //     let mut results: mysql::QueryResult<'_, '_, '_, mysql::Text> = conn.query_iter(select_query).unwrap();

    //     while let Some(row) = results.next() {
    //         let row = row.unwrap();
    //         let (id, name, email): (u32, String, String) = mysql::from_row(row);
    //         println!("ID: {}, Name: {}, Email: {}", id, name, email);
    //     }
    // }

    // 条件查询使用预处理
    {
        let select_query = "SELECT id, name, email FROM users WHERE id < ?";
        let stmt = conn.prep(select_query).unwrap();
        let mut results = conn.exec_iter(&stmt, ("3",)).unwrap();
        while let Some(row) = results.next() {
            let row = row.unwrap();
            let (id, name, email): (u32, String, String) = mysql::from_row(row);
            println!("ID: {}, Name: {}, Email: {}", id, name, email);
        }
    }
    

}

查询时使用结构体

use mysql::Pool;
use mysql::OptsBuilder;
use mysql::prelude::*;
use mysql::Row;
use mysql::FromRowError;
#[derive(Debug, PartialEq, Eq)]
struct User {
    id: u32,
    name: String,
    email: String,
}

impl User {
    fn from_row(row: Row) -> Result<Self, FromRowError> {
        Ok(User {
            id: row.get(0).ok_or(FromRowError(row.clone()))?,
            name: row.get(1).ok_or(FromRowError(row.clone()))?,
            email: row.get(2).ok_or(FromRowError(row.clone()))?,
        })
    }
}

fn main() {
    // 初始化连接池
    let opts = OptsBuilder::new()
        .ip_or_hostname(Some("localhost"))
        .tcp_port(3306)
        .user(Some("root"))
        .pass(Some("root"))
        .db_name(Some("test"));
    let pool: Result<Pool, mysql::Error> = Pool::new(opts);
    let mut conn;
    if let Ok(p) = pool {
        conn = p.get_conn().unwrap();
    } else {
        panic!("Failed to connect to MySQL.");
    }

    // 插入数据
    let name = "test ' or 1 = 1";
    let email = "alice@example.com";
    let insert_query = "INSERT INTO users (name, email) VALUES (?, ?)";
    let params: (&str, &str) = (name, email);
    match conn.exec_drop(insert_query, params) {
        Ok(_) => println!("Data inserted successfully."),
        Err(e) => println!("Failed to insert data: {}", e),
    }

    // 查询数据
    // {
    //     let select_query = "SELECT id, name, email FROM users";
    //     let mut results: mysql::QueryResult<'_, '_, '_, mysql::Text> = conn.query_iter(select_query).unwrap();

    //     while let Some(row) = results.next() {
    //         let row = row.unwrap();
    //         let (id, name, email): (u32, String, String) = mysql::from_row(row);
    //         println!("ID: {}, Name: {}, Email: {}", id, name, email);
    //     }
    // }

    // 条件查询使用预处理
    {
        let select_query = "SELECT id, name, email FROM users WHERE id < ?";
        let stmt = conn.prep(select_query).unwrap();
        let mut results = conn.exec_iter(&stmt, (3,)).unwrap(); // 注意这里参数应该是数字类型
        while let Some(row) = results.next() {
            let row = row.unwrap();
            let user = User::from_row(row).unwrap();
            println!("ID: {}, Name: {}, Email: {}", user.id, user.name, user.email);
        }
    }
    

}