菜单
探索 Prisma

MySQL

MySQL 数据类型简介

分享到

简介

一般来说，关系型数据库的主要功能之一是能够定义模式或表结构，以精确指定它们将包含的数据的格式。 这是通过规定这些结构包含的列以及它们的数据类型和任何约束来完成的。

数据类型指定了它们接受和存储的数据的一般模式。 值必须遵守它们概述的要求，才能被 MySQL 接受。 虽然可以定义自定义要求，但数据类型提供了基本构建块，使 MySQL 能够验证输入并使用适当的操作处理数据。

MySQL 包括范围广泛的数据类型，用于标记和验证值是否符合适当的类型。 在本指南中，我们将讨论 MySQL 中最常用的数据类型、它们使用的不同输入和输出格式，以及如何配置各种字段以满足应用程序的需求。

MySQL 中的数据类型有哪些？

在深入细节之前，让我们大致了解一下 MySQL 提供了哪些数据类型。

MySQL 支持合理范围的数据类型，适用于各种简单和复杂的数据类型。 这些包括

  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • INT
  • BIGINT
  • DECIMAL
  • NUMERIC
  • FLOAT
  • DOUBLE
  • BIT
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIME
  • YEAR
  • CHAR
  • VARCHAR
  • BINARY
  • VARBINARY
  • BLOB
  • TEXT
  • ENUM
  • SET
  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON
  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION
  • JSON

我们将在本指南的后续部分更深入地介绍其中最常用的类型。

MySQL 数据类型入门

在开始使用类型时，重要的是要记住，类型本身并不总是数据验证的完整解决方案，而只是一个组件。 其他数据库工具，如约束，也在定义正确性方面发挥作用。 尽管如此，数据类型通常是防止无效数据的第一道防线。

在许多情况下，MySQL 提供的通用类型适用于您将要存储的数据类型。 例如，虽然您可以将几何点的坐标存储在两个不同的数字列中，但提供的 point 类型是专门为存储和验证这种类型的信息而构建的。 在选择类型时，请检查以确保您使用的是最适合您的用例的特定类型。

数字和数值

MySQL 包括一系列适用于不同场景的数值数据类型。 合适的类型取决于您计划存储的值的确切性质以及您的精度要求。

整数

整数数据类型是一类用于存储不带任何分数或小数的数字的类型。 这些可以是正值或负值，不同的整数类型可以存储不同范围的数字。 接受值范围较小的整数类型比范围较宽的整数类型占用更少的空间。

整数类型的基本列表包括以下内容

整数类型长度适用的有符号范围适用的无符号范围
TINYINT1 字节-128 到 1270 到 255
SMALLINT2 字节-32768 到 327670 到 65535
MEDIUMINT3 字节-8388608 到 83886070 到 16777215
INT4 字节-2147483648 到 21474836470 到 4294967295
BIGINT8 字节-2^63 到 2^63-10 到 2^64-1

以上类型受其有效范围的限制。 任何超出范围的值都将导致错误。

除了上面提到的类型之外，MySQL 还识别一个名为 SERIAL 的别名。 将列标记为 SERIAL 将赋予其以下属性：BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE。 这用作常用主键列属性的简写。 每当添加记录时，该列将自动分配一个新的唯一值。

定点

定点类型用于控制带小数的数字可能达到的精度或具体程度。 在 MySQL 中，这可以通过操纵两个因素来控制：精度和刻度。

精度是一个数字可以拥有的最大总位数。 相比之下，刻度是小数点右侧的位数。 通过操纵这些数字，您可以控制数字的小数和非小数部分允许有多大。

这两个参数用于使用 numeric 或 decimal 数据类型（这两种类型在 MySQL 中是同义的）来控制任意精度。numeric 类型接受零到两个参数。

如果没有参数，则列定义为精度为 10，刻度为 0。 这意味着该列最多可以容纳 10 位数字，但这些数字都不能在小数点后。

NUMERIC

当提供单个参数时，它被解释为列的精度，刻度设置为 0。 这有效地允许您指定类似整数的数字（没有小数或小数部分）的最大位数。 例如，如果您需要一个 5 位数的整数，您可以指定

NUMERIC(5)

使用这两个控件配置列时，请指定精度，然后指定刻度。 MySQL 将使用刻度数字将任何输入的十进制部分四舍五入到正确的位数。 MySQL 将使用精度和刻度来确定小数点左侧允许有多少位数字。 如果条目超过允许的位数，MySQL 将产生错误。

例如，我们可以指定一个总精度为 5，刻度为 2 的列

NUMERIC(5, 2)

此列将具有以下行为

输入值四舍五入的值接受（符合精度）？
400.28080400.28是
8.3327998.33是
11799.79911799.80否
1179911799否
2802.272802.27否

浮点

浮点数是表达十进制数的另一种方式，但没有精确、一致的精度。 相反，浮点类型只有一个最大精度的概念，这通常与硬件的架构和平台有关。

例如，要将浮点列限制为 8 位精度，可以使用 FLOAT 类型，该类型使用 4 个字节存储结果，精度范围为 0 到 23 位数字

FLOAT(8)

同样，DOUBLE 类型使用 8 个字节来存储数据，并且可以使用 24 到 53 位数字的精度。

由于这些设计选择，浮点数可以有效地处理具有大量小数的数字，但并非总是精确的。 数字的内部表示可能会导致输入和输出之间存在细微差异。 当比较值、进行浮点数学运算或执行需要精确值的操作时，这可能会导致意外行为。

浮点与数值

由 FLOAT 和 DOUBLE 等类型提供的浮点数以及由 NUMERIC 或 DECIMAL 类型提供的定点数都可以用于存储十进制值。 如何知道使用哪一个？

一般规则是，如果您需要计算的精确性，NUMERIC 类型始终是更好的选择。NUMERIC 类型将完全按照提供的方式存储值，这意味着在检索或计算值时，结果是完全可预测的。NUMERIC 类型被称为任意精度，因为您可以指定类型所需的精度量，它将在字段中存储该精确位数的数字。

相比之下，像 FLOAT 和 DOUBLE 这样的类型是可变精度类型。 它们保持的精度量取决于输入值。 当它们达到允许的精度级别末尾时，它们可能会舍入剩余的数字，从而导致提交的值和检索的值之间存在差异。

那么，何时使用可变精度类型呢？ 当不需要精确值（例如，无论如何都会舍入）并且速度非常重要时，可变精度类型（如 FLOAT 和 DOUBLE）非常适合。 与 NUMERIC 类型相比，可变精度通常会提供性能优势。

字符串类型

MySQL 的字符类型和字符串类型可以分为两类：固定长度和可变长度。 这两者之间的选择会影响 MySQL 如何为每个值分配空间以及如何验证输入。

MySQL 中最简单的基于字符的数据类型是 char 类型。 如果没有参数，char 类型接受单个字符作为输入

CHAR

当声明中提供正整数时，char 列将存储一个固定长度的字符串，该字符串等于指定的字符数

CHAR(10)

如果提供的字符串字符数较少，则将附加空格以填充长度

输入输入字符数存储值存储字符数
'tree'4'tree      '10

如果给定的字符串字符数大于允许的字符数，MySQL 将引发错误。 作为此规则的例外，如果溢出的字符都是空格，MySQL 将简单地截断多余的空格以适合该字段。

固定长度字符字段的替代方案是可变长度字段。 为此，MySQL 提供了 varchar 类型。varchar 类型存储的字符没有固定大小。 与 char 不同，varchar 不能在不指定要存储的最大字符数的情况下使用。

通过使用正整数定义 varchar，您可以设置最大字符串长度

VARCHAR(10)

这与使用带整数的 char 类型不同，因为如果输入不满足最大字段长度，varchar 将不会填充值

输入输入字符数存储值存储字符数
'tree'4'tree'4

如果字符串大于最大长度，MySQL 将抛出错误。 char 字段中存在的相同截断行为也发生在这里：如果溢出的字符是空格，它们将被截断以适合最大字符长度。

MySQL 还支持 binary 和 varbinary 数据类型。 这些类型的操作方式与 char 和 varchar 类型类似，但存储的是二进制字符串而不是字符串。 这对它们的存储方式和操作方式（例如比较、排序等）有影响。

对于 binary 和 varbinary 类型，定义列类型时给定的整数表示字节数，而不是字符数。

MySQL 为字符串和字符存储提供的另外两种数据类型是 blob 和 text。 这些类型的操作方式分别类似于 varchar 和 varbinary 类型，旨在用于存储大型对象。 它们的操作方式与其对应类型基本相同，但有一些差异，例如无法拥有默认值，并且在创建索引时需要前缀长度。

布尔值

MySQL 实际上没有用于表示真值和假值的本机布尔类型。

MySQL 识别类型 BOOL 或 BOOLEAN，以便与其他数据库系统兼容。 然而，它的内部实现使用 TINYINT(1) 列来存储值，并根据一组规则将它们解释为真或假。

在布尔上下文中解释数值时，值 0 被认为是假。 所有非零值都被认为是真。

MySQL 识别 布尔文字 TRUE 和 FALSE，并在存储它们时将 TRUE 转换为 1，将 FALSE 转换为 0。

日期和时间

MySQL 支持表示日期、时间和两者的组合。

日期

date 类型可以存储日期，而无需关联的时间值

DATE

在处理 date 列的输入时，MySQL 可以解释不同的格式以确定要存储的正确日期。 但是，组成部分必须始终以相同的顺序出现：年、月，然后是日。 STR_TO_DATE() 函数可用于帮助将其他日期格式转换为 MySQL 将正确解释的格式。

显示日期时，MySQL 使用 YYYY-MM-DD 格式。 您可以使用 DATE_FORMAT() 函数以其他格式格式化输出。

date 类型可以存储从 1000-01-01 到 9999-12-31 的值。

时间

time 数据类型可以存储一天中的特定时间，而无需关联的时区或日期。

在处理 time 列的输入时，MySQL 可以解释多种格式以确定要存储的正确时间。 当输入具有冒号时，通常将其解释为 hh:mm:ss。 任何缩短的值（仅使用一列）都将被解释为使用 hh:mm。 当输入不包含冒号时，将处理时间以首先填充最小值。 例如，1045 被视为 10 分钟和 45 秒。

如果给定小数点，MySQL 还支持小数秒。 它最多存储小数点后 6 位精度。 time 列中的值的范围可以从 -838:59:59.000000 到 838:59:59.000000。

显示时间值时，MySQL 使用 hh:mm:ss 格式。 与日期一样，提供了一个名为 TIME_FORMAT() 的函数，用于使用其他格式显示时间值。

时间戳和日期时间

MySQL 可以表示时间戳，日期和时间的组合，用于表示时间中的特定时刻，有两种不同的变体：使用 timestamp 类型和 datetime 类型。

datetime 类型可以表示从 1000-01-01 00:00:00 到 9999-12-31 23:59:59 的值。 它还可以包括与 time 类型类似的小数秒，最多六位数字。

timestamp 类型可以表示从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 的值。 它也可以处理小数秒。 存储 timestamp 值时，所有值都从给定的时区转换为 UTC 进行存储，并在检索时转换回本地时区。datetime 类型不执行此操作。

从 MySQL 8.0.19 开始，您可以在存储 timestamp 时包含时区偏移量，以显式设置存储值的时区。 您可以通过在时间组件之后包含一个值来执行此操作，并且不带空格以指示偏移量。 接受值的范围从 -14:00 到 +14:00，这表示存储值与 UTC 的偏移量。

在决定是使用 datetime 还是 timezone 类型来存储日期和时间值时，通常最好根据它们最适合的用途来区分它们。

将 datetime 值视为相对于日历和时钟的特定日期和时间，无论它在何处被检索。 如果一个人晚上 11 点睡觉，则 datetime 值可以表示该值，而与该人当前所在的时区无关。

另一方面，timezone 值最适合表示跨时区明确的特定时间点。 要发送视频通话邀请，timezone 值可以确保会议在每个人的同一时间发生，而与参与者所在的时区无关。

其他有用的类型

除了我们上面深入介绍的类型之外，还有一些其他类型在特定场景中也很有用。 我们将简要介绍这些内容，以便您了解如何使用它们以及何时它们可能有用。

枚举类型和集合类型

enum 和 set 类型是两种相关的类型，允许用户指定列的有效值。

enum 类型是一种字符串类型，允许用户在创建列时定义有效值的集合。 任何与定义的值之一匹配的值都会被接受，所有其他值都会被拒绝。 这类似于下拉菜单的功能，即可以从一组特定的选项中进行选择。 例如，可以创建一个名为 season 的 enum，其值为 winter、spring、summer 和 autumn。

要创建 enum 列，请将类型指定为 enum，并在括号内给出可能的字符串值（用逗号分隔），如下所示

season ENUM('winter', 'spring', 'summer', 'autumn')

一种类似的用户定义类型是 set 类型。 与 enum 类型类似，set 类型允许用户在定义时将有效值指定为字符串。 这两种类型之间的区别在于，在 set 中，每个记录可以存储多个值。

例如，如果您需要一列来表示志愿者可以工作的星期几，您可以拥有一个像这样的 set 列

availability SET('sunday', 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday')

在为我们刚刚创建的 availability 列输入值时，您需要提供一个字符串，其中用逗号分隔志愿者可以工作的每一天。 例如

'monday,tuesday,wednesday,thursday,friday'
'sunday,saturday'
'monday,wednesday,friday'
'thursday'

对于 MySQL 中的 set 类型，输入中的重复值始终被删除，并且在检索时，这些值遵循 set 定义中使用的顺序，而与列中输入的顺序无关。

JSON

MySQL 使用 json 类型支持 JSON 中的列。 以 json 形式存储的数据以二进制形式存储，以便更快地执行和处理，从而使服务器不必解释字符串即可对 JSON 值进行操作。

JSON

为了对 JSON 列进行操作，MySQL 提供了 许多函数来处理文档中的值。

结论

在本文中，我们介绍了许多在处理 MySQL 数据库时最有用的常用数据类型。 还有其他类型未在本指南中介绍，但了解它们也很有帮助，但这些类型代表了大多数用例的良好起点。

重要的是要适当地使用类型系统，以便您可以控制有效值并按预期对数据进行操作。 如果您选择的类型不适合您的数据，您可能会遇到一些陷阱，因此在大多数情况下，在提交数据类型之前仔细考虑是值得的。

如果您正在使用 Prisma Client 来操作您的 MySQL 数据库，您可以在 Prisma 的 MySQL 数据连接器文档 中找到一些常见的 MySQL 和 Prisma 类型之间的映射关系。

PRISMA.IO 相关内容

在 Prisma schema 使用的数据模型中，数据类型由字段类型表示。请查看我们的文档以了解更多信息。

Prisma 是一个适用于 Typescript 和 Node.js 的开源数据库工具包，旨在使应用程序开发者在操作数据库时更高效和更有信心。

FAQ

DECIMAL 列的声明语法是 DECIMAL(M, D)。参数的值范围如下：

  • M 是最大位数（精度）。它的范围是 1 到 65。
  • D 是小数点右边的位数（刻度）。它的范围是 0 到 30，并且必须不大于 M。

MySQL 中字符串类型的存储要求可以用下表表示，其中 L 表示给定字符串值的实际字节长度。

数据类型所需存储空间
TINYTEXTL + 1 字节，其中 L < 2^8
TEXTL + 2 字节，其中 L < 2^16
MEDIUMTEXTL + 3 字节，其中 L < 2^24
LONGTEXTL + 4 字节，其中 L < 2^32

TEXT 和 VARCHAR 类似地存储字符，没有固定大小。

VARCHAR 的不同之处在于，它必须指定要存储的最大字符数才能使用，而 TEXT 则不需要。

VARCHAR 的示例定义语法如下所示：

VARCHAR(10)

VARCHAR 列中的值是可变长度的字符串。最大长度可以指定为 0 到 65,535 之间的值。

VARCHAR 的有效最大长度受所有列的最大行大小（65,535 字节）的限制。

ENUM 是一种有利的存储类型，适用于列具有有限的可能值集的情况。

它的存储要求与所有列相同：

数据类型所需存储空间
ENUM1 或 2 个字节，具体取决于枚举值的数量（最多 65,535 个值）
关于作者
Justin Ellingwood

Justin Ellingwood

自 2013 年以来，Justin 一直在撰写关于数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。
在 GitHub 上编辑此页

Prisma 的数据指南

一个不断增长的文章库，专注于使数据库更易于理解。

用 ❤️ 由 Prisma 制作