MySQL8-中文参考-四十五-

布客飞龙
39 阅读1小时+

MySQL8 中文参考（四十五）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

13.2.3 TIME 类型

原文：dev.mysql.com/doc/refman/8.0/en/time.html

MySQL 以*'hh:mm:ss'格式（对于较大的小时值为'hhh:mm:ss'*格式）检索和显示TIME值。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分可能很大，因为TIME类型不仅可以用于表示一天中的时间（必须小于 24 小时），还可以表示经过的时间或两个事件之间的时间间隔（可能远大于 24 小时，甚至为负）。

MySQL 以几种格式识别TIME值，其中一些可以包括高达微秒（6 位数字）精度的尾随小数秒部分。请参阅 Section 11.1.3, “Date and Time Literals”了解更多信息。有关 MySQL 中小数秒支持的信息，请参阅 Section 13.2.6, “Fractional Seconds in Time Values”。特别是，插入到TIME列中的值中的任何小数部分都将被存储而不是丢弃。包括小数部分后，TIME值的范围为'-838:59:59.000000'到'838:59:59.000000'。

在为TIME列分配缩写值时要小心。MySQL 将带有冒号的缩写TIME值解释为当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。MySQL 解释不带冒号的缩写值时，假设最右边的两位数字代表秒数（即经过的时间而不是当天的时间）。例如，你可能认为'1112'和1112表示'11:12:00'（11 点 12 分后 12 分钟），但 MySQL 将其解释为'00:11:12'（11 分钟 12 秒）。同样，'12'和12被解释为'00:00:12'。

时间部分和小数秒部分之间唯一识别的分隔符是小数点。

默认情况下，超出TIME范围但仍然有效的值将被剪切到范围的最近端点。例如，'-850:00:00'和'850:00:00'将转换为'-838:59:59'和'838:59:59'。无效的TIME值将转换为'00:00:00'。请注意，因为'00:00:00'本身是一个有效的TIME值，所以无法从存储在表中的'00:00:00'的值中得知原始值是指定为'00:00:00'还是无效的。

要对无效的TIME值进行更严格的处理，请启用严格的 SQL 模式以导致错误发生。请参阅 Section 7.1.11, “Server SQL Modes”。

13.2.4 年份类型

原文：dev.mysql.com/doc/refman/8.0/en/year.html

YEAR类型是用于表示年份值的 1 字节类型。它可以声明为带有 4 个字符的隐式显示宽度的YEAR，或者等效地声明为带有显式显示宽度的YEAR(4)。

注意

截至 MySQL 8.0.19，带有显式显示宽度的YEAR(4)数据类型已被弃用，您应该期望在未来的 MySQL 版本中删除对其的支持。取而代之的是，使用没有显示宽度的YEAR，其含义相同。

MySQL 8.0 不支持在旧版本 MySQL 中允许的 2 位YEAR(2)数据类型。有关转换为 4 位YEAR的说明，请参阅 2 位 YEAR(2)的限制和迁移到 4 位 YEAR，在 MySQL 5.7 参考手册中。

MySQL 以*YYYY*格式显示YEAR值，范围为1901到2155，以及0000。

YEAR接受各种格式的输入值：

  • 作为范围在'1901'到'2155'之间的 4 位数字字符串。

  • 作为范围在1901到2155之间的 4 位数字。

  • 作为范围在'0'到'99'之间的 1 位或 2 位字符串。MySQL 将范围在'0'到'69'和'70'到'99'之间的值转换为范围在2000到2069和1970到1999之间的YEAR值。

  • 作为范围在0到99之间的 1 位或 2 位数字。MySQL 将范围在1到69和70到99之间的值转换为范围在2001到2069和1970到1999之间的YEAR值。

    插入数字0的结果显示值为0000，内部值为0000。要插入零并将其解释为2000，请将其指定为字符串'0'或'00'。

  • 作为返回在YEAR上下文中可接受值的函数的结果，例如NOW()。

如果未启用严格的 SQL 模式，MySQL 会将无效的YEAR值转换为0000。在严格的 SQL 模式下，尝试插入无效的YEAR值会产生错误。

另请参阅第 13.2.9 节，“日期中的 2 位年份”。

13.2.5 TIMESTAMP 和 DATETIME 的自动初始化和更新

原文：dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间（即当前时间戳）。

对于表中的任何 TIMESTAMP 或 DATETIME 列，您可以将当前时间戳分配为默认值、自动更新值或两者：

  • 自动初始化列对于未为该列指定值的插入行设置为当前时间戳。

  • 自动更新列在行中的任何其他列的值从当前值更改时，将自动更新为当前时间戳。如果所有其他列都设置为它们的当前值，则自动更新列保持不变。要防止自动更新列在其他列更改时更新，请明确将其设置为当前值。即使其他列不更改，也要更新自动更新列，请明确将其设置为应具有的值（例如，将其设置为CURRENT_TIMESTAMP）。

此外，如果禁用了 explicit_defaults_for_timestamp 系统变量，则可以通过将其分配为 NULL 值来将任何 TIMESTAMP（但不是 DATETIME）列初始化或更新为当前日期和时间，除非已使用 NULL 属性定义了它以允许 NULL 值。

要指定自动属性，请在列定义中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。子句的顺序无关紧要。如果列定义中同时存在两者，则任何一个都可以先出现。任何 CURRENT_TIMESTAMP 的同义词都与 CURRENT_TIMESTAMP 具有相同的含义。这些同义词包括 CURRENT_TIMESTAMP()、NOW()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP 和 LOCALTIMESTAMP()。

使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP是特定于TIMESTAMP 和 DATETIME 的。DEFAULT子句也可以用于指定常量（非自动）默认值（例如，DEFAULT 0 或 DEFAULT '2000-01-01 00:00:00'）。

注意

以下示例使用DEFAULT 0，这是一个可能会产生警告或错误的默认值，具体取决于是否启用了严格的 SQL 模式或 NO_ZERO_DATE SQL 模式。请注意，TRADITIONAL SQL 模式包括严格模式和 NO_ZERO_DATE。请参阅 第 7.1.11 节，“服务器 SQL 模式”。

TIMESTAMP 或 DATETIME 列定义可以为默认值和自动更新值同时指定当前时间戳，其中一个指定当前时间戳，或者两者都不指定。不同列可以具有不同的自动属性组合。以下规则描述了可能性：

  • 同时使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP，列的默认值为当前时间戳，并且会自动更新为当前时间戳。

    CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

  • 使用DEFAULT子句但没有ON UPDATE CURRENT_TIMESTAMP子句，列具有给定的默认值，不会自动更新为当前时间戳。

    默认值取决于DEFAULT子句是否指定为CURRENT_TIMESTAMP或常量值。使用CURRENT_TIMESTAMP时，默认值为当前时间戳。

    CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

    使用常量时，默认值为给定值。在这种情况下，列根本没有自动属性。

    CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);

  • 使用ON UPDATE CURRENT_TIMESTAMP子句和常量DEFAULT子句，列会自动更新为当前时间戳，并具有给定的常量默认值。

    CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

  • 使用ON UPDATE CURRENT_TIMESTAMP子句但没有DEFAULT子句，列会自动更新为当前时间戳，但不具有当前时间戳作为默认值。

    在这种情况下，默认值取决于类型。TIMESTAMP 的默认值为 0，除非使用NULL属性定义，此时默认值为NULL。

    CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

    DATETIME 的默认值为NULL，除非使用NOT NULL属性定义，此时默认值为 0。

    CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

时间戳 和 日期时间 列除非明确指定，否则没有自动属性，有一个例外：如果禁用了 explicit_defaults_for_timestamp 系统变量，则第一个 时间戳 列如果没有明确指定，则同时具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP。要抑制第一个 时间戳 列的自动属性，可以使用以下策略之一：

  • 启用 explicit_defaults_for_timestamp 系统变量。在这种情况下，指定自动初始化和更新的 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句是可用的，但除非明确包含在列定义中，否则不会分配给任何 时间戳 列。

  • 或者，如果禁用了 explicit_defaults_for_timestamp，则执行以下操作之一：

    • 使用指定常量默认值的 DEFAULT 子句定义列。

    • 指定 NULL 属性。这也会导致列允许 NULL 值，这意味着您不能通过将列设置为 NULL 来分配当前时间戳。将 NULL 分配给列会将列设置为 NULL，而不是当前时间戳。要分配当前时间戳，请将列设置为 CURRENT_TIMESTAMP 或类似 NOW()。

考虑以下表定义：

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
  ts1 TIMESTAMP NULL,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
  ts1 TIMESTAMP NULL DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);

这些表具有以下属性：

  • 在每个表定义中，第一个 时间戳 列没有自动初始化或更新。

  • 这些表在处理 NULL 值的 ts1 列上有所不同。对于 t1，ts1 是 NOT NULL，将其赋值为 NULL 会将其设置为当前时间戳。对于 t2 和 t3，ts1 允许 NULL，将其赋值为 NULL 会将其设置为 NULL。

  • t2 和 t3 在 ts1 的默认值上有所不同。对于 t2，ts1 被定义为允许 NULL，因此在没有明确的 DEFAULT 子句的情况下，默认值也为 NULL。对于 t3，ts1 允许 NULL 但具有明确的默认值为 0。

如果 时间戳 或 日期时间 列定义在任何地方包含明确的小数秒精度值，则必须在整个列定义中使用相同的值。这是允许的：

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

这是不允许的：

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

时间戳初始化和 NULL 属性

如果禁用了explicit_defaults_for_timestamp系统变量，则默认情况下TIMESTAMP列为NOT NULL，不能包含NULL值，并且将NULL分配给当前时间戳。要允许TIMESTAMP列包含NULL，请明确声明具有NULL属性。在这种情况下，默认值也变为NULL，除非使用指定不同默认值的DEFAULT子句覆盖。DEFAULT NULL可用于明确指定NULL作为默认值。（对于未声明具有NULL属性的TIMESTAMP列，DEFAULT NULL是无效的。）如果TIMESTAMP列允许NULL值，分配NULL会将其设置为NULL，而不是当前时间戳。

以下表包含几个允许NULL值的TIMESTAMP列：

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

允许NULL值的TIMESTAMP列在插入时不会自动获取当前时间戳，除非满足以下条件之一：

  • 其默认值被定义为CURRENT_TIMESTAMP，并且未为列指定任何值。

  • CURRENT_TIMESTAMP或其任何同义词，如NOW()，被明确插入到列中。

换句话说，只有在允许NULL值的TIMESTAMP列的定义中包含DEFAULT CURRENT_TIMESTAMP时，才会自动初始化：

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

如果TIMESTAMP列允许NULL值，但其定义不包括DEFAULT CURRENT_TIMESTAMP，则必须明确插入与当前日期和时间对应的值。假设表t1和t2具有以下定义：

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);

要在任一表中的TIMESTAMP列设置为插入时的当前时间戳，请明确地分配该值。例如：

INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (NOW());

如果启用了explicit_defaults_for_timestamp系统变量，则TIMESTAMP列只允许NULL值，如果声明了NULL属性。此外，TIMESTAMP列不允许将NULL分配给当前时间戳，无论是否声明了NULL或NOT NULL属性。要分配当前时间戳，请将列设置为CURRENT_TIMESTAMP或类似的同义词，如NOW()。

13.2.6 时间值中的分数秒

原文：dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

MySQL 支持TIME、DATETIME和TIMESTAMP值的分数秒，精度可达微秒（6 位数）：

  • 要定义包含分数秒部分的列，请使用*type_name*(*fsp*)的语法，其中*type_name是TIME、DATETIME或TIMESTAMP，fsp*是分数秒精度。例如：

    CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

    如果给出*fsp*值，则必须在 0 到 6 的范围内。值为 0 表示没有分数部分。如果省略，则默认精度为 0。（这与标准 SQL 默认值 6 不同，以与以前的 MySQL 版本兼容。）

  • 将带有分数秒部分的TIME、DATE或TIMESTAMP值插入到具有较少分数位数但类型相同的列中会导致四舍五入。考虑以下创建和填充的表：

    CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

    时间值将被插入到表中并进行四舍五入：

    mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+

    当发生此类四舍五入时，不会发出警告或错误。此行为遵循 SQL 标准。

    要进行截断插入值，需启用TIME_TRUNCATE_FRACTIONAL SQL 模式：

    SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

    启用该 SQL 模式后，时间值将被截断插入：

    mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 |
+-------------+------------------------+------------------------+

  • 接受时间参数的函数接受带有分数秒的值。从时间函数返回的值将根据需要包含分数秒。例如，不带参数的NOW()返回当前日期和时间，不包含分数部分，但可以使用 0 到 6 的可选参数指定返回值包含指定位数的分数秒部分。

  • 时间文字的语法生成时间值：DATE '*str*'、TIME '*str*'和TIMESTAMP '*str*'，以及 ODBC 语法的等效形式。如果指定，生成的值将包含尾随的分数秒部分。以前，时间类型关键字被忽略，这些构造产生字符串值。参见标准 SQL 和 ODBC 日期和时间文字

13.2.7 MySQL 使用哪种日历？

原文：dev.mysql.com/doc/refman/8.0/en/mysql-calendar.html

MySQL 使用的是所谓的推测格里高利历。

每个从儒略历转换为格里高利历的国家都必须在转换过程中丢弃至少十天。要了解这是如何运作的，请考虑 1582 年 10 月，当第一次儒略历到格里高利历的转换发生时。

星期一星期二星期三星期四星期五星期六星期日
1234151617
18192021222324
25262728293031

10 月 4 日至 10 月 15 日之间没有日期。这种不连续称为转换。转换前的任何日期都是儒略历，转换后的日期都是格里高利历。转换期间的日期是不存在的。

在实际未使用的日期上应用的日历称为推测日历。因此，如果我们假设从未有过转换，格里高利规则始终适用，我们就有了一个推测格里高利历。这是 MySQL 使用的，也是标准 SQL 所要求的。因此，存储为 MySQL DATE 或 DATETIME 值的转换前日期必须进行调整以弥补差异。重要的是要意识到，转换并不是在所有国家同时发生的，而且发生得越晚，丢失的天数就越多。例如，在英国，这发生在 1752 年，当周三 9 月 2 日之后是周四 9 月 14 日。俄罗斯一直使用儒略历直到 1918 年，这个过程中丢失了 13 天，根据格里高利历，被普遍称为“十月革命”的事件实际发生在 11 月。

13.2.8 日期和时间类型之间的转换

原文：dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html

在一定程度上，您可以将一个时间类型的值转换为另一个时间类型。但是，可能会对值进行一些更改或丢失信息。在所有情况下，时间类型之间的转换受到生成类型的有效值范围的限制。例如，尽管DATE、DATETIME和TIMESTAMP值都可以使用相同的格式集来指定，但这些类型的值范围并不相同。TIMESTAMP值不能早于1970年 UTC 或晚于'2038-01-19 03:14:07' UTC。这意味着像'1968-01-01'这样的日期，虽然作为DATE或DATETIME值是有效的，但作为TIMESTAMP值是无效的，并转换为0。

转换DATE值：

  • 转换为DATETIME或TIMESTAMP值会添加一个时间部分'00:00:00'，因为DATE值不包含时间信息。

  • 转换为TIME值没有用处；结果为'00:00:00'。

转换DATETIME和TIMESTAMP值：

  • 转换为DATE值考虑了小数秒并四舍五入时间部分。例如，'1999-12-31 23:59:59.499'变为'1999-12-31'，而'1999-12-31 23:59:59.500'变为'2000-01-01'。

  • 转换为TIME值会丢弃日期部分，因为TIME类型不包含日期信息。

对于将TIME值转换为其他时间类型，日期部分使用CURRENT_DATE()的值。TIME被解释为经过的时间（而不是一天中的时间）并添加到日期上。这意味着结果的日期部分与当前日期不同，如果时间值超出范围从'00:00:00'到'23:59:59'。

假设当前日期为'2012-01-01'。当TIME值为'12:00:00'，'24:00:00'和'-12:00:00'时，转换为DATETIME或TIMESTAMP值，结果分别为'2012-01-01 12:00:00'，'2012-01-02 00:00:00'和'2011-12-31 12:00:00'。

将TIME转换为DATE类似，但从结果中丢弃时间部分：分别为'2012-01-01'，'2012-01-02'和'2011-12-31'。

显式转换可用于覆盖隐式转换。例如，在比较DATE和DATETIME值时，DATE值通过添加时间部分'00:00:00'被强制转换为DATETIME类型。要通过忽略DATETIME值的时间部分执行比较，可以使用CAST()函数如下方式：

*date_col* = CAST(*datetime_col* AS DATE)

将TIME和DATETIME值转换为数值形式（例如，通过添加+0）取决于值是否包含小数秒部分。当N为 0（或省略）时，TIME(*N*)或DATETIME(*N*)转换为整数，当N大于 0 时，转换为具有N位小数位的DECIMAL值：

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------+-------------+--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------+-------------+--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------+-------------+--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+

13.2.9 日期中的 2 位数年份

原文：dev.mysql.com/doc/refman/8.0/en/two-digit-years.html

具有 2 位数年份的日期值是模糊的，因为世纪未知。这些值必须被解释为 4 位数形式，因为 MySQL 在内部使用 4 位数存储年份。

对于DATETIME、DATE和TIMESTAMP类型，MySQL 使用以下规则解释具有模糊年份值的日期：

  • 范围在00-69之间的年份值变为2000-2069。

  • 范围在70-99之间的年份值变为1970-1999。

对于YEAR，规则相同，但有一个例外：将数字00插入YEAR会导致0000而不是2000。要为YEAR指定零并将其解释为2000，请将其指定为字符串'0'或'00'。

请记住，这些规则只是提供合理猜测的启发式规则，以确定数据值的含义。如果 MySQL 使用的规则无法产生所需的值，您必须提供包含 4 位数年份值的明确输入。

ORDER BY可以正确排序具有 2 位数年份的YEAR值。

一些函数，如MIN()和MAX()，将YEAR转换为数字。这意味着具有 2 位数年份的值无法与这些函数正常工作。在这种情况下的修复方法是将YEAR转换为 4 位数年份格式。

13.3 字符串数据类型

原文：dev.mysql.com/doc/refman/8.0/en/string-types.html

13.3.1 字符串数据类型语法

13.3.2 CHAR 和 VARCHAR 类型

13.3.3 BINARY 和 VARBINARY 类型

13.3.4 BLOB 和 TEXT 类型

13.3.5 ENUM 类型

13.3.6 SET 类型

字符串数据类型包括 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, 和 SET.

有关字符串数据类型的存储要求，请参阅 第 13.7 节，“数据类型存储要求”。

关于操作字符串值的函数描述，请参阅 第 14.8 节，“字符串函数和运算符”。

13.3.1 字符串数据类型语法

原文：dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html

字符串数据类型包括CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, 和 SET。

在某些情况下，MySQL 可能会将字符串列更改为与CREATE TABLE或ALTER TABLE语句中给出的类型不同的类型。请参阅第 15.1.20.7 节，“静默列规范更改”。

对于字符串列的定义(CHAR, VARCHAR, 和 TEXT类型), MySQL 以字符单位解释长度规范。对于二进制字符串列的定义(BINARY, VARBINARY, 和 BLOB类型), MySQL 以字节单位解释长度规范。

字符串数据类型CHAR, VARCHAR, TEXT类型, ENUM, SET, 和任何同义词的列定义可以指定列字符集和排序规则：

  • CHARACTER SET指定字符集。如果需要，可以使用COLLATE属性指定字符集的排序规则，以及任何其他属性。例如：

    CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8mb4,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

    此表定义创建一个名为c1的列，其字符集为utf8mb4，具有该字符集的默认排序规则，并创建一个名为c2的列，其字符集为latin1，具有区分大小写(_cs)的排序规则。

    当CHARACTER SET和COLLATE属性中的一个或两个缺失时，关于分配字符集和排序规则的规则在第 12.3.5 节，“列字符集和排序”中描述。

    CHARSET是CHARACTER SET的同义词。

  • 为字符串数据类型指定 CHARACTER SET binary 属性会导致该列被创建为相应的二进制字符串数据类型：CHAR 变为 BINARY，VARCHAR 变为 VARBINARY，TEXT 变为 BLOB。对于 ENUM 和 SET 数据类型，不会发生这种情况；它们将按声明创建。假设您使用以下定义指定一个表：

    CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET binary,
  c2 TEXT CHARACTER SET binary,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

    结果表的定义如下：

    CREATE TABLE t
(
  c1 VARBINARY(10),
  c2 BLOB,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

  • BINARY 属性是 MySQL 的非标准扩展，是指定列字符集的二进制 (_bin) 校对的简写（如果未指定列字符集，则是表默认字符集的二进制校对）。在这种情况下，比较和排序是基于数字字符代码值的。假设您使用以下定义指定一个表：

    CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET latin1 BINARY,
  c2 TEXT BINARY
) CHARACTER SET utf8mb4;

    结果表的定义如下：

    CREATE TABLE t (
  c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,
  c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) CHARACTER SET utf8mb4;

    在 MySQL 8.0 中，BINARY 属性的这种非标准用法是模棱两可的，因为 utf8mb4 字符集有多个 _bin 校对。从 MySQL 8.0.17 开始，BINARY 属性已被弃用，您应该期望在将来的 MySQL 版本中删除对其的支持。应用程序应调整为使用显式的 _bin 校对。

    使用 BINARY 指定数据类型或字符集的用法保持不变。

  • ASCII 属性是 CHARACTER SET latin1 的简写。在较旧的 MySQL 版本中受支持，但在 MySQL 8.0.28 及更高版本中已被弃用；请使用 CHARACTER SET 替代。

  • UNICODE 属性是 CHARACTER SET ucs2 的简写。在较旧的 MySQL 版本中受支持，但在 MySQL 8.0.28 及更高版本中已被弃用；请使用 CHARACTER SET 替代。

字符列的比较和排序基于分配给列的校对规则。对于 CHAR、VARCHAR、TEXT、ENUM 和 SET 数据类型，您可以声明一个带有二进制 (_bin) 校对或 BINARY 属性的列，以使比较和排序使用底层字符代码值而不是词法顺序。

有关 MySQL 中字符集使用的更多信息，请参阅 第十二章，字符集、校对规则、Unicode。

  • [NATIONAL] CHAR[(*M*)] [CHARACTER SET *charset_name*] [COLLATE *collation_name*]

    固定长度的字符串，在存储时总是用空格右填充到指定长度。M代表列长度（以字符为单位）。M的范围是 0 到 255。如果省略M，长度为 1。

    注意

    当检索CHAR值时，尾随空格会被移除，除非启用了PAD_CHAR_TO_FULL_LENGTH SQL 模式。

    CHAR是CHARACTER的简写。NATIONAL CHAR（或其等效的简写形式，NCHAR）是定义CHAR列应使用某个预定义字符集的标准 SQL 方式。MySQL 使用utf8mb3作为这个预定义字符集。第 12.3.7 节，“国家字符集”。

    CHAR BYTE数据类型是BINARY数据类型的别名。这是一个兼容性特性。

    MySQL 允许创建类型为CHAR(0)的列。这在必须符合依赖于列存在但实际上不使用其值的旧应用程序时非常有用。当您需要一个只能取两个值的列时，CHAR(0)也非常好：定义为CHAR(0) NULL的列仅占用一个位，并且只能取NULL和''（空字符串）这两个值。

  • [NATIONAL] VARCHAR(*M*) [字符集 *charset_name*] [校对 *collation_name*]

    可变长度字符串。*M表示最大列长度（以字符为单位）。M*的范围是 0 到 65,535。VARCHAR的有效最大长度取决于最大行大小（65,535 字节，在所有列之间共享）和所使用的字符集。例如，utf8mb3字符可能每个字符需要最多三个字节，因此使用utf8mb3字符集的VARCHAR列最大可以声明为 21,844 个字符。参见第 10.4.7 节，“表列计数和行大小限制”。

    MySQL 将VARCHAR值存储为 1 字节或 2 字节长度前缀加数据。长度前缀指示值中的字节数。如果值不超过 255 字节，则VARCHAR列使用一个长度字节，如果值可能超过 255 字节，则使用两个长度字节。

    注意

    MySQL 遵循标准 SQL 规范，不会从 VARCHAR 值中删除尾随空格。

    VARCHAR 是 CHARACTER VARYING 的简写。NATIONAL VARCHAR 是定义 VARCHAR 列应使用某个预定义字符集的标准 SQL 方式。MySQL 使用 utf8mb3 作为这个预定义字符集。第 12.3.7 节，“国家字符集”。NVARCHAR 是 NATIONAL VARCHAR 的简写。

  • BINARY[(*M*)]

    BINARY 类型类似于 CHAR 类型，但存储的是二进制字节字符串而不是非二进制字符字符串。可选长度 M 表示列长度（以字节为单位）。如果省略，M 默认为 1。

  • VARBINARY(*M*)

    VARBINARY 类型类似于 VARCHAR 类型，但存储的是二进制字节字符串而不是非二进制字符字符串。 M 表示最大列长度（以字节为单位）。

  • TINYBLOB

    最大长度为 255（2⁸ − 1）字节的 BLOB 列。每个 TINYBLOB 值都使用一个 1 字节长度前缀来存储，指示值中的字节数。

  • TINYTEXT [字符集 *charset_name*] [校对 *collation_name*]

    最大长度为 255（2⁸ − 1）个字符的 TEXT 列。如果值包含多字节字符，则有效最大长度会减少。每个 TINYTEXT 值都使用一个 1 字节长度前缀来存储，指示值中的字节数。

  • BLOB[(*M*)]

    最大长度为 65,535（2¹⁶ − 1）字节的 BLOB 列。每个 BLOB 值都使用一个 2 字节长度前缀来存储，指示值中的字节数。

    可以为此类型指定可选长度 M。如果这样做，MySQL 将创建一个足以容纳 M 字节长值的最小 BLOB 类型列。

  • TEXT[(*M*)] [字符集 *charset_name*] [校对 *collation_name*]

    一个最大长度为 65,535（2¹⁶ − 1）个字符的TEXT列。如果值包含多字节字符，则有效最大长度会更少。每个TEXT值都使用一个 2 字节长度前缀来指示值中的字节数。

    可以为此类型指定一个可选长度 M。如果这样做，MySQL 会创建一个足以容纳 M 个字符长的最小TEXT类型列。

  • MEDIUMBLOB

    一个最大长度为 16,777,215（2²⁴ − 1）字节的BLOB列。每个MEDIUMBLOB值都使用一个 3 字节长度前缀来指示值中的字节数。

  • MEDIUMTEXT [字符集 *charset_name*] [校对 *collation_name*]

    一个最大长度为 16,777,215（2²⁴ − 1）个字符的TEXT列。如果值包含多字节字符，则有效最大长度会更少。每个MEDIUMTEXT值都使用一个 3 字节长度前缀来指示值中的字节数。

  • LONGBLOB

    一个最大长度为 4,294,967,295 或 4GB（2³² − 1）字节的BLOB列。LONGBLOB列的有效最大长度取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个LONGBLOB值都使用一个 4 字节长度前缀来指示值中的字节数。

  • LONGTEXT [字符集 *charset_name*] [校对 *collation_name*]

    一个最大长度为 4,294,967,295 或 4GB（2³² − 1）个字符的TEXT列。如果值包含多字节字符，则有效最大长度会更少。LONGTEXT列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个LONGTEXT值都使用一个 4 字节长度前缀来指示值中的字节数。

  • ENUM('*value1*','*value2*',...) [字符集 *charset_name*] [校对 *collation_name*]

    一个枚举。一个字符串对象，只能有一个值，从值列表'*value1*'，'*value2*'，...，NULL或特殊的''错误值中选择。ENUM值在内部表示为整数。

    ENUM列最多可以有 65,535 个不同元素。

    单个ENUM元素的最大支持长度为*M* <= 255，且(M x w) <= 1020，其中M是元素文字长度，*w*是字符集中最大长度字符所需的字节数。

  • SET('*value1*','*value2*',...) [字符集 *charset_name*] [校对 *collation_name*]

    一个集合。一个字符串对象，可以有零个或多个值，每个值必须从值列表'*value1*'，'*value2*'，... SET中选择的值内部表示为整数。

    SET列最多可以有 64 个不同成员。

    单个SET元素的最大支持长度为*M* <= 255，且(M x w) <= 1020，其中M是元素文字长度，*w*是字符集中最大长度字符所需的字节数。

13.3.2 CHAR 和 VARCHAR 类型

原文：dev.mysql.com/doc/refman/8.0/en/char.html

CHAR和VARCHAR类型相似，但在存储和检索方式、最大长度以及是否保留尾随空格方面有所不同。

CHAR和VARCHAR类型声明的长度指示您希望存储的最大字符数。例如，CHAR(30)可以容纳最多 30 个字符。

CHAR列的长度在创建表时声明为固定长度。长度可以是 0 到 255 之间的任何值。存储CHAR值时，它们会右填充空格以达到指定长度。检索CHAR值时，除非启用了PAD_CHAR_TO_FULL_LENGTH SQL 模式，否则会移除尾随空格。

VARCHAR列中的值是可变长度字符串。长度可以指定为 0 到 65,535 之间的值。VARCHAR的有效最大长度取决于最大行大小（65,535 字节，这些字节在所有列之间共享）和所使用的字符集。参见第 10.4.7 节，“表列计数和行大小限制”。

与CHAR相反，VARCHAR值存储为 1 字节或 2 字节长度前缀加数据。长度前缀指示值中的字节数。如果值不超过 255 字节，则列使用一个长度字节，如果值可能需要超过 255 字节，则使用两个长度字节。

如果未启用严格的 SQL 模式，并且将值分配给超出列最大长度的CHAR或VARCHAR列，则该值将被截断以适应，并生成警告。对于非空格字符的截断，您可以通过使用严格的 SQL 模式来导致错误发生（而不是警告），并抑制值的插入。参见第 7.1.11 节，“服务器 SQL 模式”。

对于VARCHAR列，在插入之前会截断超出列长度的尾随空格，并生成警告，无论使用的 SQL 模式如何。对于CHAR列，无论使用的 SQL 模式如何，插入值时都会静默执行超出尾随空格的截断。

存储VARCHAR值时不会填充。在存储和检索值时，尾随空格会被保留，符合标准 SQL。

以下表格通过展示将各种字符串值存储到CHAR(4)和VARCHAR(4)列中的结果（假设列使用单字节字符集如latin1）来说明CHAR和VARCHAR之间的差异。

值CHAR(4)存储空间VARCHAR(4)存储空间
''' '4 字节''1 字节
'ab''ab '4 字节'ab'3 字节
'abcd''abcd'4 字节'abcd'5 字节
'abcdefgh''abcd'4 字节'abcd'5 字节

在表的最后一行显示的值仅在不使用严格 SQL 模式时适用；如果启用了严格模式，超过列长度的值将不会被存储，并且会导致错误。

InnoDB将长度大于或等于 768 字节的固定长度字段编码为可变长度字段，可以存储在页外。例如，如果字符集的最大字节长度大于 3，如utf8mb4，则CHAR(255)列可以超过 768 字节。

如果给定值存储到CHAR(4)和VARCHAR(4)列中，从列中检索的值并不总是相同，因为从CHAR列中检索时会删除尾随空格。以下示例说明了这种差异：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

CHAR、VARCHAR和TEXT列中的值根据分配给列的字符集排序和比较。

MySQL 排序具有PAD SPACE填充属性，除了基于 UCA 9.0.0 及更高版本的 Unicode 排序具有NO PAD填充属性。 (参见第 12.10.1 节，“Unicode 字符集”).

要确定排序的填充属性，请使用INFORMATION_SCHEMA COLLATIONS表，该表具有一个PAD_ATTRIBUTE列。

对于非二进制字符串（CHAR、VARCHAR和TEXT值），字符串排序填充属性决定了在字符串末尾的尾随空格比较中的处理方式。NO PAD排序将尾随空格视为比较中的重要部分，就像任何其他字符一样。PAD SPACE排序将尾随空格视为比较中不重要的部分；字符串将在不考虑尾随空格的情况下进行比较。参见比较中的尾随空格处理。服务器 SQL 模式对于尾随空格的比较行为没有影响。

注意

有关 MySQL 字符集和排序的更多信息，请参见第十二章，“字符集、排序、Unicode”。有关存储要求的其他信息，请参见第 13.7 节，“数据类型存储要求”。

对于那些尾随填充字符被剥离或比较忽略它们的情况，如果一个列有一个需要唯一值的索引，插入到列中仅在尾随填充字符数量不同的值会导致重复键错误。例如，如果一个表包含'a'，尝试存储'a '会导致重复键错误。

13.3.3 BINARY 和 VARBINARY 类型

原文：dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，只是它们存储二进制字符串而不是非二进制字符串。也就是说，它们存储字节字符串而不是字符字符串。这意味着它们具有 binary 字符集和校对，比较和排序基于值中字节的数值。

BINARY 和 VARBINARY 的最大长度与 CHAR 和 VARCHAR 的最大长度相同，只是 BINARY 和 VARBINARY 的长度是以字节而不是字符计量的。

BINARY 和 VARBINARY 数据类型与 CHAR BINARY 和 VARCHAR BINARY 数据类型不同。对于后者，BINARY 属性不会导致列被视为二进制字符串列。相反，它会导致使用列字符集的二进制 (_bin) 校对（如果未指定列字符集，则使用表默认字符集），并且列本身存储非二进制字符字符串而不是二进制字节字符串。例如，如果默认字符集是 utf8mb4，CHAR(5) BINARY 被视为 CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin。这与 BINARY(5) 不同，后者存储具有 binary 字符集和校对的 5 字节二进制字符串。有关 binary 字符集的 binary 校对与非二进制字符集的 _bin 校对之间的差异的信息，请参见 Section 12.8.5, “The binary Collation Compared to _bin Collations”。

如果未启用严格的 SQL 模式，并且将值分配给超出列最大长度的 BINARY 或 VARBINARY 列，则该值将被截断以适应，并生成警告。对于截断的情况，要导致发生错误（而不是警告）并阻止插入该值，请使用严格的 SQL 模式。请参见 Section 7.1.11, “Server SQL Modes”。

存储 BINARY 值时，它们会使用填充值右填充到指定长度。填充值为 0x00（零字节）。对于插入，值会使用 0x00 右填充，检索时不会删除尾随字节。在比较中，所有字节都是重要的，包括 ORDER BY 和 DISTINCT 操作。0x00 和空格在比较中不同，0x00 排在空格之前。

例如：对于 BINARY(3) 列，'a ' 在插入时变为 'a \0'。'a\0' 在插入时变为 'a\0\0'。这两个插入值在检索时保持不变。

对于VARBINARY，插入时不进行填充，检索时不剥离任何字节。在比较中，所有字节都是重要的，包括ORDER BY和DISTINCT操作。在比较中，0x00和空格是不同的，0x00在排序中排在空格之前。

对于那些剥离尾随填充字节或比较忽略它们的情况，如果列具有需要唯一值的索引，那么在列中插入仅在尾随填充字节数量上不同的值会导致重复键错误。例如，如果表中包含'a'，尝试存储'a\0'会导致重复键错误。

如果您计划使用BINARY数据类型存储二进制数据，并且需要检索的值与存储的值完全相同，那么您应该仔细考虑前导填充和剥离特性。以下示例说明了BINARY值的0x00填充如何影响列值比较：

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+
1 row in set (0.09 sec)

如果检索的值必须与存储时指定的值完全相同且没有填充，则最好使用VARBINARY或BLOB数据类型之一。

注意

在mysql客户端中，二进制字符串使用十六进制表示，取决于--binary-as-hex选项的值。有关该选项的更多信息，请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。

13.3.4 BLOB 和 TEXT 类型

原文：dev.mysql.com/doc/refman/8.0/en/blob.html

BLOB是可以容纳可变数量数据的二进制大对象。四种BLOB类型分别是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们仅在可以容纳的值的最大长度上有所不同。四种TEXT类型是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些与四种BLOB类型对应，并具有相同的最大长度和存储要求。参见第 13.7 节，“数据类型存储要求”。

BLOB值被视为二进制字符串（字节字符串）。它们具有binary字符集和排序规则，比较和排序基于列值中字节的数值。TEXT值被视为非二进制字符串（字符字符串）。它们具有除binary之外的字符集，并且根据字符集的排序规则进行排序和比较。

如果未启用严格的 SQL 模式并且将值分配给超出列的最大长度的BLOB或TEXT列，则该值将被截断以适应并生成警告。对于非空格字符的截断，您可以通过使用严格的 SQL 模式导致错误发生（而不是警告）并抑制值的插入。参见第 7.1.11 节，“服务器 SQL 模式”。

要插入TEXT列的值中多余的尾随空格截断总是生成警告，无论 SQL 模式如何。

对于TEXT和BLOB列，在插入时不会填充，选择时也不会去除任何字节。

如果对TEXT列进行索引，索引条目比较在末尾填充空格。这意味着，如果索引需要唯一值，则对于仅在尾随空格数量上不同的值会导致重复键错误。例如，如果表包含'a'，则尝试存储'a '会导致重复键错误。对于BLOB列则不是这样。

在大多数方面，您可以将BLOB列视为可以任意大的VARBINARY列。同样，您可以将TEXT列视为VARCHAR列。BLOB和TEXT与VARBINARY和VARCHAR在以下方面有所不同：

  • 对于BLOB和TEXT列上的索引，必须指定索引前缀长度。对于CHAR和VARCHAR，前缀长度是可选的。参见第 10.3.5 节，“列索引”。

  • BLOB和TEXT列不能有DEFAULT值。

如果在TEXT数据类型中使用BINARY属性，则该列将被分配为列字符集的二进制(_bin)排序规则。

LONG和LONG VARCHAR映射到MEDIUMTEXT数据类型。这是一个兼容性特性。

MySQL Connector/ODBC 将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

因为BLOB和TEXT值可能非常长，所以在使用它们时可能会遇到一些限制：

  • 在排序时只使用列的前max_sort_length字节。max_sort_length的默认值为 1024。您可以通过在服务器启动或运行时增加max_sort_length的值来使更多字节在排序或分组中起作用。任何客户端都可以更改其会话max_sort_length变量的值：

    mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
 -> ORDER BY comment;

  • 在使用临时表处理的查询结果中存在BLOB或TEXT列的实例会导致服务器在磁盘上而不是内存中使用表，因为MEMORY存储引擎不支持这些数据类型（参见第 10.4.4 节，“MySQL 中的内部临时表使用”）。使用磁盘会导致性能损失，因此只有在真正需要时才在查询结果中包含BLOB或TEXT列。例如，避免使用SELECT *，它会选择所有列。

  • BLOB或TEXT对象的最大大小由其类型确定，但实际上您可以在客户端和服务器之间传输的最大值取决于可用内存量和通信缓冲区的大小。您可以通过更改max_allowed_packet变量的值来更改消息缓冲区大小，但必须同时为服务器和客户端程序执行此操作。例如，mysql和mysqldump都允许您更改客户端端的max_allowed_packet值。请参阅第 7.1.1 节，“配置服务器”，第 6.5.1 节，“mysql — MySQL 命令行客户端”和第 6.5.4 节，“mysqldump — 数据库备份程序”。您可能还想比较数据包大小和您存储的数据对象的大小与存储要求的大小，参见第 13.7 节，“数据类型存储要求”

每个BLOB或TEXT值在内部由单独分配的对象表示。这与所有其他数据类型形成对比，其他数据类型在打开表时为每列分配存储空间。

在某些情况下，将二进制数据（如媒体文件）存储在BLOB或TEXT列中可能是可取的。您可能会发现 MySQL 的字符串处理函数在处理此类数据时非常有用。请参阅第 14.8 节，“字符串函数和运算符”。出于安全和其他原因，通常最好使用应用程序代码来处理此类数据，而不是给予应用程序用户FILE权限。您可以在 MySQL 论坛（forums.mysql.com/）讨论各种语言和平台的具体情况。

注意

在mysql客户端中，二进制字符串以十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。

13.3.5 ENUM 类型

原文：dev.mysql.com/doc/refman/8.0/en/enum.html

ENUM是一个字符串对象，其值是在表创建时在列规范中明确枚举的允许值列表中选择的。

请参见第 13.3.1 节，“字符串数据类型语法”以获取ENUM类型的语法和长度限制。

ENUM类型具有以下优点：

  • 在列具有有限可能值集的情况下紧凑的数据存储。您指定的输入值字符串会自动编码为数字。有关ENUM类型的存储要求，请参见第 13.7 节，“数据类型存储要求”。

  • 可读的查询和输出。数字在查询结果中被翻译回相应的字符串。

以及需要考虑的潜在问题：

  • 如果您创建看起来像数字的枚举值，很容易混淆文字值与其内部索引号，如枚举限制中所解释的那样。

  • 在ORDER BY子句中使用ENUM列需要额外小心，如枚举排序中所解释的那样。

  • 创建和使用 ENUM 列

  • 枚举文字的索引值

  • 枚举文字的处理

  • 空或 NULL 的枚举值

  • 枚举排序

  • 枚举限制

创建和使用 ENUM 列

枚举值必须是带引号的字符串文字。例如，您可以像这样创建具有ENUM列的表：

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

将值为'medium'的 1 百万行插入到此表中将需要 1 百万字节的存储空间，而如果您将实际字符串'medium'存储在VARCHAR列中，则需要 6 百万字节。

枚举文字的索引值

每个枚举值都有一个索引：

  • 在列规范中列出的元素被分配索引号，从 1 开始。

  • 空字符串错误值的索引值为 0。这意味着您可以使用以下SELECT语句查找分配了无效ENUM值的行：

    mysql> SELECT * FROM *tbl_name* WHERE *enum_col*=0;

  • NULL值的索引为NULL。

  • 这里的“索引”术语指的是枚举值列表中的位置。它与表索引无关。

例如，指定为ENUM('Mercury', 'Venus', 'Earth')的列可以具有此处显示的任何值。每个值的索引也显示在这里。

值索引
NULLNULL
''0
'水星'1
'金星'2
'地球'3

一个ENUM列最多可以有 65,535 个不同的元素。

如果在数值上下文中检索ENUM值，则返回列值的索引。例如，可以像这样从ENUM列中检索数值：

mysql> SELECT *enum_col*+0 FROM *tbl_name*;

诸如SUM()或AVG()之类的期望数值参数的函数在必要时将参数转换为数字。对于ENUM值，索引号用于计算。

处理枚举文字

在创建表时，表定义中的ENUM成员值的尾随空格会被自动删除。

检索时，存储到ENUM列中的值将使用在列定义中使用的大小写形式显示。请注意，ENUM列可以分配字符集和排序规则。对于二进制或区分大小写的排序规则，在为列分配值时会考虑大小写形式。

如果将一个数字存储到一个ENUM列中，该数字将被视为可能值的索引，存储的值是具有该索引的枚举成员。（但是，这在LOAD DATA中不起作用，因为它将所有输入视为字符串。）如果将数字值引用起来，如果在枚举值列表中没有匹配的字符串，它仍然被解释为索引。因此，不建议定义一个具有看起来像数字的枚举值的ENUM列，因为这很容易变得令人困惑。例如，以下列具有字符串值为'0'，'1'和'2'的枚举成员，但索引值为1，2和3：

numbers ENUM('0','1','2')

如果存储2，它被解释为索引值，并变为'1'（具有索引 2 的值）。如果存储'2'，它匹配一个枚举值，因此存储为'2'。如果存储'3'，它不匹配任何枚举值，因此被视为索引并变为'2'（具有索引 3 的值）。

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

要确定ENUM列的所有可能值，请使用SHOW COLUMNS FROM *tbl_name* LIKE '*enum_col*'并解析输出的Type列中的ENUM定义。

在 C API 中，ENUM值以字符串形式返回。有关使用结果集元数据将其与其他字符串区分开的信息，请参阅 C API 基本数据结构。

空或 NULL 枚举值

在某些情况下，枚举值也可以是空字符串（''）或NULL：

  • 如果向ENUM中插入无效值（即不在允许值列表中的字符串），则插入空字符串作为特殊错误值。此字符串可以通过该字符串的数值为 0 来与“正常”空字符串区分。有关枚举值的数值索引的详细信息，请参阅枚举文字的索引值。

    如果启用了严格的 SQL 模式，则尝试插入无效的ENUM值会导致错误。

  • 如果声明ENUM列允许NULL，则NULL值是列的有效值，且默认值为NULL。如果声明ENUM列为NOT NULL，其默认值为允许值列表的第一个元素。

枚举排序

ENUM值根据它们的索引号排序，这取决于在列规范中列出枚举成员的顺序。例如，对于ENUM('b', 'a')，'b'在'a'之前排序。空字符串在非空字符串之前排序，NULL值在所有其他枚举值之前排序。

为了在对ENUM列使用ORDER BY子句时避免意外结果，请使用以下技术之一：

  • 按字母顺序指定ENUM列表。

  • 确保通过编码ORDER BY CAST(*col* AS CHAR)或ORDER BY CONCAT(*col*)对列进行字典排序而不是按索引号排序。

枚举限制

枚举值不能是表达式，即使是评估为字符串值的表达式也不行。

例如，此CREATE TABLE语句不起作用，因为CONCAT函数不能用于构造枚举值：

CREATE TABLE sizes (
    size ENUM('small', CONCAT('med','ium'), 'large')
);

您也不能将用户变量用作枚举值。这对语句不起作用：

SET @mysize = 'medium';

CREATE TABLE sizes (
    size ENUM('small', @mysize, 'large')
);

我们强烈建议不要将数字用作枚举值，因为它不会节省适当TINYINT或SMALLINT类型的存储空间，并且如果错误引用ENUM值，很容易混淆字符串和底层数值（可能不同）。如果将数字用作枚举值，请始终将其括在引号中。如果省略引号，则该数字被视为索引。请参阅枚举文字的处理以查看即使引用的数字也可能被错误地用作数值索引值的情况。

定义中的重复值会导致警告，如果启用了严格的 SQL 模式，则会导致错误。

13.3.6 SET类型

原文：dev.mysql.com/doc/refman/8.0/en/set.html

SET是一个字符串对象，可以具有零个或多个值，每个值必须从创建表时指定的允许值列表中选择。由于SET列值由逗号（,）分隔的成员指定，因此SET成员值本身不应包含逗号。

例如，指定为SET('one', 'two') NOT NULL的列可以具有以下任何值：

''
'one'
'two'
'one,two'

SET列最多可以有 64 个不同的成员。

定义中的重复值会导致警告，如果启用了严格的 SQL 模式，则会导致错误。

在创建表时，SET成员值的尾随空格会自动删除。

有关SET类型的存储要求，请参见字符串类型存储要求。

有关SET类型的语法和长度限制，请参见第 13.3.1 节，“字符串数据类型语法”。

当检索时，存储在SET列中的值将以列定义中使用的大小写形式显示。请注意，SET列可以分配字符集和排序规则。对于二进制或区分大小写的排序规则，分配值给列时会考虑大小写。

MySQL 以数字形式存储SET值，存储值的低位对应于第一个集合成员。如果在数字上下文中检索SET值，则检索到的值具有对应于构成列值的集合成员的位设置。例如，可以像这样从SET列中检索数值：

mysql> SELECT *set_col*+0 FROM *tbl_name*;

如果将数字存储到SET列中，则在数字的二进制表示中设置的位确定列值中的集合成员。对于指定为SET('a','b','c','d')的列，成员具有以下十进制和二进制值。

SET成员十进制值二进制值
'a'10001
'b'20010
'c'40100
'd'81000

如果将值9分配给此列，即二进制为1001，因此选择第一个和第四个SET值成员'a'和'd'，得到的值为'a,d'。

对于包含多个SET元素的值，插入值时元素的顺序无关紧要。同样，给定元素在值中列出的次数也无关紧要。稍后检索值时，值中的每个元素只出现一次，并且元素按照在创建表时指定的顺序列出。假设列被指定为SET('a','b','c','d')：

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

如果插入值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'd,a,d'：

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

然后检索这些值时，所有这些值都显示为'a,d'：

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

如果将SET列设置为不支持的值，则该值将被忽略并发出警告：

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

如果启用了严格的 SQL 模式，则尝试插入无效的SET值将导致错误。

SET值按数字顺序排序。NULL值在非NULL SET值之前排序。

诸如SUM()或AVG()之类的期望数值参数的函数，如果需要，将参数转换为数字。对于SET值，转换操作会导致使用数值值。

通常，您可以使用FIND_IN_SET()函数或LIKE运算符来搜索SET值：

mysql> SELECT * FROM *tbl_name* WHERE FIND_IN_SET('*value*',*set_col*)>0;
mysql> SELECT * FROM *tbl_name* WHERE *set_col* LIKE '%*value*%';

第一条语句查找包含*value集合成员的行。第二条类似，但不完全相同：它查找包含value*的任何地方的行，即使作为另一个集合成员的子字符串。

以下语句也是允许的：

mysql> SELECT * FROM *tbl_name* WHERE *set_col* & 1;
mysql> SELECT * FROM *tbl_name* WHERE *set_col* = '*val1*,*val2*';

这些语句中的第一个查找包含第一个集合成员的值。第二个查找精确匹配。对于第二种类型的比较要小心。将集合值与'*val1*,*val2*'进行比较会产生不同的结果，与将值与'*val2*,*val1*'进行比较不同。您应该按照它们在列定义中列出的顺序指定值。

要确定SET列的所有可能值，请使用SHOW COLUMNS FROM *tbl_name* LIKE *set_col*并解析输出的Type列中的SET定义。

在 C API 中，SET值以字符串形式返回。有关使用结果集元数据将其与其他字符串区分开的信息，请参阅 C API 基本数据结构。

13.4 空间数据类型

原文：dev.mysql.com/doc/refman/8.0/en/spatial-types.html

13.4.1 空间数据类型

13.4.2 OpenGIS 几何模型

13.4.3 支持的空间数据格式

13.4.4 几何形态和有效性

13.4.5 空间参考系统支持

13.4.6 创建空间列

13.4.7 填充空间列

13.4.8 获取空间数据

13.4.9 优化空间分析

13.4.10 创建空间索引

13.4.11 使用空间索引

开放地理空间联盟（OGC）是一个由 250 多家公司、机构和大学参与开发公开可用的概念解决方案的国际联盟，这些解决方案可用于管理各种处理空间数据的应用程序。

开放地理空间联盟发布了OpenGIS®实施标准地理信息 - 简单要素访问 - 第 2 部分：SQL 选项，这是一份提出了几种概念性扩展 SQL RDBMS 以支持空间数据的文件。此规范可从 OGC 网站www.opengeospatial.org/standards/sfs获取。

遵循 OGC 规范，MySQL 将空间扩展实现为带有几何类型的 SQL环境的子集。该术语指的是已扩展为一组几何类型的 SQL 环境。实现为具有几何类型的列的几何值的 SQL 列。规范描述了一组 SQL 几何类型，以及在这些类型上创建和分析几何值的函数。

MySQL 空间扩展使得可以生成、存储和分析地理要素：

  • 用于表示空间值的数据类型

  • 用于操作空间值的函数

  • 空间索引以提高对空间列的访问时间

空间数据类型和函数可用于MyISAM, InnoDB, NDB, 和 ARCHIVE 表。对于索引空间列，MyISAM 和 InnoDB 支持 SPATIAL 和非 SPATIAL 索引。其他存储引擎支持非 SPATIAL 索引，如 第 15.1.15 节，“CREATE INDEX 语句” 中所述。

地理要素 是世界上任何具有位置的事物。一个要素可以是：

  • 一个实体。例如，一座山，一个池塘，一个城市。

  • 一个空间。例如，城镇区，热带地区。

  • 可定义的位置。例如，十字路口，作为两条街道交汇的特定地点。

一些文档使用术语地理空间特征来指代地理特征。

几何是指地理特征的另一个词。最初，几何一词指地球的测量。另一个含义来自制图学，指制图师用来绘制世界地图的几何特征。

这里讨论的术语视为同义词：地理特征，地理空间特征，特征或几何。最常用的术语是几何，定义为代表世界上任何具有位置的点或点的集合。

以下材料涵盖了这些主题：

  • MySQL 模型中实现的空间数据类型

  • OpenGIS 几何模型中空间扩展的基础

  • 用于表示空间数据的数据格式

  • 如何在 MySQL 中使用空间数据

  • 用于空间数据的索引使用

  • MySQL 与 OpenGIS 规范的差异

有关操作空间数据的函数信息，请参见第 14.16 节，“空间分析函数”。

其他资源

这些标准对于 MySQL 实现空间操作很重要：

  • SQL/MM 第 3 部分：空间。

  • 开放地理空间联盟发布了地理信息的 OpenGIS®实施标准，该文件提出了几种扩展 SQL RDBMS 以支持空间数据的概念方法。特别参见 Simple Feature Access - Part 1: Common Architecture 和 Simple Feature Access - Part 2: SQL Option。开放地理空间联盟（OGC）在www.opengeospatial.org/维护一个网站。规范可在www.opengeospatial.org/standards/sfs上找到。其中包含与此材料相关的其他信息。

  • 空间参考系统（SRS）定义的语法基于OpenGIS 实施规范：坐标转换服务，修订版 1.00，OGC 01-009，2001 年 1 月 12 日，第 7.2 节中定义的语法。该规范可在www.opengeospatial.org/standards/ct上找到。有关 MySQL 中 SRS 定义与该规范的差异，请参见第 15.1.19 节，“CREATE SPATIAL REFERENCE SYSTEM Statement”。

如果您对 MySQL 的空间扩展使用有疑问或担忧，可以在 GIS 论坛中讨论：forums.mysql.com/list.php?23。

13.4.1 空间数据类型

原文：dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html

MySQL 具有与 OpenGIS 类对应的空间数据类型。这些类型的基础在第 13.4.2 节，“OpenGIS 几何模型”中描述。

一些空间数据类型保存单个几何值：

  • GEOMETRY

  • POINT

  • LINESTRING

  • POLYGON

GEOMETRY可以存储任何类型的几何值。其他单值类型（POINT，LINESTRING和POLYGON）将其值限制为特定的几何类型。

其他空间数据类型保存值的集合：

  • MULTIPOINT

  • MULTILINESTRING

  • MULTIPOLYGON

  • GEOMETRYCOLLECTION

GEOMETRYCOLLECTION可以存储任何类型的对象集合。其他集合类型（MULTIPOINT，MULTILINESTRING和MULTIPOLYGON）将集合成员限制为具有特定几何类型的成员。

示例：要创建一个名为geom的表，其中包含一个名为g的列，可以存储任何几何类型的值，请使用以下语句：

CREATE TABLE geom (g GEOMETRY);

具有空间数据类型的列可以具有SRID属性，以明确指示存储在列中的值的空间参考系统（SRS）。例如：

CREATE TABLE geom (
    p POINT SRID 0,
    g GEOMETRY NOT NULL SRID 4326
);

如果计划在列上创建SPATIAL索引，则可以在具有特定 SRID 的列上创建SPATIAL索引，因此，如果计划对列进行索引，请声明具有NOT NULL和SRID属性：

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);

InnoDB表允许笛卡尔和地理 SRS 的SRID值。MyISAM表允许笛卡尔 SRS 的SRID值。

SRID属性使空间列受 SRID 限制，这具有以下影响：

  • 该列只能包含具有给定 SRID 的值。尝试插入具有不同 SRID 的值会产生错误。

  • 优化器可以在列上使用SPATIAL索引。请参见第 10.3.3 节，“SPATIAL 索引优化”。

没有SRID属性的空间列不受 SRID 限制，并接受任何 SRID 的值。但是，在将列定义修改为包含SRID属性之前，优化器无法在其上使用SPATIAL索引，这可能需要首先修改列内容，以使所有值具有相同的 SRID。

有关如何在 MySQL 中使用空间数据类型的其他示例，请参见第 13.4.6 节，“创建空间列”。有关空间参考系统的信息，请参见第 13.4.5 节，“空间参考系统支持”。

13.4.2 OpenGIS 几何模型

原文：dev.mysql.com/doc/refman/8.0/en/opengis-geometry-model.html

13.4.2.1 几何类层次结构

13.4.2.2 几何类

13.4.2.3 点类

13.4.2.4 曲线类

13.4.2.5 线串类

13.4.2.6 表面类

13.4.2.7 多边形类

13.4.2.8 几何集合类

13.4.2.9 多点类

13.4.2.10 多曲线类

13.4.2.11 多线串类

13.4.2.12 多表面类

13.4.2.13 多边形集合类

OGC 的带有几何类型的 SQL环境提出的几何类型集合基于OpenGIS 几何模型。在这个模型中，每个几何对象具有以下一般属性：

  • 它与空间参考系统相关联，描述了对象定义的坐标空间。

  • 它属于某些几何类。

原文：dev.mysql.com/doc/refman/8.0/en/gis-geometry-class-hierarchy.html

几何类层次结构

几何类定义如下层次结构：

  • Geometry（不可实例化）

    • Point（可实例化）

    • Curve（不可实例化）

      • LineString（可实例化）

        • Line

        • LinearRing

    • Surface（不可实例化）

      • Polygon（可实例化）

    • GeometryCollection（可实例化）

      • MultiPoint（可实例化）

      • MultiCurve（不可实例化）

        • MultiLineString（可实例化）

      • MultiSurface（不可实例化）

        • MultiPolygon（可实例化）

不可能在不可实例化的类中创建对象。可以在可实例化的类中创建对象。所有类都有属性，可实例化的类也可能有断言（定义有效类实例的规则）。

Geometry是基类。它是一个抽象类。Geometry的可实例化子类限制为存在于二维坐标空间中的零、一和二维几何对象。所有可实例化的几何类都被定义为有效实例是拓扑闭合的（即，所有定义的几何包括其边界）。

基类Geometry具有Point、Curve、Surface和GeometryCollection的子类：

  • Point代表零维对象。

  • Curve代表一维对象，具有子类LineString，子子类Line和LinearRing。

  • Surface设计用于二维对象，具有子类Polygon。

  • GeometryCollection具有专门的零、一和二维集合类，分别命名为MultiPoint、MultiLineString和MultiPolygon，用于建模对应于Points、LineStrings和Polygons的几何。MultiCurve和MultiSurface被引入为概括集合接口以处理Curves和Surfaces的抽象超类。

Geometry、Curve、Surface、MultiCurve和MultiSurface被定义为不可实例化的类。它们为其子类定义了一组共同的方法，并且包含用于可扩展性的方法。

Point、LineString、Polygon、GeometryCollection、MultiPoint、MultiLineString和MultiPolygon是可实例化的类。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-geometry.html

13.4.2.2 几何类

Geometry是层次结构的根类。它是一个不可实例化的类，但具有一些属性，描述了从任何Geometry子类创建的所有几何值共有的属性。特定的子类具有自己的特定属性，稍后描述。

几何属性

几何值具有以下属性：

  • 其类型。每个几何体属于层次结构中的可实例化类之一。

  • 其SRID，或空间参考标识符。此值标识了描述几何对象定义的坐标空间的相关空间参考系统。

    在 MySQL 中，SRID 值是与几何值关联的整数。可使用的最大 SRID 值为 2³²−1。如果给出一个更大的值，则只使用低 32 位。

    SRID 0 表示一个无限的平面笛卡尔平面，其轴没有分配单位。为了确保 SRID 0 的行为，请使用 SRID 0 创建几何值。如果未指定 SRID，则 SRID 0 是新几何值的默认值。

    对于多个几何值的计算，所有值必须具有相同的 SRID，否则会出错。

  • 其在其空间参考系统中的坐标，表示为双精度（8 字节）数字。所有非空几何体至少包含一对（X，Y）坐标。空几何体不包含坐标。

    坐标与 SRID 相关。例如，在不同的坐标系统中，两个对象之间的距离可能会有所不同，即使对象具有相同的坐标，因为平面坐标系统上的距离和大地测量系统（地球表面上的坐标）上的距离是不同的。

  • 其内部，边界和外部。

    每个几何体在空间中占据一定位置。几何体的外部是几何体未占据的所有空间。内部是几何体占据的空间。边界是几何体内部和外部之间的界面。

  • 其MBR（最小外包矩形）或包络。这是由最小和最大（X，Y）坐标形成的边界几何体：

    ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))

  • 值是简单还是非简单。类型为（LineString，MultiPoint，MultiLineString）的几何值要么是简单的，要么是非简单的。每种类型确定其自己的简单或非简单断言。

  • 值是闭合还是未闭合。类型为（LineString，MultiString）的几何值要么是闭合的，要么是未闭合的。每种类型确定其自己的闭合或未闭合断言。

  • 该值是空还是非空。如果几何图形没有任何点，则为空。空几何图形的外部、内部和边界未定义（即，它们由 NULL 值表示）。空几何图形被定义为始终简单且面积为 0。

  • 它的维度。一个几何图形可以具有维度为−1、0、1 或 2：

    • −1 代表空几何图形。

    • 0 代表长度和面积均为零的几何图形。

    • 1 代表具有非零长度和零面积的几何图形。

    • 2 代表具有非零面积的几何图形。

    Point 对象的维度为零。LineString 对象的维度为 1。Polygon 对象的维度为 2。MultiPoint、MultiLineString 和 MultiPolygon 对象的维度与它们所包含的元素的维度相同。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-point.html

13.4.2.3 点类

一个Point是表示坐标空间中单个位置的几何体。

Point示例

  • 想象一幅世界地图，上面标注着许多城市。一个Point对象可以代表每个城市。

  • 在城市地图上，一个Point对象可以代表一个公交车站。

Point属性

  • X 坐标值。

  • Y 坐标值。

  • Point被定义为零维几何体。

  • 一个Point的边界是空集。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-curve.html

13.4.2.4 Curve 类

一个Curve是一维几何体，通常由一系列点表示。特定的Curve子类定义了点之间的插值类型。Curve是一个不可实例化的类。

Curve属性

  • 一个Curve具有其点的坐标。

  • 一个Curve被定义为一维几何体。

  • 如果一个Curve不通过同一点两次，则它是简单的，但如果起点和终点相同，则曲线仍然可以是简单的。

  • 如果一个Curve的起点等于终点，则该Curve是闭合的。

  • 闭合Curve的边界为空。

  • 非闭合Curve的边界由其两个端点组成。

  • 一个简单且闭合的Curve是LinearRing。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-linestring.html

13.4.2.5 LineString 类

一个LineString是一个具有点之间线性插值的Curve。

LineString 示例

  • 在世界地图上，LineString对象可以表示河流。

  • 在城市地图上，LineString对象可以表示街道。

LineString 属性

  • 一个LineString由每对连续点定义的段的坐标组成。

  • 如果一个LineString由恰好两个点组成，则它是一条Line。

  • 一个LineString如果既是封闭的又是简单的，就是一个LinearRing。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-surface.html

13.4.2.6 Surface 类

一个Surface是一个二维几何体。它是一个不可实例化的类。它唯一可实例化的子类是Polygon。

Surface属性

  • 一个Surface被定义为一个二维几何体。

  • OpenGIS 规范将简单的Surface定义为一个几何体，由一个与单个外部边界和零个或多个内部边界相关联的“补丁”组成。

  • 一个简单Surface的边界是与其外部和内部边界对应的一组闭合曲线。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-polygon.html

13.4.2.7 Polygon 类

一个Polygon是表示多边几何形状的平面Surface。它由单个外部边界和零个或多个内部边界定义，其中每个内部边界定义了Polygon中的一个孔。

Polygon示例

  • 在区域地图上，Polygon对象可以表示森林、区域等。

Polygon断言

  • Polygon的边界由一组LinearRing对象（即，既简单又闭合的LineString对象）组成，构成其外部和内部边界。

  • 一个Polygon没有交叉的环。Polygon边界中的环可能在一个Point处相交，但只能作为切线。

  • 一个Polygon没有线条、尖角或穿孔。

  • 一个Polygon具有连通的内部点集。

  • 一个Polygon可能有孔。带有孔的Polygon的外部不是连通的。每个孔定义了外部的一个连通组件。

上述断言使Polygon成为一个简单的几何体。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-geometrycollection.html

13.4.2.8 GeometryCollection 类

GeomCollection 是一个包含零个或多个任意类别几何体的集合几何体。

GeomCollection 和 GeometryCollection 是同义词，GeomCollection 是首选类型名称。

几何集合中的所有元素必须在相同的空间参考系统中（即在相同的坐标系统中）。对于几何集合的元素没有其他约束，尽管下面描述的 GeomCollection 的子类可能会限制成员资格。限制可能基于：

  • 元素类型（例如，MultiPoint 可能只包含 Point 元素）

  • 维度

  • 元素之间空间重叠程度的约束

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-multipoint.html

13.4.2.9 MultiPoint 类

一个MultiPoint是由Point元素组成的几何集合。这些点没有以任何方式连接或排序。

MultiPoint 示例

  • 在世界地图上，一个MultiPoint可以代表一串小岛。

  • 在城市地图上，一个MultiPoint可以代表售票处的出口。

MultiPoint 属性

  • 一个MultiPoint是一个零维几何体。

  • 如果一个MultiPoint的两个Point值不相等（具有相同的坐标值），那么它就是简单的。

  • 一个MultiPoint的边界是空集。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-multicurve.html

13.4.2.10 MultiCurve 类

MultiCurve 是由 Curve 元素组成的几何集合。MultiCurve 是一个不可实例化的类。

MultiCurve 属性

  • MultiCurve 是一维几何体。

  • 当且仅当 MultiCurve 的所有元素都是简单的时，MultiCurve 才是简单的；任何两个元素之间的唯一交点发生在这两个元素的边界上。

  • 通过应用“模 2 并集规则”（也称为“奇偶规则”）可以获得 MultiCurve 边界：如果一个点在奇数个 Curve 元素的边界上，则它在 MultiCurve 的边界上。

  • 如果 MultiCurve 的所有元素都是闭合的，则 MultiCurve 是闭合的。

  • 闭合 MultiCurve 的边界始终为空。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-multilinestring.html

13.4.2.11 MultiLineString 类

一个MultiLineString是由LineString元素组成的MultiCurve几何集合。

MultiLineString 示例

  • 在区域地图上，一个MultiLineString可以代表一个河流系统或者高速公路系统。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-multisurface.html

13.4.2.12 多表面类

一个MultiSurface是由表面元素组成的几何集合。MultiSurface是一个不可实例化的类。它唯一可实例化的子类是MultiPolygon。

MultiSurface断言

  • MultiSurface内的表面没有相交的内部。

  • MultiSurface内的表面的边界最多在有限数量的点处相交。

原文：dev.mysql.com/doc/refman/8.0/en/gis-class-multipolygon.html

13.4.2.13 MultiPolygon 类

一个MultiPolygon是由Polygon元素组成的MultiSurface对象。

MultiPolygon示例

  • 在区域地图上，一个MultiPolygon可以代表一个湖泊系统。

MultiPolygon断言

  • 一个MultiPolygon没有两个内部相交的Polygon元素。

  • 一个MultiPolygon没有两个相交的Polygon元素（相交也被前一个断言禁止），或者在无限多点处接触。

  • 一个MultiPolygon不能有切线、尖点或穿孔。一个MultiPolygon是一个规则的、闭合的点集。

  • 一个具有多个Polygon的MultiPolygon具有不连通的内部。MultiPolygon内部的连通分量数量等于MultiPolygon中的Polygon值数量。

MultiPolygon属性

  • 一个MultiPolygon是一个二维几何体。

  • 一个MultiPolygon的边界是一组闭合曲线（LineString值），对应于其Polygon元素的边界。

  • MultiPolygon边界中的每个Curve都在一个Polygon元素的边界中。

  • 每个Polygon元素边界中的每个Curve都在MultiPolygon的边界中。

13.4.3 支持的空间数据格式

原文：dev.mysql.com/doc/refman/8.0/en/gis-data-formats.html

用于在查询中表示几何对象的两种标准空间数据格式：

  • Well-Known Text (WKT)格式

  • Well-Known Binary (WKB)格式

在内部，MySQL 以一种与 WKT 或 WKB 格式不完全相同的格式存储几何值（内部格式类似于 WKB，但具有用于指示 SRID 的初始 4 个字节）。

有函数可用于在不同数据格式之间转换；参见第 14.16.6 节，“几何格式转换函数”。

以下各节描述了 MySQL 使用的空间数据格式：

  • Well-Known Text (WKT) Format Format")

  • Well-Known Binary (WKB) Format Format")

  • Internal Geometry Storage Format

Well-Known Text (WKT)格式

几何值的 Well-Known Text (WKT)表示设计用于以 ASCII 形式交换几何数据。OpenGIS 规范提供了一个 Backus-Naur 语法，指定了编写 WKT 值的正式生成规则（参见第 13.4 节，“空间数据类型”）。

几何对象的 WKT 表示示例：

  • 一个Point：

    POINT(15 20)

    点坐标未用逗号分隔。这与 SQL Point()函数的语法不同，后者要求坐标之间有逗号。请注意使用适合给定空间操作上下文的语法。例如，以下语句都使用ST_X()从Point对象中提取 X 坐标。第一个直接使用Point()函数生成对象。第二个使用转换为Point的 WKT 表示，使用ST_GeomFromText()。

    mysql> SELECT ST_X(Point(15, 20));
+---------------------+
| ST_X(POINT(15, 20)) |
+---------------------+
|                  15 |
+---------------------+

mysql> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
|                                    15 |
+---------------------------------------+

  • 具有四个点的LineString：

    LINESTRING(0 0, 10 10, 20 25, 50 60)

    点坐标对由逗号分隔。

  • 具有一个外环和一个内环的Polygon：

    POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

  • 具有三个Point值的MultiPoint：

    MULTIPOINT(0 0, 20 20, 60 60)

    接受MultiPoint值的 WKT 格式表示的空间函数，如ST_MPointFromText()和ST_GeomFromText()，允许值内的单个点被括号括起来。例如，以下两个函数调用都是有效的：

    ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')

  • 一个具有两个LineString值的MultiLineString：

    MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

  • 具有��个Polygon值的MultiPolygon：

    MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

  • 由两个Point值和一个LineString组成的GeometryCollection：

    GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

熟知二进制（WKB）格式

几何值的熟知二进制（WKB）表示用于以二进制流交换几何数据，表示为包含几何 WKB 信息的 BLOB 值。该格式由 OpenGIS 规范定义（请参阅第 13.4 节，“空间数据类型”）。它还在 ISO SQL/MM 第 3 部分：空间标准中定义。

WKB 使用 1 字节无符号整数、4 字节无符号整数和 8 字节双精度数（IEEE 754 格式）。一个字节是八位。

例如，与 POINT(1 -1) 对应的 WKB 值由以下 21 个字节序列组成，每个字节由两个十六进制数字表示：

0101000000000000000000F03F000000000000F0BF

该序列由下表中显示的组件组成。

表 13.2 WKB 组件示例

组件大小值
字节顺序1 字节01
WKB 类型4 字节01000000
X 坐标8 字节000000000000F03F
Y 坐标8 字节000000000000F0BF

组件表示如下：

  • 字节顺序指示符为 1 或 0，表示小端或大端存储。小端和大端字节顺序也被称为网络数据表示（NDR）和外部数据表示（XDR）。

  • WKB 类型是指示几何类型的代码。MySQL 使用值从 1 到 7 来表示 Point、LineString、Polygon、MultiPoint、MultiLineString、MultiPolygon 和 GeometryCollection。

  • Point 值具有 X 和 Y 坐标，每个坐标表示为双精度值。

更复杂的几何值的 WKB 值具有更复杂的数据结构，详细信息请参阅 OpenGIS 规范。

内部几何存储格式

MySQL 使用 4 个字节来指示 SRID，然后是值的 WKB 表示。有关 WKB 格式的描述，请参阅熟知二进制（WKB）格式。

对于 WKB 部分，这些是适用于 MySQL 的特定考虑因素：

  • 字节顺序指示符字节为 1，因为 MySQL 将几何值存储为小端值。

  • MySQL 支持 Point、LineString、Polygon、MultiPoint、MultiLineString、MultiPolygon 和 GeometryCollection 几何类型。不支持其他几何类型。

  • 只有 GeometryCollection 可以为空。这样的值存储为 0 元素。

  • 多边形环可以指定顺时针和逆时针。MySQL 在读取数据时会自动翻转环。

笛卡尔坐标以空间参考系统的长度单位存储，X 值在 X 坐标中，Y 值在 Y 坐标中。轴方向由空间参考系统指定。

地理坐标以空间参考系统的角度单位存储，经度在 X 坐标中，纬度在 Y 坐标中。轴方向和子午线由空间参考系统指定。

LENGTH() 函数返回存储值所需的字节空间。例如：

mysql> SET @g = ST_GeomFromText('POINT(1 -1)');
mysql> SELECT LENGTH(@g);
+------------+
| LENGTH(@g) |
+------------+
|         25 |
+------------+
mysql> SELECT HEX(@g);
+----------------------------------------------------+
| HEX(@g)                                            |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+

值长度为 25 个字节，由以下组件组成（如从十六进制值中可以看出）：

  • 4 个字节用于整数 SRID（0）

  • 1 个字节用于整数字节顺序（1 = 小端）

  • 4 个字节用于整数类型信息（1 = Point）

  • 8 个字节用于双精度 X 坐标（1）

  • 8 个字节用于双精度 Y 坐标（−1）

13.4.4 几何体的良好形成性和有效性

原文：dev.mysql.com/doc/refman/8.0/en/geometry-well-formedness-validity.html

对于几何值，MySQL 区分了语法上良好形成和几何有效的概念。

如果几何体满足这个（不全面）列表中的条件，那么它在语法上是良好形成的：

  • 线串至少有两个点

  • 多边形至少有一个环

  • 多边形环是封闭的（第一个和最后一个点相同）

  • 多边形环至少有 4 个点（最小多边形是一个三角形，第一个和最后一个点相同）

  • 集合不为空（除了 GeometryCollection）

如果几何体在语法上是良好形成的并满足这个（不全面）列表中的条件，那么它在几何上是有效的：

  • 多边形不会自相交

  • 多边形内部环在外部环内

  • 多边形不会有重叠的多边形

如果几何体在语法上不是良好形成的，则空间函数会失败。解析 WKT 或 WKB 值的空间导入函数会对尝试创建不在语法上良好形成的几何体的操作引发错误。对于尝试将几何体存储到表中的操作也会检查语法上的良好形成性。

允许插入、选择和更新几何上无效的几何体，但它们必须在语法上是良好形成的。由于计算开销，MySQL 不会明确检查几何有效性。空间计算可能会检测到一些无效几何体并引发错误，但也可能在不检测到无效性的情况下返回未定义的结果。需要几何有效性的应用程序应使用 ST_IsValid() 函数进行检查。

13.4.5 空间参考系统支持

原文：dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html

空间数据的空间参考系统（SRS）是用于地理位置的基于坐标的系统。

有不同类型的空间参考系统：

  • 投影 SRS 是将地球投影到平面表面的投影；也就是说，是一个平面地图。例如，一个位于地球仪内部的灯泡照射到包围地球的纸筒上，将地图投影到纸上。结果是地理参考：每个点映射到地球上的一个位置。该平面上的坐标系统是笛卡尔坐标系，使用长度单位（米、英尺等），而不是经度和纬度的度数。

    在这种情况下，地球是椭球体；也就是说，是扁平的球体。地球在南北轴上比东西轴短一点，所以稍微扁平的球体更正确，但完美的球体可以进行更快的计算。

  • 地理 SRS 是表示椭球上经度-纬度（或纬度-经度）坐标的非投影 SRS，使用任何角度单位。

  • MySQL 中由 SRID 0 表示的 SRS 代表一个无限的平面笛卡尔平面，其轴没有分配单位。与投影 SRS 不同，它没有地理参考，也不一定代表地球。它是一个可以用于任何事物的抽象平面。SRID 0 是 MySQL 中空间数据的默认 SRID。

MySQL 在数据字典mysql.st_spatial_reference_systems表中维护有关空间数据可用空间参考系统的信息，该表可以存储投影和地理 SRS 的条目。这个数据字典表是不可见的，但 SRS 条目内容可以通过INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS表获得，该表作为mysql.st_spatial_reference_systems上的视图实现（参见 Section 28.3.36, “INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS 表”）。

以下示例展示了 SRS 条目的外观：

mysql> SELECT *
       FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS
       WHERE SRS_ID = 4326\G
*************************** 1\. row ***************************
                SRS_NAME: WGS 84
                  SRS_ID: 4326
            ORGANIZATION: EPSG
ORGANIZATION_COORDSYS_ID: 4326
              DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984",
                          SPHEROID["WGS 84",6378137,298.257223563,
                          AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],
                          PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
                          UNIT["degree",0.017453292519943278,
                          AUTHORITY["EPSG","9122"]],
                          AXIS["Lat",NORTH],AXIS["Long",EAST],
                          AUTHORITY["EPSG","4326"]]
             DESCRIPTION:

本条目描述了用于 GPS 系统的 SRS。它的名称（SRS_NAME）是 WGS 84，ID（SRS_ID）是 4326，这是欧洲石油勘探集团（EPSG）使用的 ID。

DEFINITION列中的 SRS 定义是 WKT 值，表示为开放地理空间联盟文档OGC 12-063r5中指定的形式。

SRS_ID值代表与几何值的 SRID 相同类型的值，或作为空间函数的 SRID 参数传递。SRID 0（无单位的笛卡尔平面）是特殊的。它始终是合法的空间参考系统 ID，并且可以在依赖于 SRID 值的空间数据的任何计算中使用。

对于多个几何值的计算，所有值必须具有相同的 SRID，否则会出错。

当 GIS 函数需要定义时，SRS 定义会按需解析。解析后的定义存储在数据字典缓存中，以便重复使用，并避免为每个需要 SRS 信息的语句产生解析开销。

为了使数据字典中存储的 SRS 条目可以进行操作，MySQL 提供了以下 SQL 语句：

  • CREATE SPATIAL REFERENCE SYSTEM: 查看第 15.1.19 节，“CREATE SPATIAL REFERENCE SYSTEM Statement”。该语句的描述包括有关 SRS 组件的附加信息。

  • DROP SPATIAL REFERENCE SYSTEM: 查看第 15.1.31 节，“DROP SPATIAL REFERENCE SYSTEM Statement”。

13.4.6 创建空间列

原文：dev.mysql.com/doc/refman/8.0/en/creating-spatial-columns.html

MySQL 提供了一种标准方法来为几何类型创建空间列，例如，使用CREATE TABLE或ALTER TABLE。空间列支持MyISAM、InnoDB、NDB和ARCHIVE表。另请参阅有关在第 13.4.10 节，“创建空间索引”下的空间索引的注意事项。

具有空间数据类型的列可以具有 SRID 属性，以明确指示存储在列中的值的空间参考系统（SRS）。有关 SRID 受限列的影响，请参见第 13.4.1 节，“空间数据类型”。

  • 使用CREATE TABLE语句创建具有空间列的表：

    CREATE TABLE geom (g GEOMETRY);

  • 使用ALTER TABLE语句向现有表添加或删除空间列：

    ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;

13.4.7 填充空间列

原文：dev.mysql.com/doc/refman/8.0/en/populating-spatial-columns.html

在创建空间列之后，您可以用空间数据填充它们。

值应存储在内部几何格式中，但您可以将它们从 Well-Known Text（WKT）或 Well-Known Binary（WKB）格式转换为该格式。以下示例演示了如何通过将 WKT 值转换为内部几何格式将几何值插入表中：

  • 直接在INSERT语句中执行转换：

    INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

  • 在INSERT之前执行转换：

    SET @g = ST_GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);

以下示例将更复杂的几何形状插入表中：

SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

前面的示例使用ST_GeomFromText()创建几何值。您还可以使用特定类型的函数：

SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_PointFromText(@g));

SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (ST_LineStringFromText(@g));

SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (ST_PolygonFromText(@g));

SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (ST_GeomCollFromText(@g));

想要使用 WKB 表示几何值的客户端应用程序负责向服务器发送正确形成的 WKB 查询。有几种方法可以满足此要求。例如：

  • 插入一个带有十六进制字面值语法的POINT(1 1)值：

    INSERT INTO geom VALUES
(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

  • ODBC 应用程序可以发送 WKB 表示，将其绑定到使用BLOB类型的参数的占位符：

    INSERT INTO geom VALUES (ST_GeomFromWKB(?))

    其他编程接口可能支持类似的占位符机制。

  • 在 C 程序中，您可以使用mysql_real_escape_string_quote()转义二进制值，并将结果包含在发送到服务器的查询字符串中。请参见 mysql_real_escape_string_quote()。

13.4.8 获取空间数据

原文：dev.mysql.com/doc/refman/8.0/en/fetching-spatial-data.html

存储在表中的几何值可以以内部格式获取。您还可以将它们转换为 WKT 或 WKB 格式。

  • 获取内部格式的空间数据：

    使用内部格式获取几何值在表与表之间的转移中可能很有用：

    CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;

  • 获取 WKT 格式的空间数据：

    ST_AsText() 函数将几何从内部格式转换为 WKT 字符串。

    SELECT ST_AsText(g) FROM geom;

  • 获取 WKB 格式的空间数据：

    ST_AsBinary() 函数将几何从内部格式转换为包含 WKB 值的 BLOB。

    SELECT ST_AsBinary(g) FROM geom;

13.4.9 优化空间分析

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-spatial-analysis.html

对于MyISAM和InnoDB表，包含空间数据的列中的搜索操作可以使用SPATIAL索引进行优化。最典型的操作包括：

  • 点查询，搜索包含给定点的所有对象

  • 区域查询，搜索与给定区域重叠的所有对象

MySQL 在空间列上使用具有二次分裂的 R-Tree来构建SPATIAL索引。SPATIAL索引是使用几何图形的最小外接矩形（MBR）构建的。对于大多数几何图形，MBR 是一个围绕几何图形的最小矩形。对于水平或垂直线串，MBR 是一个退化为线串的矩形。对于点，MBR 是一个退化为点的矩形。

也可以在空间列上创建普通索引。在非SPATIAL索引中，必须为除POINT列之外的任何空间列声明前缀。

MyISAM和InnoDB都支持SPATIAL和非SPATIAL索引。其他存储引擎支持非SPATIAL索引，如第 15.1.15 节，“CREATE INDEX 语句”中所述。

13.4.10 创建空间索引

原文：dev.mysql.com/doc/refman/8.0/en/creating-spatial-indexes.html

对于InnoDB和MyISAM表，MySQL 可以使用类似于创建常规索引的语法来创建空间索引，但使用SPATIAL关键字。空间索引中的列必须声明为NOT NULL。以下示例演示了如何创建空间索引：

  • 使用CREATE TABLE：

    CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

  • 使用ALTER TABLE：

    CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
ALTER TABLE geom ADD SPATIAL INDEX(g);

  • 使用CREATE INDEX：

    CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
CREATE SPATIAL INDEX g ON geom (g);

SPATIAL INDEX创建一个 R 树索引。对于支持对空间列进行非空间索引的存储引擎，引擎会创建一个 B 树索引。对空间值创建 B 树索引对于精确值查找很有用，但不适用于范围扫描。

优化器可以使用在受 SRID 限制的列上定义的空间索引。有关更多信息，请参见第 13.4.1 节，“空间数据类型”和第 10.3.3 节，“空间索引优化”。

有关在空间列上创建索引的更多信息，请参见第 15.1.15 节，“CREATE INDEX Statement”。

要删除空间索引，请使用ALTER TABLE或DROP INDEX：

  • 使用ALTER TABLE：

    ALTER TABLE geom DROP INDEX g;

  • 使用DROP INDEX：

    DROP INDEX g ON geom;

例如：假设一个表geom包含超过 32,000 个几何图形，这些图形存储在类型为GEOMETRY的列g中。该表还有一个AUTO_INCREMENT列fid用于存储对象 ID 值。

mysql> DESCRIBE geom;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| fid   | int(11)  |      | PRI | NULL    | auto_increment |
| g     | geometry |      |     |         |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM geom;
+----------+
| count(*) |
+----------+
|    32376 |
+----------+
1 row in set (0.00 sec)

要在列g上添加空间索引，请使用以下语句：

mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
Query OK, 32376 rows affected (4.05 sec)
Records: 32376  Duplicates: 0  Warnings: 0
avatar
文章
阅读
粉丝