mac11.14 mysql_mysql 5.7 11 章 数据类型（1）

前言 这是 一篇mysql 5.7 官方手册的部分翻译，目的加强自己的理解 ，和 提升英文水平。不保证质量，速度。部分我感觉没有多大用处的部分没有做翻译。英语手册地址 https://dev.mysql.com/doc/refman/5.7/en/data-types.html

----------------------------------------------

内容包括

11.1 数据类型 概况

11.2 数字类型

11.3 日期和时间类型

11.4 字符串类型

11.5 空间数据类型

11.6 json数据类型

11.7 数据类型默认的值

11.8 数据类型的存储需要

11.9 对一个字段选择一个正确的数据类型

11.10 使用来自其他数据库引擎的数据类型

mysql 支持若干种类的多种数据类型：数字，日期和时间，字符串类型(character and byte),空间类型，json类型。这个章节体提供了对这些数据类型的概述,更加详细的描述这些类型的属性在各个对应的目录。也提供了数据类型的存储需要的摘要。最开始的概述是有意简短的。对特定数据类型其他的信息将在这个章节的后面做更加详细的描述讨论。例如,你可以指定的值的允许格式。

数据类型的描述使用这些惯例：

1.对 integer 类型，M代表最大的展示宽带，对浮动和固定类型，M代表了数字能被储存的数量(精度)。对字符串类型，M是最大的长度。M最大允许的值根据数据类型的不同而不同。

2. D被应用于浮动 和 固定类型。用于指定小数点后面位数。最大的可能的值是30，但是不会大于M-2

3.fsp应用于

4. 方括号( [ 和 ] ) 表示类型定义可以选择的部分。

11.1 数据类型 概况

11.1.1 数字类型概述

11.1.2日期和时间类型概述

11.1.3 字符串类型概述

11.1.1 数字类型概述

下面是对数字类型的概述。其他额外的关于数字类型属性和存储需要的信息，见章节 11.2数字类型和章节 11.8 数据类型的存储需要。

对 整数 类型而言，M 表示最大展示宽度。这最大宽度是255. 展示宽度 同一个类型的值能存储的范围不相关，相关描述见 章节11.2数字类型

对浮动和固定类型，M代表了数字能被储存的数量

如果你指定一个字段ZEROFILL，mysql会自动添加UNSIGNED 属性给该字段

数字类型允许UNSIGNED 属性，也允许SIGNED。但是 数据类型默认 是 signed，所以 设置 signed 类型是没有效果的。

SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.的别名

SERIAL 在一个整数字段中 的默认值 是 NOT NULL AUTO_INCREMENT UNIQUE. 的别名

警告

当你使用2个整数类型的值相减而且 其中一个是 UNSIGNED 类型的，那么值也是 UNSIGNED ，除非 NO_UNSIGNED_SUBTRACTION 模式被启用。见章节 12.10 计算功能和操作

BIT[(M)]

A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

一个非常小的整数，signed 的范围是 -128到 127 ，unsigned 范围是0到255

这些类型可以考虑为TINYINT(1) 的同义词。0被认为false,不是0的值被认为true;

mysql> SELECT IF(0, 'true', 'false');

+------------------------+

| IF(0, 'true', 'false') |

+------------------------+

| false |

+------------------------+

mysql> SELECT IF(1, 'true', 'false');

+------------------------+

| IF(1, 'true', 'false') |

+------------------------+

| true |

+------------------------+

mysql> SELECT IF(2, 'true', 'false');

+------------------------+

| IF(2, 'true', 'false') |

+------------------------+

| true |

+------------------------+

但是 TRUE 和 FALSE 仅仅只是 1 和 0 各自的同义词，例如下面的展示的

mysql> SELECT IF(0 = FALSE, 'true', 'false');

+--------------------------------+

| IF(0 = FALSE, 'true', 'false') |

+--------------------------------+

| true |

+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');

+-------------------------------+

| IF(1 = TRUE, 'true', 'false') |

+-------------------------------+

| true |

+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');

+-------------------------------+

| IF(2 = TRUE, 'true', 'false') |

+-------------------------------+

| false |

+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');

+--------------------------------+

| IF(2 = FALSE, 'true', 'false') |

+--------------------------------+

| false |

+--------------------------------+

最后2个语句展示的结构表明2 既不等于1，也不等于0

(实际测试 0认为是 fasle.1认为是 true，其他 如 -1 ，2等 既不被认为是 true 也不被认为是 false ，该结果同数据类型无关 int(2) ，TINYINT(2) 也是如此测试结果 )

一个小的整数，signed 的范围是 -32768 到 32767，unsigned 范围是0到65535

一个中等大小的整数，signed的范围是 -8388608 到 8388607，unsigned 范围是0到16777215

ef="https://dev.mysql.com/doc/refman/5.7/en/integer-types.html">INT[(M)] [UNSIGNED] [ZEROFILL]

一个普通大小的整数，signed的范围是 -2147483648 到 2147483647，unsigned 范围是0到4294967295

int的同义词

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

一个大的整数。signed 的范围是 -9223372036854775808 到 223372036854775807.。unsigned范围是0到18446744073709551615。

SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的同义词

有些关于事情你应该知道。

.

1.所有的运算时使用signed 的 BIGINT 或者 DOUBLE 值，所以，除了bit function 以外，你不应该使用 unsigned 的大的超过 9223372036854775807 的整数。如果你那样做，有些结果的最后的数字可能会出错，因为当一个BIGINT 类型的值 转换为 DOUBLE 类型时，产生舍入错误。

mysql能在下面的事例中处理BIGINT

1.1 当使用 整数保存大的unsigned 值到一个 BIGINT 字段中时。

1.2 在 MIN(col_name)或者 MAX(col_name) 中，这里 col_name 引用 一个 BIGINT 字段

1.3 当使用运算符(+，-，*，等等)2边都是整数时。

2.你也可以使用储存字符串的方式，储存一个精确的整数值到一个 BIGINT 字段。因为,mysql执行字符串到数字的转换，无中间双精度表示。

3.当2边都是整数形式时， +，-，*运算使用BIGINT来进行运算。这时，如果你使用2个大的整数进行乘法运算(或者functions 的返回的结果时整数)，当结果大于9223372036854775807 时，你可能会达到出乎意料的结果。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

一个精确的固定的数。M表示全部的数字的位数(精度),D表示了小数点后面的数字的位数(比例)。小数点和 – 没有被计算到M中，如果D等于0，代表没有小数位。最大的M对DECIMAL 而言是65。最大的支持的D为30.如果D不指定，默认为0，如果M不指定默认为10

UNSIGNED 如果你指定，表示不允许负数。

所有具有十进制列的基本计算(+，-，*，/)都是以65位的精度完成的。

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

这个类型是DECIMAL的同义词。 FIXED 是为了同其他类型的数据库兼容。

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

一个小的浮动小数(单精度)。允许的值为 -3.402823466E+38 到 -1.175494351E-38，0，和

1.175494351E-38 t 到 3.402823466E+38. 这些是依据IEEE标准， 理论上的限制 。根据你的硬件或者操作系统，实际的范围可能会微微小于这个值

M表示全部的数字的位数，,D表示了小数点后面的数字的位数。如果 M和 D没有提交，储存的位数的限制依赖硬件。一个单精度浮动数大约的精确度是7位。

FLOAT(M,D) 是一个不 标准的mysql扩展

UNSIGNED 如果你指定，表示不允许负数。

使用FLOAT可能给一些不可预料的问题，因为所以在mysql的计算使用 double 精度。见章节 Section B.4.4.7, “Solving Problems with No Matching Rows”.

FLOAT(p) [UNSIGNED] [ZEROFILL]

A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.

FLOAT(p) syntax is provided for ODBC compatibility.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

一个正常大小的浮动位小数(双精度)。允许的值为 -1.7976931348623157E+308 到 -2.2250738585072014E-308，0，和2.2250738585072014E-308到 1.7976931348623157E+308。这些是依据IEEE标准， 理论上的限制 。根据你的硬件或者操作系统，实际的范围可能会微微小于这个值

M表示全部的数字的位数，,D表示了小数点后面的数字的位数。如果 M和 D没有提交，储存的位数的限制依赖硬件。一个双精度浮动数大约的精确度是15位。

DOUBLE[(M,D)] 是一个不 标准的mysql扩展

UNSIGNED 如果你指定，表示不允许负数

These types are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.

11.1.2 日期和时间类型概述

下面是对时间类型的概述。其他额外的关于数字类型属性和存储需要的信息，见章节 11.3日期和时间类型 和章节 11.8 数据类型的存储需要。关于更多的操作时间值的函数的描述见 章节 12.7 日期和时间的函数

对 DATE 和DATETIME 类型范围的描述,” supported” 意味着虽然较早的值可能工作，但是无法保证

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版本)

任何 在一个表中的TIMESTAMP 或者 DATETIME 字段 能自动初始化和自动更新

date类型。支持的范围为 1000-01-01 到 9999-12-31 。Mysql 展示date类型 使用 YYYY-MM-DD 格式，但是允许指定date 字段 使用 字符串 或者 数字

日期和时间的组合，支持的范围为1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。mysql展示 DATETIME 的值使用 YYYY-MM-DD hh:mm:ss[.fraction] 格式，但是允许指定DATETIME 字段 使用 字符串 或者 数字。

可选的参数fsp,长度可能在0到6位用于指定微秒的精度。0表示这里没有微秒，如果忽略，默认的精度为0

自动初始化和更新当前的日期和时间字段 可以使用 DEFAULT 和 ON UPDATE 字段定义 语句，相关描述见章节哎11.3.5 TIMESTAMP 和DATETIME 字段的 自动初始化和更新 。

一个时间戳。支持的范围为1970-01-01 00:00:01.000000 UTC到'2038-01-19 03:14:07.999999' UTC. TIMESTAMP 使用从1970-01-01 00:00:00' UTC开始的秒的数量来储存。TIMESTAMP 不能表示1970-01-01 00:00:00' ，因为 这等同于 从这时间开始的0秒，而0秒已经保留下来用于代表 '0000-00-00 00:00:00 ，zero 时间戳的值。

可选的参数fsp,长度可能在0到6位用于指定微秒的精度。0表示这里没有微秒，如果忽略，默认的精度为0

服务处理TIMESTAMP 定义的方式依赖explicit_defaults_for_timestamp 系统变量的值(见章节 5.1.7 服务器系统变量)

如果explicit_defaults_for_timestamp 被启用，DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMPattributes 自动的赋值将不能应用于任何的TIMESTAMP 字段。他们必须包括在明确的字段定义中，没有明确声明NOT NULL的TIMESTAMP 都允许NULL。

Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. However, any TIMESTAMP column in a table can be defined to have these properties.

TIME[(fsp)]

时间，范围为 -838:59:59.000000 到 838:59:59.000000。mysql使用 TIME 字段使用 hh:mm:ss[.fraction] 格式，但是允许指定time字段 使用 字符串 或者 数字

可选的参数fsp,长度可能在0到6位用于指定微秒的精度。0表示这里没有微秒，如果忽略，默认的精度为0

年使用4位的格式。mysql展示 YEAR 使用 YYYY格式，但是允许指定 YEAR字段的值使用字符串或者数字，值展示如 1901 ， 2155, 或者0000.

注意

关于其他的关于YEAR展示格式和输入值的说明，见章节11.3.3, “The YEAR Type”.

SUM() 和 AVG()聚集函数不能在时间字段中工作。(他们会转换值为数字，同时丢失掉任何的非数字的特性)。围绕这问题，把时间字段转换为数字，执行聚合函数，然后再转换为时间字段。 例如

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;

SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

注意

Mysql服务能在MAXDB sql模式中运行。这时，TIMESTAMP等同于 DATETIME. 。如果该模式被启用，TIMESTAMP 创造 等同于创造DATETIME 字段。作为一个结果，这样的字段使用DATETIME 的展示格式，拥有一样的范围，不能自动的初始化和更新当前的日期和时间，见章节 5.1.10, “Server SQL Modes”

注意

MySQL 5.7.22 中MAXDB 是反对的。同时将会在以后的mysql版本中移除。