Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Appearance settings

[MySQL] 请教一下MySQL表里json类型在struct上应该怎么表示? #487

Unanswered
Cofecit asked this question in Q&A
Discussion options

MySQL表里有表结构:

CREATE TABLE `json_test`
(
    `id`   bigint NOT NULL AUTO_INCREMENT,
    `data` json DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci

表里已有一条数据:

mysql> select * from json_test;
+----+----------+
| id | data     |
+----+----------+
|  1 | {"a": 1} |
+----+----------+
1 rows in set (0.00 sec)

用rbatis的rbdc::Json表示json字段类型,查询可以成功,但是insert失败:

use serde::{Deserialize, Serialize};

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct JsonTest {
    pub id: u64,
    pub data: rbdc::Json,
}
rbatis::crud!(JsonTest {}, "json_test");

#[cfg(test)]
mod tests {
    use rbatis::RBatis;
    use rbdc::rt::tokio;
    use rbdc_mysql::MysqlDriver;

    use super::*;

    #[tokio::test]
    async fn test_json_field_select() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();
        let result = JsonTest::select_by_column(&rb, "id", 1).await;
        // OK
        assert!(result.is_ok());
    }

    #[tokio::test]
    async fn test_json_field_insert() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();

        let record = JsonTest {
            id: 2,
            data: rbdc::Json(String::from(r#"{"b":2}"#)),
        };

        // FAILED: called `Result::unwrap()` on an `Err` value: E("1210 (HY000): Incorrect arguments to mysqld_stmt_execute")
        JsonTest::insert(&rb, &record).await.unwrap();
    }
}

如果用String表示json类型字段,插入可以成功,但是查询失败:

use serde::{Deserialize, Serialize};

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct JsonTest {
    pub id: u64,
    pub data: String,
}
rbatis::crud!(JsonTest {}, "json_test");

#[cfg(test)]
mod tests {
    use rbatis::RBatis;
    use rbdc::rt::tokio;
    use rbdc_mysql::MysqlDriver;

    use super::*;

    #[tokio::test]
    async fn test_json_field_select() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();
        let result = JsonTest::select_by_column(&rb, "id", 1).await;
        assert!(!result.is_ok());
        // FAILED: called `Result::unwrap()` on an `Err` value: E("error while decoding value: invalid type: map, expected a string")
        result.unwrap();
    }

    #[tokio::test]
    async fn test_json_field_insert() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();

        let record = JsonTest {
            id: 2,
            data: String::from(r#"{"b":2}"#),
        };

        // OK
        JsonTest::insert(&rb, &record).await.unwrap();
    }
}

实在不清楚这个类型的字段该怎么表示了......

You must be logged in to vote

Replies: 1 comment · 2 replies

Comment options

MySQL表里有表结构:

CREATE TABLE `json_test`
(
    `id`   bigint NOT NULL AUTO_INCREMENT,
    `data` json DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci

表里已有一条数据:

mysql> select * from json_test;
+----+----------+
| id | data     |
+----+----------+
|  1 | {"a": 1} |
+----+----------+
1 rows in set (0.00 sec)

用rbatis的rbdc::Json表示json字段类型,查询可以成功,但是insert失败:

use serde::{Deserialize, Serialize};

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct JsonTest {
    pub id: u64,
    pub data: rbdc::Json,
}
rbatis::crud!(JsonTest {}, "json_test");

#[cfg(test)]
mod tests {
    use rbatis::RBatis;
    use rbdc::rt::tokio;
    use rbdc_mysql::MysqlDriver;

    use super::*;

    #[tokio::test]
    async fn test_json_field_select() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();
        let result = JsonTest::select_by_column(&rb, "id", 1).await;
        // OK
        assert!(result.is_ok());
    }

    #[tokio::test]
    async fn test_json_field_insert() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();

        let record = JsonTest {
            id: 2,
            data: rbdc::Json(String::from(r#"{"b":2}"#)),
        };

        // FAILED: called `Result::unwrap()` on an `Err` value: E("1210 (HY000): Incorrect arguments to mysqld_stmt_execute")
        JsonTest::insert(&rb, &record).await.unwrap();
    }
}

如果用String表示json类型字段,插入可以成功,但是查询失败:

use serde::{Deserialize, Serialize};

#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct JsonTest {
    pub id: u64,
    pub data: String,
}
rbatis::crud!(JsonTest {}, "json_test");

#[cfg(test)]
mod tests {
    use rbatis::RBatis;
    use rbdc::rt::tokio;
    use rbdc_mysql::MysqlDriver;

    use super::*;

    #[tokio::test]
    async fn test_json_field_select() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();
        let result = JsonTest::select_by_column(&rb, "id", 1).await;
        assert!(!result.is_ok());
        // FAILED: called `Result::unwrap()` on an `Err` value: E("error while decoding value: invalid type: map, expected a string")
        result.unwrap();
    }

    #[tokio::test]
    async fn test_json_field_insert() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();

        let record = JsonTest {
            id: 2,
            data: String::from(r#"{"b":2}"#),
        };

        // OK
        JsonTest::insert(&rb, &record).await.unwrap();
    }
}

实在不清楚这个类型的字段该怎么表示了......

JsonV<T> 结构体,T可以是serde_json::Value 或者任意的json结构体

You must be logged in to vote
2 replies
@Cofecit
Comment options

@zhuxiujia 还是不太行,select没问题,insert会报Incorrect arguments to mysqld_stmt_execute错。MySQL版本是8.0.20。

use serde::Serializer;
use serde_json::{Map, Value};

/**
CREATE TABLE `test`.`json_test`
(
    `id`   bigint NOT NULL AUTO_INCREMENT,
    `data` json DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
 */
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct JsonTest {
    pub id: u64,
    pub data: rbdc::JsonV<serde_json::Value>,
}
rbatis::crud!(JsonTest {}, "json_test");

#[cfg(test)]
mod tests {
    use rbatis::RBatis;
    use rbdc::rt::tokio;
    use rbdc_mysql::MysqlDriver;
    use serde_json::Number;

    use super::*;

    #[tokio::test]
    async fn test_json_field_select() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();
        let result = JsonTest::select_by_column(&rb, "id", 1).await;
        // OK
        result.unwrap();
    }

    #[tokio::test]
    async fn test_json_field_insert() {
        let rb = RBatis::new();
        rb.init(MysqlDriver {}, "mysql://root:123456@localhost:3306/test").unwrap();

        let mut data = Map::new();
        data.insert("ccc".into(), Value::Number(Number::from(123)));
        let record = JsonTest {
            id: 1,
            data: rbdc::JsonV(serde_json::Value::Object(data)),
        };

        let res = JsonTest::insert(&rb, &record).await;
        // FAILED: called `Result::unwrap()` on an `Err` value: E("1210 (HY000): Incorrect arguments to mysqld_stmt_execute")
        res.unwrap();
    }
}
@zhuxiujia
Comment options

已修复,执行cargo update 更新即可

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
🙏
Q&A
Labels
help wanted Extra attention is needed fixed
2 participants
Morty Proxy This is a proxified and sanitized view of the page, visit original site.