Rust SQLX执行SQL查询结果与PyMySQL、MySQL客户端不一致

使用Rust sqlx执行SQL语句时返回结果不正确，相同SQL语句在PyMySQL和MySQL客户端中执行能得到一致的正确结果（计数为39，最大时间为'2023-07-20 15:08:33'），但sqlx返回的结果为0和None，怀疑是某些SQL函数的处理存在问题。

SQL语句 #

SELECT COUNT(*),MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog 
WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW()

Rust sqlx代码 #

let sql = format!(r#"SELECT COUNT(*),MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW()"#);
match sqlx::query(sql.as_str())
    .fetch_one(&pool)
    .await {
    Ok(res) => {
        log::info!("res: {:?}", res);
        log::info!("res: {:?}, {:?}", res.get::<i32, _>(0), res.get::<Option<String>, _>(1));
    }
    Err(e) => {
        log::error!("{e}");
        return;
    }
};

sqlx输出 #

[2023-07-20 15:30:35] INFO [evi_saver::save_evi_layer::test] src/save_evi_layer.rs:437: res: MySqlRow { row: Row { storage: b"\x08\0\0\0\0\0\0\0\0", values: [Some(1..9), None] }, format: Binary, columns: [MySqlColumn { ordinal: 0, name: COUNT(*), type_info: MySqlTypeInfo { type: LongLong, flags: ColumnFlags(NOT_NULL | BINARY), char_set: 63, max_size: Some(21) }, flags: Some(ColumnFlags(NOT_NULL | BINARY)) }, MySqlColumn { ordinal: 1, name: MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')), type_info: MySqlTypeInfo { type: VarString, flags: ColumnFlags(0x0), char_set: 224, max_size: Some(76) }, flags: Some(ColumnFlags(0x0)) }], column_names: {COUNT(*): 0, MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')): 1} }
[2023-07-20 15:30:35] INFO [evi_saver::save_evi_layer::test] src/save_evi_layer.rs:438: res: 0, None

PyMySQL代码 #

cursor = db.cursor()
sql = """SELECT COUNT(*),MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW()"""
cursor.execute(sql)
res = cursor.fetchall()
print(res)

PyMySQL输出 #

((39, '2023-07-20 15:08:33'),)

MySQL客户端输出 #

mysql> SELECT COUNT(*), MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) FROM rulelog WHERE STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND NOW();
+----------+--------------------------------------------------+
| COUNT(*) | MAX(STR_TO_DATE(occurtime, '%Y-%m-%d %H:%i:%s')) |
+----------+--------------------------------------------------+
|       39 | 2023-07-20 15:08:33                              |
+----------+--------------------------------------------------+
1 row in set (0.03 sec)

环境信息 #

SQLx version: [0.7.1]
SQLx features enabled: ["mysql", "runtime-tokio", "runtime-tokio-native-tls"]
Database server and version: [MySQL 5.7.42-0ubuntu0.18.04.1 (Ubuntu)]
Operating system: [ubuntu0.18.04.1]
rustc --version: [rustc 1.72.0-nightly (839e9a6e1 2023-07-02)]

内容的提问来源于stack exchange，提问作者wengang yang