一、添加mysql依赖
mysql_async 是基于tokio2.0的异步mysql
[dependencies]
mysql_async = "0.22.2"
tokio = "0.2.13"
二、设置database_url，创建连接池
DSN：mysql://root:[email protected]: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