Rust - mysql 增删改查

Sachi ·

更新时间:2024-11-10

· 730 次阅读

一、添加mysql依赖

mysql_async 是基于tokio2.0的异步mysql

[dependencies]
mysql_async = "0.22.2"
tokio = "0.2.13"

二、设置database_url，创建连接池

DSN：mysql://root:root@127.0.0.1:3306/testdb?pool_min=10&pool_max=100&conn_ttl=10

?后面的参数列表：

pool_min：连接池最小值，默认10 pool_max：连接池最大值，默认100 inactive_connection_ttl：默认0秒，如果空闲连接时长大于ttl，连接数也大于pool_min，就关闭 ttl_check_interval：默认30秒，ttl检测周期，每隔30秒检查一下空间连接的状态 conn_ttl：默认为wait_timeout，如果最后一次IO执行的时长大于ttl，则关闭该连接 tcp_keepalive：默认为None，tcp保持连接状态的时长，毫秒单位 tcp_nodelay：默认为true，是否启用TCP_NODELAY选项 stmt_cache_size：默认10条，缓存准备好的stmt语句 prefer_socket：是否查询@@socket变量 socket：默认为None，Unix套接字（或Windows上的命名管道）的路径 compression：默认None，压缩快慢，（fast = 1、on = true、best = 9，0~9）

创建连接池

let pool = mysql_async::Pool::new(database_url);

从连接池获取一个connect，当离开作用域，调用drop自动返还给pool

let conn = pool.get_conn().await?;

程序退出，需要手动关闭连接池

pool.disconnect().await?;

三、执行sql的几种函数调用

1、ping，判断mysql服务器是否可用

let conn = conn.ping().await?;

2、query，普通的查询，需要自己判断sql参数是否正确

let result = conn.query("SELECT * FROM t_user").await?;

3、prep_exec，使用stmt语句查询

let result = conn.prep_exec("SELECT * FROM user where id = :id", params! {
    "id" => 1
}).await?;

4、只查询一条结果集

let (conn, row): (_, Option) = conn.first_exec("SELECT * FROM user where id = :id", params! {
    "id" => 1
}).await?;

5、执行sql，不关心result结果集

    let conn = conn.drop_query(r"SET NAMES utf8").await?;
    let conn = conn.drop_exec(r"SET NAMES utf8", ()).await?;

6、批量执行sql

// params是param!参数的集合
let conn = conn.batch_exec(r"INSERT INTO payment (customer_id, amount, account_name) VALUES (:customer_id, :amount, :account_name)", params).await?;

7、事务处理

    let mut tr_opts = TransactionOptions::new();
    tr_opts.set_isolation_level(IsolationLevel::RepeatableRead);
    let tr = conn.start_transaction(tr_opts).await?;
    let result: QueryResult = tr.prep_exec("SELECT username, email FROM user where id = :id", params! {
        "id" => 1
    }).await?;
    let (tr, user_info) = result.map_and_drop(|row| {
        let username: Option = row.get("username");
        let email: Option = row.get("email");
        (username, email)
    }).await?;
    tr.commit().await?;
    println!("{:?}", user_info);

四、处理查询结果集Rows

1、使用mysql_async::from_row()，获取column的tuple结果

    let result = conn.prep_exec("SELECT username, email FROM user where id = :id", params! {
        "id" => 1
    }).await?;
    let (_, user_info) = result.map_and_drop(|row| {
        let (username, email): (Option, Option) = mysql_async::from_row(row);
        (username, email)
    }).await?;
    println!("{:?}", user_info);

2、使用row本身的函数get按照index索引，或者column的名字来获取

    let result = conn.prep_exec("SELECT username, email FROM user where id = :id", params! {
        "id" => 1
    }).await?;
    let (_, user_info) = result.map_and_drop(|row| {
        let username: Option = row.get("username");
        let email: Option = row.get("email");
        (username, email)
    }).await?;
    println!("{:?}", user_info);

五、mysql数据类型的值类型与rust类型对应关系 NULL => 各种类型的Option的None，int(0)，“NULL” Bytes(Vec) => String，Vec，bool('0', '1')，...(其他所有类型) Int(i64) =>i8 ~ i128，isize，bool(0, 1) UInt(u64) => u8 ~ u128，usize Float(f64) => f32/64 Date(u16, u8, u8, u8, u8, u8, u32) => Timespec，NaiveDateTime，NaiveDate Time(bool, u32, u8, u8, u8, u32) => Duration，NativeTime
作者：刀剑の神君

rust Mysql

1024 个赞