MySQL - Data Types 翻译

原文链接

Chapter 11 Data Types

MySQL 支持以下几种 SQL 数据类型：数值类型、日期和时间类型、字符串类型和空间类型。

数值类型

MySQL 支持所有的标准 SQL 数值类型。包括精确数值类型（INTEGER，SAMLLINT，DECIMAL 和 NUMERIC），以及近似数值类型（FLOAT，REAL 和 DOUBLE PRECISION）。关键词 INT 和 INTEGER 是同义词，DEC、FIXED 是 DECIMAL 的同义词。MySQL 将 DECIMAL 作为 DOUBLE PRECISION 的同义词（非标准扩展）；不弃用 REAL_AS_FLOAT 的话，MySQL 还将 REAL 作为 DOUBLE PRECISION 的同义词。

BIT 类型存储 bit 值，支持在 MyISAM、MEMORY、InnoDB 和 NDB 表中使用。

关于 MySQL 表达式求值及赋值溢出的处理的更多信息，参阅《Out-of-Range and Overflow Handling》

关于数值类型的存储需求，参阅《Data Type Storage Requirements》

关于操作数值和函数的说明，参阅《Numeric Functions and Operators》、《Arithmetic Operators》

语法

对于整数数据类型，M 表示最大显示宽度。最大显示宽度为 255。显示宽度与可存储的值无关，如《Numeric Type Attributes》所述。

对于浮点和和定点数据类型，M 表示可储存的字节数。

如果数值列指定了 ZEROFILL，MySQL 会自动将 UNSIGNED 属性添加到该列。

允许 UNSIGNED 属性的数据类型也允许 SIGNED。但这些数据类型默认就是 SIGNED，因此 SIGNED 没有什么作用。

SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的一个别名

BIT[(M)]: 位值类型。M 表示每个值的 bit 数，从 1 到 64，缺省状态下 M = 1
TINYINT[(M)] [UNSIGNED] [ZEROFILL]: 很小的整数。有符号的范围是 -128 ~ 127，无符号的范围是 0 ~ 255
BOOL, BOOLEAN: `TINYINT(1) 的同义词。零被认为是 false，非零则为 true
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]: 小整数。有符号的范围是 -32768 ~ 32767，无符号的范围是 0 ~ 65535
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]: 中等大小的整数。有符号的范围是 -8388608 ~ 8388607，无符号的范围是 0 ~ 16777215
INT[(M)] [UNSIGNED] [ZEROFILL]: 常规整数。有符号的范围是 -2147483648 ~ 2147483647，无符号的范围是 0 ~ 4294967295
INTEGER[(M)] [UNSIGNED] [ZEROFILL]: 与 INT 定义相同
BIGINT[(M)] [UNSIGNED] [ZEROFILL]: 大整数。有符号的范围是 -9223372036854775808 ~ 9223372036854775807，无符号的范围是 0 ~ 18446744073709551615
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]: 一个「精确」的定点数值。M 为总位数，D 为小数点后的位数。小数点和符号不记在 M 中。若 D 为 0，则值没有小数点及小数部分，M 最大值为 65，D 最大值为 30。缺省的 D 默认为 0，缺省的 M 默认为 10。如果使用 UNSIGNED 则不允许使用负数
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]: 与 DECIMAL 定义相同
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]: 单精度浮点数，范围是 -3.402823466E+38 ~ -1.175494351E-38, 0, 1.175494351E-38 ~ 3.402823466E+38。这些是际遇 IEEE 标准的理论限制。实际范围根据硬件和操作系统有微小差别。如果使用 UNSIGNED 则不允许使用负数
FLOAT(p) [UNSIGNED] [ZEROFILL]: 浮点数。p 表示以位为单位的精度，如果 p 是 0 ~ 24，MySQL 会将类型转换成没有 M 与 D 值的 FLOAT，如果 p 是 25 ~ 53，则数据类型会被转换成没有 M 与 D 值的 DOUBLE
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]: 双精度浮点数，范围是 -1.7976931348623157E+308 ~ -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 ~ 1.7976931348623157E+308。这些是际遇 IEEE 标准的理论限制。实际范围根据硬件和操作系统有微小差别。如果使用 UNSIGNED 则不允许使用负数
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]: 定义与 DOUBLE 相同

日期和时间类型

日期和时间类型包括 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR，每个时态类型都有一个有效值范围，以及一个「零值」（用于指定一个 MySQL 不能表示的无效值）。TIMESTAMP 和 DATETIME 具有特别的自动更新行为，如《Automatic Initialization and Updating for TIMESTAMP and DATETIME》所述。

关于时态数据类型存储相关的更多信息，参阅《Data Type Storage Requirements》

关于操作时间值函数的描述，参阅《Date and Time Functions》

处理日期和时间类型时，请记住以下几点：

MySQL 以标准输出格式检索给定日期或时间的值，但它试图解释你提供的输入值的各种格式（例如，当你指定一个用于分配或比较的时态类型值的时候）。有关日期和时间类型的允许格式说明，参阅《Date and Time Literals》。最好提供有效的值，否则可能出现不可预知的结果
虽然 MySQL 尝试以集中格式解释值，但日期部分必须始终以 year-monty-day 顺序给出（例如：’98-09-04’）。要将其他顺序转换为 year-month-day 顺序，可以使用 STR_TO_DATE() 方法
两位数的年份是不明确的，因为所在的世纪未知。MySQL 使用一下规则解释两位数的年：
- 70-99 => 1970-1999
- 00-69 => 2000-2069
时态类型的转换，参阅《Conversion Between Date and Time Types》
如果在数字上下文使用日期或时间值，MySQL 会自动将其转换为数字，反之亦然
默认情况下，当 MySQL遇到超出范围的日期或时间类型的值或该类型无效的值时，它会将该值转换为该类型的零值。例外情况是，超出范围的时间值被裁剪到时间范围的适当端点
通过将 SQL 模式设置为适当的值，你可以更准确地指定希望 MySQL 支持的时间类型，参阅《Server SQL Modes》。通过启用 ALLOW_INVALID_DATES 模式，可以让 MySQL 接受某些日期，如 2009-11-31。当你希望在数据库中存储用户指定的「可能错误」的值时，这可能非常有用。在这种模式下，MySQL 只验证月份在 1 到 12，日期在 1 到 31 的范围
MySQL 允许在 DATE 或 DATETIME 列中存储日期，其中日期，其中日或者月日为零。这对于需要存储生日的应用汇非常有用，因为你可能不知道切确的日期，例如 2009-00-00 或 2009-01-00。然而，对于这样的日期，你不应该期望获得 DATE_SUB() 或 DATE_ADD() 方法的正确计算结果，它们需要完整的日期。可通过启用 NO_ZERO_IN_DATE 模式去禁用上述行为
MySQL 允许将零值 0000-00-00 存储为「虚拟日期」。在某些情况下，这比使用 NULL 值更方便，并且使用更少的数据和索引空间。可通过启用 NO_ZERO_DATE 模式来禁用上述行为
通过连接器 / ODBC 使用的「零值」将自动转换为 NULL，因为 ODBC 不能处理这样的值

Data Type	“Zero” Value
DATE	‘0000-00-00’
TIME	‘00:00:00’
DATETIME	‘0000-00-00 00:00:00’
TIMESTAMP	‘0000-00-00 00:00:00’
YEAR	0000

MySQL 允许 TIME、DATETIME、TIMESTAMP 的小数秒达到微秒级别（6 位），可以使用 type_name(fsp) 语法进行定义，例如

1	CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

fsp 值必须为 0 到 6 之间的整数，缺省值为 0（为了兼容以前的 MySQL 版本，这与标准 SQL 的默认值 6 不同）

TIMESTAMP 或 DATETIME 列中可以实现自动初始化和更新属性；参阅《Automatic Initialization and Updating for TIMESTAMP and DATETIME》

DATE: 日期。支持的范围是 1000-01-01 ~ 9999-12-31。MySQL 以 YYYY-MM-DD 格式显示日期值，但允许使用字符串或数字对 DATE 列赋值
DATETIME[(fsp)]: 日期和时间的组合。支持的范围是 1000-01-01 00:00:00.000000 ~ '9999-12-31 23:59:59.999999'。MySQL 以 YYYY-MM-DD hh:mm:ss[.fraction] 格式显示 DATETIME 值，但允许使用字符串或数字对 DATETIME 列进行赋值
TIMESTAMP[(fsp)]: 时间戳。支持的范围是 '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC。TIMESTAMP 值存储自 epoch (‘1970-01-01 00:00:00’ UTC) 以来的秒数。TIMESTAMP 不能表示 1970-01-01 00:00:00，因为它等于 epoch 开始的 0 秒，而 0 用于表示 0000-00-00 00:00:00
- 可选值 fsp 范围为 0 ~ 6 之间的整数，代表秒的小数精度，缺省值为 0
- 服务器处理 TIMESTAMP 定义依赖于 explicit_defaults_for_timestamp 的值
- 如果 explicit_defaults_for_timestamp 处于启用状态，则不会自动将 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 分配给任何 TIMESTAMP 列，它们必须被显式定义。此外，任何为显式声明为 NOT NULL 的 TIMESTAMP 都允许空值
- 如果 explicit_defaults_for_timestamp 处于禁用状态，那么默认情况下，表中第一个 TIMESTAMP 列具备 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性
- explicit_defaults_for_timestamp 在 MySQL 5.6.6 之后可用，在 5.6.6 之前，服务端处理 TIMESTAMP 表现为 explicit_defaults_for_timestamp 被禁用的状态
TIME[(fsp)]: 时间。支持的范围是 -838:59:59.000000 ~ 838:59:59.000000。MySQL 以 hh:mm:ss[.fraction] 格式显示 TIME 值，但允许使用字符串或数字对 TIME 列进行赋值。可选值 fsp 范围为 0 ~ 6 之间的整数，代表秒的小数精度，缺省值为 0
YEAR[(2|4)]: 两位数或四位数格式的年。
聚合函数 SUM() 和 AVG() 不能处理时间值。（他们会将值转换成数字，丢失第一个非数字字符之后的所有内容）。要解决此问题，请将其转换为数值，执行局和操作后再将其转换为时间值。例：

1 2	SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name; SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

字符类型

语法

字符类型包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET。

在某些情况下，MySQL 可能会将字符串更改为与 CREATE TABLE 或 ALTER TABLE 语句中给定的不同数据类型，参与《Silent Column Specification Changes》。

对于字符串列（CHAR、VARCHAR、TEXT），MySQL 以字符为单位声明长度。对于二进制字符串（BINARY、VARBINARY、BLOB），MySQL 以字节数为单位声明长度。

字符串数据类型（CHAR、VARCHAR、TEXT、ENUM、SET 和其他同义词的列定义）可以指定列字符集和排序规则

CHARACTER SET 用于声明字符集，如果需要，可以使用 COLLATE 或其他属性指定字符集的排序规则，例如：

CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
# case-sensitive (_cs) 表示排序规则区分大小写

当缺少 CHARACTER SET 或 COLLATE 属性时，分配字符集和排序规则参阅《Column Character Set and Collation》

CHARSET 是 CHARACTER SET 的同义词。

为字符串数据类型指定二进制属性会导致列被创建为二进制字符串：CHAR 变为 BINARY，VARCHAR 变为 VARBINARY，TEXT 变为 BLOB。

## 试图创建
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
);

二进制属性是一个非标准的 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;

ASCII 属性是 CHARACTER SET latin1 的简写形式
UNICODE 属性是 CHARACTER SET ucs2 的简写形式

字符列的比较和排序基于分配给列的排序规则。对 CHAR、VARCHAR、TEXT、ENUM、SET 类型，可以使用二进制（_bin）排序规则或 BINARY 属性声明一个列，以便使用底层字符串代码值而不是词法排序来进行比较和排序。

有关 MySQL 中使用字符集的更多信息，参阅《Chapter 10, Character Sets, Collations, Unicode》

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
- 一个固定长度的字符串，存储时总是将空格右填充到指定长度。M 表示以字符为单位的列长度，范围是 0 ~ 255，缺省值为 1。
- 除非启用了 PAD_CHAR_TO_FULL_LENGTH 的 SQL 模式，检索时尾部的空格会被删除
- CHAR 是 CHARACTER 的缩写。使用 NATIONAL CHAR（或与其等价的 NCHAR）应该使用一些预定义字符集，这是定义一个 CHAR 列标准的 SQL 方法。MySQL 使用 utf8 作为作为这个预定义字符集。
- CHAR BYTE 数据类型是二进制数据类型的别名，这是一个兼容功能。
- MySQL 允许你创建一个 CHAR(0) 的列。当你必须让该列存在但实际并不适用值使其余旧的应用程序兼容时，这是非常有用的。当你需要一个值接受两个值的列时，CHAR(0) 也非常有用：定义为 CHAR(0) NULL 的列只占用 1 bit，并且只能接受 NULL 和空字符串
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
- 一个变长字符串。M 表示字符的最大列长度，范围是 0 ~ 65535。VARCHAR 的有效最大长度取决于最大行大小（所有列共享 65535 字节）。例如 utf8 字符每个字符最多需要 3 个字节，因此使用 utf8 字符集的 VARCHAR 最多可以声明 21844 个字符。参阅《Limits on Table Column Count and Row Size》
- MySQL 将 VARCHAR 值存储为 1 字节或 2 字节长度的前缀加上数据。长度前缀表示值中的字节数，如果值需要的长度不超过 255 字节，VARCHAR 列则使用 1 个长度字节，如果值需要的长度可能超过 255 字节，则使用两个长度字节
- MySQL 遵循标准的 SQL 规范，并且不从 VARCHAR 值中删除尾随空格
- VARCHAR 是 CHARACTER VARYING 的缩写。使用 NATIONAL VARCHAR 应该使用一些预定义字符集，这是定义一个 CHAR 列标准的 SQL 方法。MySQL 使用 utf8 作为作为这个预定义字符集
BINARY[(M)]: 类似 CHAR 类型的二进制类型，但是存储二进制字节字符串而不是非二进制字符串。M 的缺省值为 1
VARBINARY(M): 与 VARCHAR 类似，但是存储二进制字节字符串而不是非二进制字符串
TINYBLOB: 一个最大长度为 255 字节的 BLOB。每个 TINYBLOB 值都是用一个 1 字节长度的前缀去存储，该前缀表示值中的字节数
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
- 最大长度为 255 个字符的 TEXT 列。如果该值包含多字节字符，则有效长度将更小。每个 TINYTEXT 值有 1 字节长度的前缀，表示该值的字节数
BLOB[(M)]
- 最大长度为 65535 字节的 BLOB 列。每个 BLOB 值都有一个 2 字节长度的前缀，表示该值的字节数
- 可以为这种类型提供一个 M 值，MySQL 创建的列是足够容纳 M 字节的最小 BLOB 类型
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
- 最大长度为 65535 (2^16 − 1) 字节的 TEXT 列。如果该值包含多字节字符，则有效最大铲毒将更小。每个 TEXT 值多有一个 2 字节长度的前缀，表示该值的字节数
- 可以为这种类型提供一个 M 值，MySQL 创建的列是足够容纳 M 字节的最小 TEXT 类型
MEDIUMBLOB: 最大长度为 (2^24 - 1) 的 BLOB，需要 3 字节长度前缀表示该值的字节数
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]:
- 最大长度为 (2^24 - 1) 的 TEXT，需要 3 字节长度前缀表示该值的字节数
LONGBLOB: 最大长度为 (2^32 - 1) 的 BLOB，需要 4 字节长度前缀表示该值的字节数；最大有效长度受到客户端/服务器协议中配置的最大数据包大小和可用内容制约。
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
- 最大长度为 (2^32 - 1) 的 TEXT，需要 4 字节长度前缀表示该值的字节数；最大有效长度受到客户端/服务器协议中配置的最大数据包大小和可用内容制约。
ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
- 枚举。一个字符串对象，只能有一个值，从 (‘value1’,’value2’,…) 或 NULL、特殊的错误值中选择。枚举值在内部表示为整数
- ENUM 列最多可以有 65535 个不同的元素。（实际限制小于 3000）。表的 ENUM 和作为组的 SET 列之间的唯一元素列表定义不能超过 255 个。有关这些限制的更多信息，参阅《Limits Imposed by .frm File Structure》
SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
- 一个可以有 0 个或则多个值的字符串对象，每个值必须从 (‘value1’,’value2’,…) 中选择。SET 值在内部表示为整数
- 一个 SET 值最多可以有 64 个不同的成员