菜单
探索 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 中，这可以通过操作两个因素来控制：精度（precision）和小数位数（scale）。

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

这两个参数用于使用 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 位的精度。

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

浮点数 vs 数值类型

由 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 类型可以表示从 UTC 1970-01-01 00:00:01 到 UTC 2038-01-19 03:14:07 的值。它也可以处理小数秒。存储 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 的开源数据库工具包，旨在提高应用程序开发人员在使用数据库时的生产力和信心。

常见问题

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

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

Prisma 数据指南

一个不断增长的图书馆，专注于让数据库更容易理解。

由 Prisma 用 ❤️ 制作
© . All rights reserved.