TIDB日期和时间类型

TIDB日期和时间类型

一、日期和时间

DATE、DATETIME和TIMESTAMP

1、DATE

DATE 类型的格式为 YYYY-MM-DD，支持的范围是 1000-01-01 到 9999-12-31。

2、TIME 类型

TIME 类型的格式为 HH:MM:SS[.fraction]，支持的范围是 -838:59:59.000000 到 838:59:59.000000。TIME 不仅可用于指示一天内的时间，还可用于指两个事件之间的时间间隔。fsp 参数表示秒精度，取值范围为：0 ~ 6，默认值为 0。

3、DATETIME 类型

DATETIME 类型是日期和时间的组合，格式为 YYYY-MM-DD HH:MM:SS[.fraction]。支持的范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.000000。 fsp 参数表示秒精度，取值范围为 0~6，默认值为 0。

4、TIMESTAMP 类型

TIMESTAMP 类型包含日期和时间，支持的范围是 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999。fsp参数表示秒精度，取值范围为 0~6，默认值为 0。在 TIMESTAMP 中，不允许零出现在月份部分或日期部分，唯一的例外是零值本身 ‘0000-00-00 00:00:00’。

5、YEAR 类型

YEAR类型的格式为 ‘YYYY’，支持的值范围是 1901 到 2155，或零值 0000。

二、数据类型概述

TiDB 支持除空间类型 (SPATIAL) 之外的所有 MySQL 数据类型，包括数值型类型、字符串类型、时间和日期类型、JSON 类型。

数据类型定义一般为 T(M[, D])，其中：

T 表示具体的类型。
M 在整数类型中表示最大显示长度；在浮点数或者定点数中表示精度；在字符类型中表示最大长度。M 的最大值取决于具体的类型。
D 表示浮点数、定点数的小数位长度。
fsp 在时间和日期类型里的 TIME、 DATETIME 以及 TIMESTAMP 中表示秒的精度，其取值范围是 0 到 6。值为 0，表示没有小数部分。如果省略，则默认精度为 0。

三、数据类型的默认值

1、在一个数据类型描述中的 DEFAULT value 段描述了一个列的默认值。这个默认值必须是常量，不可以是一个函数或者是表达式。但是对于时间类型，可以例外的使用 NOW 、CURRENT_TIMESTAMP 、LOCALTIME 、LOCALTIMESTAMP 等函数作为 DATETIME 或者 TIMESTAMP 的默认值。

2、BLOB 、TEXT 以及 JSON 不可以设置默认值。

3、如果一个列的定义中没有 DEFAULT 的设置。TiDB 按照如下的规则决定:

4、如果该类型可以使用 NULL 作为值，那么这个列会在定义时添加隐式的默认值设置 DEFAULT NULL。
5、如果该类型无法使用 NULL 作为值，那么这个列在定义时不会添加隐式的默认值设置。
6、对于一个设置了 NOT NULL 但是没有显式设置 DEFAULT 的列，当 INSERT 、 REPLACE 没有涉及到该列的值时，TiDB 根据当时的 SQL_MODE 进行不同的行为：

7、如果此时是 strict sql mode ，在事务中的语句会导致事务失败并回滚，非事务中的语句会直接报错。
8、如果此时不是 strict sql mode ，TiDB 会为这列赋值为列数据类型的隐式默认值。
此时隐式默认值的设置按照如下规则：

-------对于数值类型，它们的默认值是 0。当有 AUTO_INCREMENT 参数时，默认值会按照增量情况赋予正确的值。
------对于除了时间戳外的日期时间类型，默认值会是该类型的“零值”。时间戳类型的默认值会是当前的时间。
-----对于除枚举以外的字符串类型，默认值会是空字符串。对于枚举类型，默认值是枚举中的第一个值。

四、数值类型

TiDB 支持 MySQL 所有的数值类型，按照精度可以分为:

整数类型（精确值)
浮点类型（近似值)
定点类型（精确值)

1、整数类型

TiDB 支持 MySQL 所有的整数类型，包括 INTEGER/INT、TINYINT、SMALLINT、MEDIUMINT 以及 BIGINT，完整信息参考这篇文档。

1.1 字段说明：

M 类型： 显示宽度，可选
UNSIGNED 无符号数： 如果不加这个标识，则为有符号数
ZEROFILL 补零标识： 如果有这个标识，TiDB 会自动给类型增加 UNSIGNED 标识，但是没有做补零的操作

1.2 类型定义

1、BIT 类型
比特值类型。M 表示比特位的长度，取值范围从1到64，其默认值是1。

2、BOOLEAN 类型
布尔类型，别名为 BOOL，和 TINYINT(1) 等价。零值被认为是 False，非零值认为是 True。在TiDB 内部，True 存储为 1，False 存储为 0。

3、TINYINT 类型
TINYINT类型。有符号数的范围是 [-128, 127] 。无符号数的范围是 [0, 255] 。

4、MEDIUMINT 类型
MEDIUMINT 类型。有符号数的范围是 [-8388608, 8388607]。无符号数的范围是 [0, 16777215]。

5、INTEGER 类型
INTEGER 类型，别名 INT。有符号数的范围是 [-2147483648, 2147483647]。无符号数的范围是 [0, 4294967295]。

6、BIGINT 类型
BIGINT 类型。有符号数的范围是 [-9223372036854775808, 9223372036854775807]。无符号数的范围是 [0, 18446744073709551615]

2、浮点类型

TiDB 支持 MySQL 所有的浮点类型，包括 FLOAT、DOUBLE。

2.1 字段说明：

M ： 小数总位数
D ： 小数点后位数
UNSIGNED： 无符号数，如果不加这个标识，则为有符号数
ZEROFILL： 补零标识，如果有这个标识，TiDB会自动给类型增加UNSIGNED标识

2.2类型定义

1、FLOAT 类型
单精度浮点数。允许的值范围为 -2^128 ~ +2^128，也即 -3.402823466E+38 到 -1.175494351E-38、0 和 1.175494351E-38 到 3.402823466E+38。这些是基于 IEEE 标准的理论限制。实际的范围根据硬件或操作系统的不同可能稍微小些。

FLOAT( p ) 类型中，p 表示精度（以位数表示）。只使用该值来确定是否结果列的数据类型为 FLOAT 或 DOUBLE。如果 p 为从 0 到 24，数据类型变为没有 M 或 D 值的 FLOAT。如果 p 为从 25 到 53，数据类型变为没有 M 或 D 值的 DOUBLE。结果列范围与本节前面描述的单精度 FLOAT 或双精度 DOUBLE 数据类型相同。

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
FLOAT(p) [UNSIGNED] [ZEROFILL]
1
2

与在 MySQL 中一样，FLOAT 数据类型存储近似值。对于货币之类的精确值，建议使用 DECIMAL 类型。

2、DOUBLE 类型
双精度浮点数，别名为 DOUBLE PRECISION。允许的值范围为：-2^1024 ~ +2^1024，也即是 -1.7976931348623157E+308 到 -2.2250738585072014E-308、0 和 2.2250738585072014E-308 到 1.7976931348623157E+308。这些是基于 IEEE 标准的理论限制。实际的范围根据硬件或操作系统的不同可能稍微小些。

**

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE PRECISION [(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
1
2

**
与在 MySQL 中一样，DOUBLE 数据类型存储近似值。对于货币之类的精确值，建议使用 DECIMAL 类型。

2.3 DECIMAL 类型
定点数，别名为 NUMERIC。M 是小数位数（精度）的总数，D 是小数点（标度）后面的位数。小数点和 -（负数）符号不包括在 M 中。如果 D 是 0，则值没有小数点或分数部分。如果 D 被省略，默认是 0。如果 M 被省略，默认是 10。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
1
2

3、字符串类型

TiDB 支持 MySQL 所有的字符串类型，包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 以及 SET，完整信息参考这篇文档。

3.1、类型定义
1、CHAR 类型
定长字符串。CHAR 列的长度固定为创建表时声明的长度。长度可以为从 0 到 255 的任何值。当保存 CHAR 值时，在它们的右边填充空格以达到指定的长度。

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

2、VARCHAR 类型
变长字符串。M 表示最大列长度，范围是 0 到 65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定。

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
1

3、TEXT 类型
文本串。M 表示最大列长度，范围是 0 到 65535。TEXT 的最大实际长度由最长的行的大小和使用的字符集确定。

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
1

4、TINYTEXT 类型
类似于 TEXT，区别在于最大列长度为 255。

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
1

5、MEDIUMTEXT 类型
类似于 TEXT，区别在于最大列长度为 16,777,215。

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
1

6、LONGTEXT 类型
类似于 TEXT，区别在于最大列长度为 4,294,967,295。

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
1

7、BINARY类型
类似于 CHAR，区别在于 BINARY 存储的是二进制字符串。

BINARY(M)
1

8、VARBINARY类型
类似于 VARCHAR，区别在于 VARBINARY 存储的是二进制字符串。

VARBINARY(M)
1

9、TINYBLOB 类型
类似于 BLOB，区别在于最大列长度为 255。

TINYBLOB
1

10、BLOB 类型
二进制大文件。M表示最大列长度，范围是 0 到 65535。

BLOB[(M)]
1

11、MEDIUMBLOB 类型
类似于 BLOB，区别在于最大列长度为 16,777,215。

MEDIUMBLOB
1

12、LONGBLOB 类型
类似于 BLOB，区别在于最大列长度为 4,294,967,295。

LONGBLOB
1

13、ENUM 类型
枚举类型是一个字符串，它只能有一个值的字符串对象。其值必须是从一个固定集合中选取，这个固定集合在创建表的时候定义，语法是：

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
1

例如：

ENUM('apple', 'orange', 'pear')
1