Rust中的ORM探索 Diesel库（3）：源码解析-MySQL数据类型与Rust数据类型对应关系

介绍

在上文Rust中的ORM探索 Diesel库（2）：使用Diesel完成CRUD操作-CSDN博客中，我们学习Diesel简单的CRUD操作。例子中创建的数据表中的数据类型也比较简单，接下来我们看一下MySQL中的数据类型和Rust数据类型的对应关系。

创建数据库

在上文中，我们执行migration时，Diesel CLI会根据数据库的表结构，生成diesel::table!宏来表示数据表的各个字段类型，并存放到schema.rs文件中。

另外我们也可以通过命令 diesel print-schema 打印表的schema

为了验证一下，MySQL数据格式和Rust数据格式，我们尝试创建一个migration all_types

diesel migration generate all_types

在up.sql中，编写创建数据表的代码

-- Your SQL goes here
CREATE TABLE `all_types` (
	`i1` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`i2` TINYINT(3) UNSIGNED ZEROFILL NOT NULL DEFAULT '000',
	`i3` SMALLINT(6) NULL DEFAULT '0',
	`i4` MEDIUMINT(9) NOT NULL DEFAULT '0',
	`i5` BIGINT(20) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`i6` BIT(1) NOT NULL,
	`f1` FLOAT NULL DEFAULT NULL,
	`f2` DOUBLE NOT NULL,
	`f3` DECIMAL(20,6) NULL DEFAULT NULL,
	`t1` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`t2` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`t3` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`t4` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`t5` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_general_ci',
	`t6` LONGTEXT NOT NULL COLLATE 'utf8mb4_general_ci',
	`d1` DATE NULL DEFAULT NULL,
	`d2` TIME NULL DEFAULT NULL,
	`d3` TIMESTAMP NOT NULL,
	`d4` DATETIME NOT NULL,
	`o1` ENUM('Y','N') NOT NULL DEFAULT 'Y' COLLATE 'utf8mb4_general_ci',
	`o2` SET('Value A','Value B') NOT NULL DEFAULT 'Value B' COLLATE 'utf8mb4_general_ci',
	`b1` BINARY(50) NULL DEFAULT NULL,
	`b2` BLOB NULL DEFAULT NULL
);

i1~i6是整数类型，f1~f3是浮点类型，t1~t6是文本类型，d1~d4是日期时间类型，o1~o2是ENUM和SET，b1~b2是二进制类型。

执行migration命令

diesel migration run

生成的schema.rs文件中，会有报错。

排查原因：MySQL中有Mediumint类型，但Diesel的sqltype中，没有定义Mediumint类型。我们考虑使用其他类型代替。

阅读源码

为了更好的确定数据类型的对应关系，下面我们阅读一下源码。

Bool

/// The boolean SQL type.
///
/// On backends without a native boolean type,
/// this is emulated with the smallest supported integer.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`bool`][bool]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`bool`][bool]
///
/// [bool]: https://doc.rust-lang.org/nightly/std/primitive.bool.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 16, array_oid = 1000))]
#[diesel(sqlite_type(name = "Integer"))]
#[diesel(mysql_type(name = "Tiny"))]
pub struct Bool;

MySQL中，创建布尔类型时，数据类型使用 BOOLEAN ，对应到 schema.rs文件中是strcut Bool，models.rs文件中类型为 bool。

注意，虽然使用的BOOLEAN类型，但是MySQL的表结构中，该字段的类型是Tinyint(1)

TINYINT

/// The tiny integer SQL type.
///
/// This is only available on MySQL.
/// Keep in mind that `diesel print-schema` will see `TINYINT(1)` as `Bool`,
/// not `TinyInt`.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`i8`][i8]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`i8`][i8]
///
/// [i8]: https://doc.rust-lang.org/nightly/std/primitive.i8.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(mysql_type(name = "Tiny"))]
pub struct TinyInt;
#[doc(hidden)]
pub type Tinyint = TinyInt;

数据类型TINYINT，对应到 schema.rs文件中是Tinyint，models.rs文件中类型为 i8

SMALLINT

/// The small integer SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`i16`][i16]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`i16`][i16]
///
/// [i16]: https://doc.rust-lang.org/nightly/std/primitive.i16.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 21, array_oid = 1005))]
#[diesel(sqlite_type(name = "SmallInt"))]
#[diesel(mysql_type(name = "Short"))]
pub struct SmallInt;
#[doc(hidden)]
pub type Int2 = SmallInt;
#[doc(hidden)]
pub type Smallint = SmallInt;

数据类型SMALLINT，对应到 schema.rs文件中是Smallint，models.rs文件中类型为 i16

INTEGER(INT)

/// The integer SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`i32`][i32]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`i32`][i32]
///
/// [i32]: https://doc.rust-lang.org/nightly/std/primitive.i32.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 23, array_oid = 1007))]
#[diesel(sqlite_type(name = "Integer"))]
#[diesel(mysql_type(name = "Long"))]
pub struct Integer;
#[doc(hidden)]
pub type Int4 = Integer;

数据类型INT，对应到 schema.rs文件中是Integer，models.rs文件中类型为 i32

BIGINT

/// The big integer SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`i64`][i64]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`i64`][i64]
///
/// [i64]: https://doc.rust-lang.org/nightly/std/primitive.i64.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 20, array_oid = 1016))]
#[diesel(sqlite_type(name = "Long"))]
#[diesel(mysql_type(name = "LongLong"))]
pub struct BigInt;
#[doc(hidden)]
pub type Int8 = BigInt;
#[doc(hidden)]
pub type Bigint = BigInt;

数据类型BIGINT，对应到 schema.rs文件中是Bigint，models.rs文件中类型为 i64

FLOAT

/// The float SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`f32`][f32]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`f32`][f32]
///
/// [f32]: https://doc.rust-lang.org/nightly/std/primitive.f32.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 700, array_oid = 1021))]
#[diesel(sqlite_type(name = "Float"))]
#[diesel(mysql_type(name = "Float"))]
pub struct Float;
#[doc(hidden)]
pub type Float4 = Float;

数据类型FLOAT，对应到 schema.rs文件中是Float，models.rs文件中类型为 f32

DOUBLE

/// The double precision float SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`f64`][f64]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`f64`][f64]
///
/// [f64]: https://doc.rust-lang.org/nightly/std/primitive.f64.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 701, array_oid = 1022))]
#[diesel(sqlite_type(name = "Double"))]
#[diesel(mysql_type(name = "Double"))]
pub struct Double;
#[doc(hidden)]
pub type Float8 = Double;

数据类型DOUBLE，对应到 schema.rs文件中是Double，models.rs文件中类型为 f64

DECIMAL

/// The arbitrary precision numeric SQL type.
///
/// This type is only supported on PostgreSQL and MySQL.
/// On SQLite, [`Double`] should be used instead.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`bigdecimal::BigDecimal`] with `feature = ["numeric"]`
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`bigdecimal::BigDecimal`] with `feature = ["numeric"]`
///
/// [`bigdecimal::BigDecimal`]: /bigdecimal/struct.BigDecimal.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 1700, array_oid = 1231))]
#[diesel(mysql_type(name = "Numeric"))]
#[diesel(sqlite_type(name = "Double"))]
pub struct Numeric;

/// Alias for `Numeric`
pub type Decimal = Numeric;

数据类型DECIMAL，对应到 schema.rs文件中是Decimal，models.rs文件中类型是bigdecimal::BigDecimal。这里需要将diesel的 numeric feature启用，并且在项目中添加bigdecimal库。

cargo add diesel -F numeric
cargo add bigdecimal

TEXT

/// The text SQL type.
///
/// On all backends strings must be valid UTF-8.
/// On PostgreSQL strings must not include nul bytes.
///
/// Schema inference will treat all variants of `TEXT` as this type (e.g.
/// `VARCHAR`, `MEDIUMTEXT`, etc).
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`String`]
/// - [`&str`][str]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`String`]
///
/// [str]: https://doc.rust-lang.org/nightly/std/primitive.str.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 25, array_oid = 1009))]
#[diesel(sqlite_type(name = "Text"))]
#[diesel(mysql_type(name = "String"))]
pub struct Text;

pub type VarChar = Text;
#[doc(hidden)]
pub type Varchar = VarChar;
#[doc(hidden)]
pub type Char = Text;
#[doc(hidden)]
pub type Tinytext = Text;
#[doc(hidden)]
pub type Mediumtext = Text;
#[doc(hidden)]
pub type Longtext = Text;

数据类型TEXT、VARCHAR、CHAR、TINYTEXT、MEDIUMTEXT、LONGTEXT，对应到 schema.rs文件中都可以使用Text，也可以使用同名的结构体，models.rs文件中类型是String。

BINARY

/// The binary SQL type.
///
/// Schema inference will treat all variants of `BLOB` as this type (e.g.
/// `VARBINARY`, `MEDIUMBLOB`, etc).
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`Vec<u8>`][Vec]
/// - [`&[u8]`][slice]
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`Vec<u8>`][Vec]
///
/// [Vec]: std::vec::Vec
/// [slice]: https://doc.rust-lang.org/nightly/std/primitive.slice.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 17, array_oid = 1001))]
#[diesel(sqlite_type(name = "Binary"))]
#[diesel(mysql_type(name = "Blob"))]
pub struct Binary;

#[doc(hidden)]
pub type Tinyblob = Binary;
#[doc(hidden)]
pub type Blob = Binary;
#[doc(hidden)]
pub type Mediumblob = Binary;
#[doc(hidden)]
pub type Longblob = Binary;
#[doc(hidden)]
pub type Varbinary = Binary;
#[doc(hidden)]
pub type Bit = Binary;

数据类型BINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB、VARBINARY、BIT，对应到 schema.rs文件中都可以使用Binary，也可以使用同名的结构体，models.rs文件中类型是Vec<u8>。

在rust中，表示二进制数据，统一都使用Vec<u8>

DATE

/// The date SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`chrono::NaiveDate`][NaiveDate] with `feature = "chrono"`
/// - [`time::Date`][Date] with `feature = "time"`
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`chrono::NaiveDate`][NaiveDate] with `feature = "chrono"`
/// - [`time::Date`][Date] with `feature = "time"`
///
/// [NaiveDate]: https://docs.rs/chrono/*/chrono/naive/struct.NaiveDate.html
/// [Date]: https://docs.rs/time/0.3.9/time/struct.Date.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 1082, array_oid = 1182))]
#[diesel(sqlite_type(name = "Text"))]
#[diesel(mysql_type(name = "Date"))]
pub struct Date;

数据类型DATE，对应到 schema.rs文件中是Date，models.rs文件中类型有多种选择，如可以使用time::Date。这里需要将diesel的 time feature启用，并且在项目中添加time库。

cargo add diesel -F time
cargo add time

TIME

/// The time SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`chrono::NaiveTime`][NaiveTime] with `feature = "chrono"`
/// - [`time::Time`][Time] with `feature = "time"`
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`chrono::NaiveTime`][NaiveTime] with `feature = "chrono"`
/// - [`time::Time`][Time] with `feature = "time"`
///
/// [NaiveTime]: /chrono/naive/time/struct.NaiveTime.html
/// [Time]: /time/struct.Time.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 1083, array_oid = 1183))]
#[diesel(sqlite_type(name = "Text"))]
#[diesel(mysql_type(name = "Time"))]
pub struct Time;

数据类型TIME，对应到 schema.rs文件中是Time，models.rs文件中类型有多种选择，如可以使用time::Time。这里需要将diesel的 time feature启用，并且在项目中添加time库。

TIMESTAMP

/// The timestamp SQL type.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - [`std::time::SystemTime`][SystemTime] (PG only)
/// - [`chrono::NaiveDateTime`][NaiveDateTime] with `feature = "chrono"`
/// - [`time::PrimitiveDateTime`] with `feature = "time"`
/// - [`time::OffsetDateTime`] with `feature = "time"` (MySQL only)
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - [`std::time::SystemTime`][SystemTime] (PG only)
/// - [`chrono::NaiveDateTime`][NaiveDateTime] with `feature = "chrono"`
/// - [`time::PrimitiveDateTime`] with `feature = "time"`
/// - [`time::OffsetDateTime`] with `feature = "time"` (MySQL only)
///
/// [SystemTime]: std::time::SystemTime
/// [Timespec]: /time/struct.Timespec.html
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(postgres_type(oid = 1114, array_oid = 1115))]
#[diesel(sqlite_type(name = "Text"))]
#[diesel(mysql_type(name = "Timestamp"))]
pub struct Timestamp;

数据类型TIMESTAMP，对应到 schema.rs文件中是Timestamp，models.rs文件中类型有多种选择，如可以使用time::OffsetDateTime。这里需要将diesel的 time feature启用，并且在项目中添加time库。

DATETIME

/// Represents the MySQL datetime type.
///
/// ### [`ToSql`] impls
///
/// - [`chrono::NaiveDateTime`] with `feature = "chrono"`
/// - [`time::PrimitiveDateTime`] with `feature = "time"`
/// - [`time::OffsetDateTime`] with `feature = "time"`
///
/// ### [`FromSql`] impls
///
/// - [`chrono::NaiveDateTime`] with `feature = "chrono"`
/// - [`time::PrimitiveDateTime`] with `feature = "time"`
/// - [`time::OffsetDateTime`] with `feature = "time"`
///
/// [`ToSql`]: crate::serialize::ToSql
/// [`FromSql`]: crate::deserialize::FromSql
#[derive(Debug, Clone, Copy, Default, QueryId, SqlType)]
#[diesel(mysql_type(name = "DateTime"))]
#[cfg(feature = "mysql_backend")]
pub struct Datetime;

数据类型DATETIME，对应到 schema.rs文件中是Datetime，models.rs文件中类型有多种选择，如可以使用time::OffsetDateTime。这里需要将diesel的 time feature启用，并且在项目中添加time库。

Nullable特征

/// The nullable SQL type.
///
/// This wraps another SQL type to indicate that it can be null.
/// By default all values are assumed to be `NOT NULL`.
///
/// ### [`ToSql`](crate::serialize::ToSql) impls
///
/// - Any `T` which implements `ToSql<ST>`
/// - `Option<T>` for any `T` which implements `ToSql<ST>`
///
/// ### [`FromSql`](crate::deserialize::FromSql) impls
///
/// - `Option<T>` for any `T` which implements `FromSql<ST>`
#[derive(Debug, Clone, Copy, Default)]
pub struct Nullable<ST>(ST);

Nullable对应MySQL中的可为空属性，如可为空的INT类型，在schema.rs文件中使用Nullable<Integer>表示，在models.rs中使用Option<i32>表示

Unsigned特征

/// Represents the MySQL unsigned type.
#[derive(Debug, Clone, Copy, Default, SqlType, QueryId)]
#[cfg(feature = "mysql_backend")]
pub struct Unsigned<ST: 'static>(ST);

Unsigned对应MySQL中的无符号特征，如无符号的INT类型，在schema.rs文件中使用Unsigned<Integer>表示，在models.rs中使用u32表示。

注意，并不是所有的数值类型，在diesel中都实现了Unsigned，通过源码查看。只有整数类型实现了Unsigned。因此使用时应避免浮点数使用Unsigned。

类型对应表

MySQL类型		schema.rs中	models.rs中
非空	BOOLEAN	Bool	bool
可为空	BOOLEAN	Nullable<Bool>	Option<bool>
有符合非空	TINYINT	Tinyint	i8
有符合可为空	TINYINT	Nullable<Tinyint>	Option<i8>
无符号非空	TINYINT	Unsigned<Tinyint>	u8
无符号可为空	TINYINT	Nullable<Unsigned<Tinyint>>	Option<u8>
有符合非空	SMALLINT	Smallint	i16
有符合可为空	SMALLINT	Nullable<Smallint>	Option<i16>
无符号非空	SMALLINT	Unsigned<Smallint>	u16
无符号可为空	SMALLINT	Nullable<Unsigned<Smallint>>	Option<u16>
有符合非空	INTEGER	Integer	i32
有符合可为空	INTEGER	Nullable<Integer>	Option<i32>
无符号非空	INTEGER	Unsigned<Integer>	u32
无符号可为空	INTEGER	Nullable<Unsigned<Integer>>	Option<u32>
有符合非空	BIGINT	Bigint	i64
有符合可为空	BIGINT	Nullable<Bigint>	Option<i64>
无符号非空	BIGINT	Unsigned<Bigint>	u64
无符号可为空	BIGINT	Nullable<Unsigned<Bigint>>	Option<u64>
非空	FLOAT	Float	f32
可为空	FLOAT	Nullable<Float>	Option<f32>
非空	DOUBLE	Double	f64
可为空	DOUBLE	Nullable<Double>	Option<f64>
非空	DECIMAL	Decimal	bigdecimal::BigDecimal
可为空	DECIMAL	Nullable<Decimal>	Option<bigdecimal::BigDecimal>
非空	TEXT	Text	String
可为空	TEXT	Nullable<Text>	Option<String>
非空	VARCHAR	Varchar	String
可为空	VARCHAR	Nullable<Varchar>	Option<String>
非空	CHAR	Char	String
可为空	CHAR	Nullable<Char>	Option<String>
非空	TINYTEXT	Tinytext	String
可为空	TINYTEXT	Nullable<Tinytext>	Option<String>
非空	MEDIUMTEXT	Mediumtext	String
可为空	MEDIUMTEXT	Nullable<Mediumtext>	Option<String>
非空	LONGTEXT	Longtext	String
可为空	LONGTEXT	Nullable<Longtext>	Option<String>
非空	BINARY	Binary	Vec<u8>
可为空	BINARY	Nullable<Binary>	Option<Vec<u8>>
非空	TINYBLOB	Tinyblob	Vec<u8>
可为空	TINYBLOB	Nullable<Tinyblob>	Option<Vec<u8>>
非空	BLOB	Blob	Vec<u8>
可为空	BLOB	Nullable<Blob>	Option<Vec<u8>>
非空	MEDIUMBLOB	Mediumblob	Vec<u8>
可为空	MEDIUMBLOB	Nullable<Mediumblob>	Option<Vec<u8>>
非空	LONGBLOB	Longblob	Vec<u8>
可为空	LONGBLOB	Nullable<Longblob>	Option<Vec<u8>>
非空	VARBINARY	Varbinary	Vec<u8>
可为空	VARBINARY	Nullable<Varbinary>	Option<Vec<u8>>
非空	BIT	Bit	Vec<u8>
可为空	BIT	Nullable<Bit>	Option<Vec<u8>>
非空	DATE	Date	time::Date
可为空	DATE	Nullable<Date>	Option<time::Date>
非空	TIME	Time	time::Time
可为空	TIME	Nullable<Time>	Option<time::Time>
非空	TIMESTAMP	Timestamp	time::OffsetDateTime
可为空	TIMESTAMP	Nullable<Timestamp>	Option<time::OffsetDateTime>
非空	DATETIME	Datetime	time::OffsetDateTime
可为空	DATETIME	Nullable<Datetime>	Option<time::OffsetDateTime>

总结

至此，已经将MySQL中的数据类型和rust中数据类型进行了对应。同时我们在使用时，应尽量避免使用diesel中不支持的类型和属性，如MySQL中的 MEDIUMINT类型、无符号的FLOAT类型。

posted @ 2025-01-14 10:29 Spanner 阅读(87) 评论(0) 收藏举报

刷新页面返回顶部

spanner