数据类型
在 MySQL(和其他关系数据库)中创建表时,您为每一列指定数据类型。为什么不把所有东西都存储为字符串呢?毕竟,当这本书使用”42”时,它表示为字符串,那么,为什么不只对所有内容都使用字符串,而对每一列都使用各种值呢?这一点有其优点。这部分是NoSQL数据库工作的方式(尽管功能更多),并且本书的作者看到了表,其中所有列均定义为varchar(255)字符串。为什么要费心使用整数、decimals、浮点数、日期、字符串等?有几个原因,这是本章的主题。
首先,将讨论对不同类型的值使用不同的数据类型的好处。然后,将概述 MySQL 中支持的数据类型。最后,将讨论数据类型如何影响查询性能以及如何选择列的数据类型。
为什么是数据类型?
列的数据类型定义可以存储哪种类型的值以及如何存储值。 另外,可能存在与数据类型相关的元属性,例如大小(例如,用于数字的字节数和字符串中最大字符数)以及用于字符串的字符集和排序规则。 尽管数据类型属性似乎是不必要的限制,但它们也有好处。 这些好处包括
- 数据验证
- 文档
- 优化的存储
- 性能
- 正确排序
本节的其余部分将讨论这些好处。
数据验证
数据类型的核心是定义允许的值类型。定义为整数数据类型的列只能存储整数值。这也是一种保障。如果您犯了一个错误,并尝试将值存储到与定义的数据类型不同的列中,则可以拒绝该值或转换该值。
提示 将错误的数据类型的值分配给列是导致错误还是要转换的数据类型取决于是否启用了STRICT_TRANS_TABLES(对于事务性存储引擎)和STRICT_ALL_TABLES(对于所有存储引擎)SQL模式以及是否启用了SQL模式。 被认为可以安全地转换数据类型。 始终允许某些被认为是安全的转换,例如,将“ 42”转换为42,反之亦然。 建议始终启用严格模式,这种模式会使DML查询在尝试进行不安全的转换或数据截断时失败。
当您可以确保存储在表中的数据始终具有预期的数据类型时,它会让你的生活更轻松。如果查询具有整数的列,您知道对返回的值执行算术操作是安全的。同样,如果您知道该值是字符串,可以安全地执行字符串操作。它需要进行一些前期的计划,但是一旦完成,您将学会了解自己知道数据的数据类型。
关于数据类型和数据验证,还有一个注意事项。通常,存在与数据类型关联的属性。在最简单的情况下,您具有最大大小。例如,整数的大小可以是 1、2、3、4 或 8 字节。这会影响可存储的值范围。此外,整数可以有符号或无符号。更复杂的示例是字符串,它不仅限制它们存储的文本量,还需要一个字符集来定义数据的编码方式,以及一个排序规则来定义数据的排序方式。
清单13-1给出了一个MySQL如何根据数据类型验证数据的示例。
Listing 13-1. Data validation based on data type
mysql> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.0003 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`val1` int(10) unsigned DEFAULT NULL,
`val2` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0011 sec)
mysql> INSERT INTO t1 (val1) VALUES ('abc');
ERROR: 1366: Incorrect integer value: 'abc' for column 'val1' at row 1
mysql> INSERT INTO t1 (val1) VALUES (-5);
ERROR: 1264: Out of range value for column 'val1' at row 1
mysql> INSERT INTO t1 (val2) VALUES ('abcdef');
ERROR: 1406: Data too long for column 'val2' at row 1
mysql> INSERT INTO t1 (val1, val2) VALUES ('42', 42);
Query OK, 1 row affected (0.0825 sec)
SQL 模式设置为默认值,其中包括STRICT_TRANS_TABLES。除了主键之外,该表还有两列,其中一列是无符号位的整数,另一列是 varchar(5),这意味着它最多可以存储五个字符。当尝试将字符串或负整数插入 val1 列时,将弹出该值,因为它无法安全地转换为无符号位的整数。同样,尝试将包含六个字符的字符串存储到 val2 列中失败。但是,将字符串’42’存储到 val1 中,将整数 42 存储到 val2 中被认为是安全的,因此是允许的。
数据验证的副作用是,您还描述了预期的数据 - 这是列的隐式文档。
Documentation
设计表时,您知道该表的预期用法是什么。当你或其他人以后使用表时, 这不一定清楚。有各种方法可以记录列:使用描述值的列名称、COMMENT 列子句、CHECK 约束和数据类型。
虽然数据类型不是记录列的最详细方式,当然也不应独立进行,但数据类型确实有助于描述您期望的数据类型。如果选择日期列而不是日期时间,则您显然只打算存储日期部分。同样,使用 tinyint 而不是 int 显示您只期望相对小的值。这一切都有助于您自己或他人了解可以预期什么样的数据。对数据了解得越好,在需要优化查询时,成功的变化也越好,这样它就可以间接地帮助查询优化。
提示 在表中提供文档的最好方法是使用COMMENT子句和CHECK约束。 但是,这些通常在表格图中是不可见的,在表格图中,数据类型有助于更好地了解预期的数据类型。
性能主题方面,显式选择数据类型也有其好处。其中之一与值的存储方式有关。
优化存储
MySQL 不会以相同方式存储所有数据。选择给定数据类型的存储格式尽可能紧凑以减少所需的存储。例如,考虑值 123456。如果将其存储为字符串,则至少需要 6 个字节加上 1 个字节来存储字符串的长度。如果改为选择整数,则只需要 3 个字节(对于整数,所有值始终使用相同数量的字节,取决于列允许的最大存储空间)。此外,从存储中读取整数不需要对该值进行任何解释,而对于字符串,则必须使用其字符集对值进行解码。
选择列的正确最大大小可以减少所需的存储量。如果需要存储整数,并且知道永远不需要超过 4 字节的存储的值,可以使用 int 数据类型,而不是使用 8 字节存储的 bigint。这是列所需的存储量的一半。如果您使用大数据,存储(和内存)节省可能会变得足够大,足以成为重要。但是,请注意不要过度优化。在许多情况下,更改数据类型或列的大小需要重建整个表,如果表很大,则这可能是一项昂贵的操作。 这样,最好现在使用更多的存储空间以节省以后的工作。
提示 与其他类型的优化一样,请注意不要过度优化数据类型。现在存储的节省相对较小,以后可能会造成疼痛。
数据的存储方式也会影响性能。
Performance
并非所有数据类型都是平等的。 整数在计算和比较中非常便宜,而必须使用字符集对存储的字节进行解码的字符串则相对昂贵。 通过选择正确的数据类型,可以显著提高查询的性能。 特别是,如果您需要比较两列中的值(可能在不同的表中),请确保它们具有相同的数据类型,包括字符集和字符串排序规则。 否则,必须先转换其中一列中的数据,然后才能将其与另一列进行比较。
虽然很容易理解为什么整数比字符串有更好的表现,但是使一种数据类型比另一种更好或更差的确切原因却相对复杂,并且取决于如何实现(存储在磁盘上)数据类型。 因此,对性能的进一步讨论将推迟到下一节对MySQL数据类型进行演练之后。
接下来将要讨论的最后一个好处是排序。
正确排序
日期类型对值的排序方式有重大影响。虽然人脑通常能直观地理解数据,但计算机需要一些帮助来了解两个值之间的比较。数据类型和字符串的排序是用于确保正确排序数据的关键属性。
日期类型对值的排序方式有重大影响。 尽管人脑通常可以直观地理解数据,但是计算机需要一些帮助来理解两个值之间的比较。 数据类型和字符串归类(collation )是用于确保数据正确排序的关键属性。
为什么排序很重要?有几个原因:
- 正确的排序要求知道两个值是否相等或一个值是否在给定范围内。 这对于使WHERE子句和联接条件按预期工作至关重要。
- 创建索引时,排序用于确保MySQL快速找到具有您要查找的值的行。 下一章将详细介绍索引
考虑值 8 和 10。它们如何排序?如果您认为它们为整数,则 8 先于 10。但是,如果您将它们视为字符串,则”10”(ASCII: 0x3130)先于”8”(ASCII: 0x38)。是否期望其中一个或另一个取决于您的应用,但除非也有具有非数值部分的值,否则您很可能期望出现要求数据类型为整数类型的整数行为。
考虑值8和10。它们如何排序? 如果您认为它们是整数,则8在10之前。但是,如果您将它们视为字符串,则“ 10”(ASCII:0x3130)在“ 8”(ASCII:0x38)之前。 是否期望一个或另一个取决于您的应用程序,但是除非还有带有非数字部分的值,否则您可能希望整数行为要求数据类型为整数类型。
现在,已经讨论了显式数据类型的好处是什么,是时候浏览 MySQL 支持的数据类型了。
MySQL 数据类型
MySQL 中有 30 多种不同的数据类型。其中几个可以调整的大小,精度,以及它们是否接受符号位的值。乍一看,它似乎很让人不知所措,但是如果将数据类型分组,则可以采用逐步的方法为数据选择正确的数据类型。
MySQL中的数据类型可以视为以下类别之一的一部分:
- 数值:包括整数,固定精度的十进制类型,近似精度的浮点类型和bit类型。
- 时间:包括year,date,time,datetime和timestamp。
- 字符串:包括二进制对象和带有字符集的字符串。
- JSON:JSON数据类型可以存储JSON文档。
- 空间:这些类型用于存储描述坐标系统中一个或多个点的值。
- 混合:MySQL有两种数据类型,都可以用作整数和字符串。
提示 MySQL参考手册对https://dev.mysql.com/doc/refman/8.0/en/data-types.html中的数据类型及其引用进行了全面的讨论。
本节的其余部分将介绍数据类型并讨论其详细信息。
数值数据类型
数值数据类型是 MySQL 支持的最简单数据类型。您可以在整数、固定精度十进制值和近似浮点值之间进行选择。
表 13-1 总结了数值数据类型,包括字节中的存储要求和支持的值范围。对于整数,您可以选择值是符号还是无符号,这会影响受支持的值范围。对于支持的值,开始值和结束值都包含在允许的值范围内。
数据类型 | 字节存储 | 范围 |
---|---|---|
tinyint | 1 | Signed: -128–127 Unsigned: 0–255 |
smallint | 2 | Signed: -32768–32767 Unsigned: 0–65535 |
mediumint | 3 | Signed: -8388608–8388607 Unsigned: 0–16777215 |
int | 4 | Signed: -2147483648–2147483647 Unsigned: 0–4294967295 |
bigint | 8 | Signed: -263–263-1 Unsigned: 0–264-1 |
decimal(M, N) | 1-29 | Depends on M and N |
float | 4 | Variable |
double | 8 | Variable |
bit(M) | 1-8 |
整数数据类型是最简单的,具有固定的存储要求和支持值的固定范围。小图的同义词是布尔(布尔值)。
十进制数据类型(数字是同义词)采用两个参数 M 和 N,这两个参数定义值的精度和比例。如果具有小数 (5,2),则值最多为五位数字,其中两位是小数(小数点右侧)。这意味着允许 -999.99 和 999.99 之间的值。最多支持 65 位数字。小数的存储量取决于使用 4 个字节的 9 位数字和使用 0+4 字节的 9 位数字数和剩余数字的数字数。
浮点和双数据类型存储近似值。这些类型对于数值计算是有效的,但代价是它们的值存在不确定性。它们分别使用 4 个字节和 8 个字节进行存储。
提示 切勿使用浮点数据类型来存储准确的数据,如货币存储。请改为使用精确的十进制数据类型。对于近似浮动点数据类型,绝不应使用相等 (+) 和不相等 (+) 运算符,因为比较两个近似值一般不会返回它们相等,即使它们本来是相等的。
最终的数字数据类型是位类型。它可以在一个值中存储 1 到 64 位。例如,这可用于位掩码。所需的存储取决于所需的位数(M 值);它可以近似为 FLOOR((M+7)/8 字节。
与数字类型相关的数据类型类别是时态数据类型,这是将涵盖的下一个类别。
时态数据类型
时态数据定义时间点。精度范围从一年到一米秒。除年数据类型外,值作为字符串输入,但使用内部优化格式,并且值将根据值表示的时间点正确排序。
13-2 显示了 MySQL 支持的时间数据类型、每种类型使用的字节存储量以及支持的值范围。
数据类型 | 字节存储 | 范围 |
---|---|---|
year | 1 | 1901–2155 |
date | 3-6 | ‘1000-01-01’ to ‘9999-12-31’ |
datetime | 5-8 | ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ |
timestamp | 4-7 | ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’ |
time | 3-6 | ‘-838:59:59.000000’ to ‘838:59:59.000000’ |
日期时间、时间戳和时间类型都支持小数秒到微秒分辨率。分数秒的存储要求为 0~3 字节,取决于位数(每两位数 1 字节)。
日期时间和时间戳列以微妙的方式不同。当您在日期时间列中存储值时,MySQL 会像指定时一样存储它。另一方面,对于时间戳列,该值将使用已配置为使用的时区 MySQL 转换为 UTC - @@session.time_zone 变量(默认情况下是系统时区)。同样,检索数据时,日期时间值将按您原始指定的值返回,而时间戳列将转换为 @@session.time_zone 变量中的时区集。
提示 使用日期时间列时,将数据存储在 UTC 时区中,并转换到使用数据时所需的时区。通过始终将值存储在 UTC 中,如果操作系统时区或MySQL 服务器时区已更改,或者您与不同时区的用户共享数据,则出现问题的可能性较小
使用字符串输入和检索日期和时间时,它们以专用格式存储在内部。实际字符串呢?让我们来看看字符串和二元数据类型。
字符串和二进制数据类型
字符串和二进制数据类型是存储任意数据的非常灵活的类型。二进制值和字符串之间的差异是字符串具有与其关联的字符集,因此 MySQL 知道如何解释数据。另一手的二进制值存储原始数据,这意味着您可以将它们用于任何类型的数据,包括图像和自定义数据格式。
虽然字符串和二进制数据非常灵活,但它们要同时支付成本。对于字符串,MySQL 需要解释字节以确定它们表示的字符。就所需的计算能力而言,这相对来说非常昂贵。某些字符集(包括 MySQL 8 中的默认字符集的 UTF-8)是可变宽度,即字符使用可变字节数;对于 UTF-8,它的范围为每字符 1 到 4 个字节。这意味着,如果您请求字符串的前四个字符,则可能需要读取 4 到 16 个字节,具体取决于它位于哪些字符,因此 MySQL 将需要分析字节以确定何时找到四个字符。对于二元字符串,数据含义的解释将重新放在应用程序上。
表 13-3 显示了 MySQL 中表示字符串和二进制数据的数据类型。该表包括可存储的最大数据量以及存储要求的说明。对于数据类型,(M)是列必须能够存储的最大字符数,在存储的 L 字节中,是需要表示用于编码的字符集中的字符串值的字节数。
数据类型 | 存储字节 | 最大长度 |
---|---|---|
char(M) | M*char width | 255 chars |
varchar(M) | L+1 or L+2 | 16383 chars for utf8mb4 and 65532 for latin1 |
tinytext | L+1 | 255 bytes |
text | L+2 | 65535 bytes |
mediumtext | L+3 | 16777216 bytes |
longtext | L+4 | 4294967296 bytes |
binary(M) | M | 255 bytes |
varbinary(M) | L+1 or L+2 | 65532 bytes |
tinyblob | L+1 | 255 bytes |
blob | L+2 | 65536 bytes |
mediumblob | L+3 | 16777216 bytes |
longblob | L+4 | 4294967296 bytes |
字符串和二进制对象的存储要求取决于数据的长度。L 是存储值所需的字节数;对于文本字符串,还必须考虑字符集。对于可变宽度类型,使用 1~4 字节来存储值的长度。对于 char(M) 列,使用 InnoDB 存储格式的紧凑系列,以及使用可变宽度字符集对字符串进行编码时,所需的存储可能小于字符宽度的 M 倍。
对于所有字符和 varchar,字符串的最大支持长度以字节为单位指定。这意味着可以存储的字符串类型的字符数取决于字符集。此外,字符、varchar、二进制列和二进制列都计入行宽度,其总宽度必须小于 64 kiB,这意味着实际上很少能够使用理论最大长度创建列。(这也是 varchar 和 varbinary 列在最多可以存储 65532 个字符/字节的原因。对于长文本列和长文本列,应该指出,虽然它们原则上可以存储多达 4 GiB 的数据,但在实践中,存储受 max_allowed_packet 变量限制,最多只能存储 1 GiB。
存储字符串的数据类型的另一个注意事项是,您必须为列选择字符集和排序规则。如果未明确选择一个,则将使用该表的默认值。在 MySQL 8 中,使用默认排序规则排序规则,默认字符集为 utf8mb4 utf8mb4_0900_ai_ci。utf8mb4 andutf8mb4_0900_ai_ci是什么意思?
utf8mb4 字符集是 UTF-8,每个字符最多支持 4 个字节(例如,某些表情符号是必需的)。最初,MySQL 仅支持每个字符最多 3 个字节的UTF-8,后来添加了 utf8mb4 以扩展支持。今天,您不应使用utf8mb3(每个字符最多 3 个字节)或其 utf8 别名(已弃用,因此以后可以将其更改为表示 utf8mb4)。使用 UTF-8 时,始终选择 4 字节变体,因为 3 字节变体的好处不大,并且已被弃用。在 MySQL 5.7 和更早版本中,拉丁文 1 是默认字符集,但随着 MySQL 8 中 UTF-8 的改进,建议使用 utf8mb4,除非您有特定的理由选择另一个字符集。
utf8mb4_0900_ai_ci排序规则是 utf8mb4 的通用排序规则。排序规则定义排序和比较规则,因此当您比较两个字符串时,它们会正确比较。规则可能相当复杂,包括某些字符序列与其他单个字符比较相等(例如,德语锐化度与某些排序规则的”ss”相同)。排序规则名称由几个部分组成,这些部分
- utf8mb4:归类所属的字符集。
- 0900:这意味着排序规则是基于Unicode排序算法(UCA)9.0.0的排序规则之一。 这些是MySQL 8中引入的,与旧的UTF-8归类相比,它们提供了显着的性能改进。
- ai:归类可以是不区分重音(ai)或不区分重音(as)。 当排序规则对重音不敏感时,将诸如à之类的重音字符视为等于非重音字符a。 在这种情况下,它对重音不敏感。
- ci:排序规则可以区分大小写(ci)或区分大小写(cs)。 在这种情况下,不区分大小写。
名称也可以包括其他部分,其他字符集具有其他字符组合。特别是,有几个针对具体国家/地区的性格集要考虑当地人的排序和比较规则;对于这些,国家/地区代码将添加到名称中。建议使用 UCA 9.0.0 排序规则之一,因为这些排序规则性能更好,并且比其他排序规则更现代。information_schema。Collations 视图包括 MySQL 支持的所有排序规则,支持按字符集进行筛选。截至 8.0.18,有 75 个排序规则可用于 utf8mb4,其中 49 个是 UCA 9.0.0 排序规则。
提示 字符集和排序规则本身是一个庞大而有趣的主题。如果您想进一步深入探讨该主题,一个起点是本书作者的以下博客及其引用:https://mysql.wisborg.dk/mysql-8_charset。
一种特殊的字符串是 JSON 文档。MySQL 具有专用数据类型。
JSON数据类型
与关系表相比,使用更灵活的存储数据的流行格式是JavaScript对象表示法(JSON)格式。这也是为 MySQL 8 中可用的 MySQL 文档存储选择的格式。MySQL 5.7 中引入了对 json 数据类型的支持。
JSON 文档是 JSON 对象(键和值)、JSON 数组和 JSON 值的组合。JSON 文档的简单示例可如下所示:
{
"name": "Sydney",
"demographics": {
"population": 5500000
},
"geography": {
"country": "Australia",
"state": "NSW"
},
"suburbs": [
"The Rocks",
"Surry Hills",
"Paramatta"
]
}
由于 JSON 文档也是字符串(或二进制对象),它也可以存储在占星或二进制对象列中。但是,通过具有专用数据类型,可以添加验证,并且存储已针对访问文档中的特定元素进行了优化。
MySQL 8 中 JSON 文档的一个出色的性能相关功能是支持部分更新。这使得更改不仅减少了更新期间完成的量,而且可能只写入二进制日志的部分更改。有一些要求,部分就地更新是可以的。如下所示:
- 仅支持JSON_SET( ),JSON_REPLACE( )和JSON_REMOVE( )函数。
- 仅支持列内的更新。 也就是说,不支持将列设置为在另一列上工作的三个JSON函数之一的返回值。
- 它必须是已替换的现有值。 添加新的对象或数组元素将导致整个文档被重写。
- 新值最多必须与替换的值大小相同。 例外情况是可以重用先前部分更新释放的空间。
为了将二进制日志的部分更新记录为部分更新,您需要将”binlog_row_value_options”选项PARTIAL_JSON。该选项可以在会话和全局级别动态设置。
在内部,文档存储为长二进制对象(longblob),其文本使用 utf8mb4 字符集进行解释。最大存储限制为 1GiB。存储要求与长球要求类似,但有必要将元数据和用于查找的字典的开销考虑在内。
到目前为止,已涵盖数字、时态数据、字符串、二进制对象和 JSON 文档。指定空间中点的数据如何?这是要涵盖的下一类数据类型。
空间数据类型
空间数据指定坐标系中的一个或多个点,可能形成对象(如多边形)。这很有用,例如,用于指定地图上项目的位置。
MySQL 8 添加了用于指定使用哪个参考系统的支持;这称为空间参考系统标识符 (SRID)。支持的参考系统可以在information_schema。ST_SPATIAL_REFERENCE_SYSTEMS视图(SRS_IDcolumn具有用于 SRID 的值);有5000多个可供选择。每个空间值都有一个与其关联的参考系统,以便 MySQL 能够正确识别两个值之间的关系,例如,计算两个点之间的距离。要使用地球作为参考系统,请将SRID设置为 4326。
支持八种不同的空间数据类型,其中四种是单值类型,四种是值集合。表 13-4 总结了以字节为单位的已查询存储的空间类型。
MySQL 使用二进制格式来存储数据。网格学、多线线线线、多多边和几何集合类型的存储要求取决于值中包含的对象的大小。这些对象集合的存储比将对象存储在单个列中稍大一点。您可以使用 LENGTH( ) 函数获取空间对象的大小,然后添加 4 个字节来存储 SRID 以获得数据所需的总存储。
这留下了一个要讨论的数据类型类别:数字和字符串数据类型之间的混合。
混合数据类型
有两种特殊数据类型组合了整数和字符串的属性:即”环境”集。两者都可以被视为可能值的集合,其区别在于,即环境数据类型允许您完全选择其中一个可能的值,而集数据类型允许您选择任何可能的值。
使项数和集数据类型混合的,是您可以将它们用作集合和字符串。后者是最常见和最用户友好的。在内部,这些值以整数存储,提供紧凑高效的存储,同时仍然允许在设置或查询列时使用字符串。使用查找表实现这两种数据类型可以作为替代方法实现。
“项”数据类型是两者中最常用的类型。创建列时,可以指定允许的值列表,例如:
CREATE TABLE t1 (
id int unsigned NOT NULL PRIMARY KEY,
val enum('Sydney', 'Melbourne', 'Brisbane')
);
数值是列表中以 1 开始的位置。也就是说,悉尼具有整数值 1、墨尔本 2 和布里斯班 3。根据列表中的成员数,总存储要求只有 1 或 2 字节,并且最多支持 65535 个成员。
集数据类型的工作方式与”已下”类似,但您可以选择多个选项。若要创建它,请列出要可用的成员,例如:
CREATE TABLE t1 (
id int unsigned NOT NULL PRIMARY KEY,
val set('Sydney', 'Melbourne', 'Brisbane')
);
列表中的每个成员根据成员在列表中的位置获取系列 1、2、4、8 等中的数值。在示例中,悉尼的值为 1、墨尔本 2 和布里斯班 4。然后值 3 代表什么?是悉尼和墨尔本。 如果你想包括多个值, 你总结他们的个人值。这样,集数据类型的工作方式与位类型相同。将值指定为占星值时更简单,因为在逗号分隔列表中包括该值的成员。清单 13-2 显示了两个插入集值的示例,每个示例都使用数值和字符串值插入相同的值。
Listing 13-2. Working with set values
mysql> INSERT INTO t1
VALUES (1, 4),
(2, 'Brisbane');
Query OK, 2 rows affected (0.0812 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1
VALUES (3, 7),
(4, 'Sydney,Melbourne,Brisbane');
Query OK, 2 rows affected (0.0919 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT *
FROM t1\G
*************************** 1. row ***************************
id: 1
val: Brisbane
*************************** 2. row ***************************
id: 2
val: Brisbane
*************************** 3. row ***************************
id: 3
val: Brisbane,Melbourne,Sydney
*************************** 4. row ***************************
id: 4
val: Brisbane,Melbourne,Sydney
4 rows in set (0.0006 sec)
首先,插入”布里斯班”的值。由于它是集中的第三个元素,因此其数值为 4。然后插入悉尼、墨尔本和布里斯班的集。在这里,您需要求和 1、2 和 4。请注意,在 SELECT 查询中,元素的顺序与集合定义中的顺序不相同。
设置列使用 1、2、3、4 或 8 字节的存储,具体取决于集中成员的数量。一组最多可以有 64 个成员。
讨论可用数据类型到此结束。数据类型如何影响查询的性能?可能相当多, 所以这是值得考虑的。
Performance
数据类型的选择不仅在数据完整性和数据类型方面很重要,而且不同的数据类型具有不同的性能表现。本节将讨论比较数据类型时性能的变化。
通常,数据类型越好,执行得越好。整数具有最佳性能,浮点(近似值)紧随其后。十进制(精确)值的开销高于近似浮点值。二进制对象比文本字符串更性能,因为二进制对象没有字符集的开销。
当涉及到像 JSON 这样的数据类型时,您可能认为它的性能比使用二进制对象更糟糕,因为 JSON 文档具有一些存储开销,如本章前面所述。但是,正是此存储开销意味着 JSON 数据类型比存储与 Blob 相同的数据性能更好。开销由元数据和字典组成,这意味着访问数据的速度更快。此外,JSON 文档支持就地更新,而文本和 Blob 数据类型将替换整个对象,即使仅替换单个字符或字节。
在给定的数据类型系列(例如,int 与 bigint)中,较小的数据类型性能优于较大的数据类型;但是,在实践中,硬件寄存器中的对齐也有考虑因素,因此对于内存工作负载,差异可能可以忽略不计,甚至相反。
那么您应该使用哪些数据类型? 这是本章的最后主题。
您应该选择哪种数据类型?
本章的开头,讨论了如何将所有数据存储在字符串或二进制对象中以具有最大的灵活性,这看起来是个好主意。在本章的过程中,讨论了使用特定数据类型的好处,并在上一节中讨论了不同数据类型的性能。那么,您应该选择哪种数据类型呢?
您可以开始问自己一些需要存储在列中的数据的问题。问题示例如下:
- 数据的本机格式是什么?
- 最初可以期望多大的价值?
- 值的大小会随着时间增长吗? 如果是这样,多少,多少时间?
- 查询中多久检索一次数据?
- 您期望多少个独特的价值?
- 您需要索引值吗? 特别是它是表的主键吗?
- 您是否需要存储数据,或者是否可以通过其他表中的外键(使用整数引用列)获取数据?
应为需要存储的数据选择本机的数据类型。如果需要存储整数,请选择整数数据类型,通常为 int 或 bigint,具体取决于所需的值大小。如果要限制值,可以选择较小的整数类型;如果要限制值,可以选择较小的整数类型。例如,存储父母数据的表的子项数量不一定是大数目,但很小的子数就足够了。同样,如果要存储 JSONocuments,请使用 json 类型而不是长文本或长文本。
对于数据类型的大小,您需要同时考虑当前需要和未来需求。如果预计在很长时间内需要较大的值,最好马上选择较大的数据类型。这样可以节省以后更改表定义。但是,如果预期更改是几年后,最好现在使用较小的数据类型,并随着时间的推移重新评估您的需求。对于 varchar 和 varbinary,也可以更改位置的宽度,只要不更改存储字符串长度或字符集的字节数。
使用字符串和二进制对象时,还可以考虑将数据存储在单独的表中,并使用整数引用值。当您需要检索值时,这将添加一个联接;但是,如果您很少需要实际的字符串值,则保持主表较小,这或许是整体胜利。此方法的好处还取决于表中的行数以及查询行的方式;检索许多行的大型扫描比单行查找受益更多,使用 SELECT * 即使不需要所有列也比仅选择所需的列受益更多。
如果只有几个唯一的字符串值,则也值得考虑使用列名数据类型。它的工作方式类似于查找表,但保存联接,并允许您直接更新字符串值
对于非整数数字数据,您可以在精确的十进制数据类型和近似浮点和双数据类型之间进行选择。如果需要存储必须精确的数据(如货币值),应始终选择十进制数据类型。这也是要选择的类型,如果您需要进行相等和非相等比较。如果不需要精确数据,则浮点和双数据类型性能更好。
关于字符串值,则字符、varchar、微文本、文本、中文本和长文本数据类型需要字符集和排序规则。通常,使用基于 UCA 9.0.0+ 的排序规则之一(名称中与 0900 的排序)选择 utf8mb4 是值得推荐的。如果您utf8mb4_0900_ai_ci,则默认选项是一个不错的选择。拉丁语 1 的表现会稍好一些, 但很少能保证为不同需要使用不同的字符集的复杂性。UCA 9.0.0 排序规则也提供了比拉丁文 1 可用的排序更现代的排序规则。
当您需要决定允许的值大小时,请选择支持现在和近期所需的值的最小数据类型或模型。较小的数据类型还意味着用于行大小限制 (64 kiB) 的空间更少,并且更多的数据可以放入 InnoDB 页面。由于 InnoDB 缓冲池可以存储一定数量的页面,从而根据缓冲池和页面的大小,这反过来又意味着更多的数据可以放入缓冲池中,从而有助于减少磁盘 I/O。同时,请记住,优化也是关于了解何时优化得足够。不要花一些时间去剃掉几个字节,而为了在一年内完成一个昂贵的表重建。
最后要考虑的是该值是否包含在索引中。值越大,索引也变得越大。这是主键的特定问题。InnoDB 根据主键(如群集索引)组织数据,因此当您添加辅助索引时,主键将添加到索引末尾,将链接添加到行。此外,此数据组织意味着在一般单调增加的值中对主键执行最佳性能。如果具有主键的列随时间随机变化和/或它很大,则最好添加具有自动增量整数的虚拟列,并使用它作为主键。
索引本身就是一个重要而大的主题,将在下一章中讨论。
总结
本章介绍了数据类型的概念。使用数据类型有几个好处:数据验证、文档、优化的存储、性能和更正排序。
MySQL 支持多种数据类型,从字符串和空间对象的简单整数到复杂的 JSON 文档。讨论了每种数据类型,重点讨论了支持的值、支持的值的大小和所需的存储量。
本章的最后一部分讨论了数据类型如何影响性能以及如何确定为列选择哪种数据类型。这包括考虑是否对列编制索引,这也与数据类型之一有关:正确的排序。索引是一个非常重要的主题,事实上,下一章将介绍它们。