TiDB 数据类型

概述

TiDB 支持 MySQL 除空间类型之外的所有数据类型,包括数值型类型、字符串类型、时间&日期类型、Json 类型。

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

  • T 表示具体的类型
  • M 对于整数类型表示最大显示长度;对于浮点数或者定点数表示精度;对于字符类型表示最大长度。M 的最大值取决于具体的类型。
  • D 表示浮点数/定点数的小数位长度
  • 对于时间&日期类型中的 TIME、DATETIME 以及 TIMESTAMP,定义中可以包含 Fsp 表示秒的精度,其取值范围是0到6,默认的精度为0

数值类型

概述

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

整数类型

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

类型定义

语法:

BIT[(M)]
> M 1641

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
> TINYINT [-128, 127][0, 255]

BOOL, BOOLEAN
>  TINYINT(1)  False True TiDB True 1 False 0

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
> SMALLINT [-32768, 32767][0, 65535]

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
> MEDIUMINT [-8388608, 8388607][0, 16777215]

INT[(M)] [UNSIGNED] [ZEROFILL]
> INT  [-2147483648, 2147483647][0, 4294967295]

INTEGER[(M)] [UNSIGNED] [ZEROFILL]
>  INT 

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
> BIGINT  [-9223372036854775808, 9223372036854775807][0, 18446744073709551615]

字段意义:

语法元素 说明
M 类型长度,可选的
UNSIGNED 无符号数,如果不加这个标识,则为有符号数
ZEROFILL 补零标识,如果有这个标识,TiDB 会自动给类型增加 UNSIGNED 标识,但是没有做补零的操作

存储空间以及取值范围

每种类型对存储空间的需求以及最大/最小值如下表所示:

类型 存储空间 最小值(有符号/无符号) 最大值(有符号/无符号)
TINYINT 1 -128 / 0 127 / 255
SMALLINT 2 -32768 / 0 32767 / 65535
MEDIUMINT 3 -8388608 / 0 8388607 / 16777215
INT 4 -2147483648 / 0 2147483647 / 4294967295
BIGINT 8 -9223372036854775808 / 0 9223372036854775807 / 18446744073709551615

浮点类型

TiDB 支持 MySQL 所有的浮点类型,包括 FLOAT、DOUBLE,完整信息参考这篇文档。

类型定义

语法:

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
>  -2^128 ~ +2^128 -3.402823466E+38  -1.175494351E-380  1.175494351E-38  3.402823466E+38 IEEE 

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
> -2^1024 ~ +2^1024 -1.7976931348623157E+308  -2.2250738585072014E-3080  2.2250738585072014E-308  1.7976931348623157E+308 IEEE 

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

FLOAT(p) [UNSIGNED] [ZEROFILL]
> p 使 FLOAT  DOUBLE p  0  24 M  D  FLOAT p  25  53 M  D  DOUBLE FLOAT  DOUBLE 

字段意义:

语法元素 说明
M 小数总位数
D 小数点后位数
UNSIGNED 无符号数,如果不加这个标识,则为有符号数
ZEROFILL 补零标识,如果有这个标识,TiDB 会自动给类型增加 UNSIGNED 标识

存储空间

每种类型对存储空间的需求如下表所示:

类型 存储空间
FLOAT 4
FLOAT(p) 如果 0 <= p <= 24 为 4 个字节, 如果 25 <= p <= 53 为 8 个字节
DOUBLE 8

定点类型

TiDB 支持 MySQL 所有的浮点类型,包括 DECIMAL、NUMERIC,完整信息参考这篇文档。

类型定义

语法:

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
> M ()D ()-()M中 D  0 D   0 M   10

NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
> DECIMAL的同义词

字段意义:

语法元素 说明
M 小数总位数
D 小数点后位数
UNSIGNED 无符号数,如果不加这个标识,则为有符号数
ZEROFILL 补零标识,如果有这个标识,TiDB 会自动给类型增加 UNSIGNED 标识

日期时间类型

概述

TiDB 支持 MySQL 所有的日期时间类型,包括 DATE、DATETIME、TIMESTAMP、TIME 以及 YEAR,完整信息参考这篇文档。

类型定义

语法:

DATE
> `1000-01-01``9999-12-31``YYYY-MM-DD` DATE 

DATETIME[(fsp)]
> `1000-01-01 00:00:00.000000``9999-12-31 23:59:59.000000`
`YYYY-MM-DD HH:MM:SS[.fraction]` DATETIME fsp  0-6 0

TIMESTAMP[(fsp)]
> `1970-01-01 00:00:01.000000``2038-01-19 03:14:07.999999`
fsp  0-6 0

TIME[(fsp)]
> `-838:59:59.000000``838:59:59.000000``HH:MM:SS[.fraction]` TIME 
fsp 0-6 0

YEAR[(2|4)]
>  1901  2155  0000 70  69 1970  2069 

字符串类型

概述

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

类型定义

语法:

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
> CHAR  0  255  CHAR 

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
> M  0  65535VARCHAR 使

BINARY(M)
>  CHAR  BINARY 

VARBINARY(M)
>  VARCHAR  VARBINARY 

BLOB[(M)]
> M  0  65535

TINYBLOB
>  BLOB,  255

MEDIUMBLOB
>  BLOB,  16777215

LONGBLOB
>  BLOB,  4294967295

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
> M  0  65535TEXT 使

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
>  TEXT,  255

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
>  TEXT,  16777215

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
>  TEXT,  4294967295

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

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

Json 类型

Json 类型可以存储 Json 这种半结构化的数据,相比于直接将 Json 存储为字符串,它的好处在于:

  1. 使用 Binary 格式进行序列化,对 Json 的内部字段的查询、解析加快;
  2. 多了 Json 合法性验证的步骤,只有合法的 Json 文档才可以放入这个字段中;

Json 字段本身上,并不能创建索引。相反,可以对 Json 文档中的某个子字段创建索引。例如:

CREATE TABLE city (
    id INT PRIMARY KEY,
    detail JSON,
    population INT AS (JSON_EXTRACT(detail, '$.population')
);
INSERT INTO city VALUES (1, '{"name": "Beijing", "population": 100}');
SELECT id FROM city WHERE population >= 100;

枚举类型

集合类型是一个字符串,其值必须是从一个固定集合中选取,这个固定集合在创建表的时候定义,语法是:

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

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

枚举类型的值在 TiDB 内部使用数值来存储,每个值会按照定义的顺序转换为一个数字,比如上面的例子中,每个字符串值都会映射为一个数字:

数字
NULL NULL
'’ 0
‘apple’ 1
‘orange’ 2
‘pear’ 3

更多信息参考 MySQL 枚举文档

集合类型

集合类型是一个包含零个或多个值的字符串,其中每个值必须是从一个固定集合中选取,这个固定集合在创建表的时候定义,语法是:

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

# 
SET('1', '2') NOT NULL

上面的例子中,这列的有效值可以是:

''
'1'
'2'
'1,2'

集合类型的值在 TiDB 内部会转换为一个 Int64 数值,每个元素是否存在用一个二进制位的 0/1 值来表示,比如这个例子 SET('a','b','c','d'),每一个元素都被映射为一个数字,且每个数字的二进制表示只会有一位是 1:

成员 十进制表示 二进制表示
‘a’ 1 0001
‘b’ 2 0010
‘c’ 4 0100
‘d’ 8 1000

这样对于值为 ('a', 'c') 的元素,其二进制表示即为 0101。

更多信息参考 MySQL 集合文档

数据类型的默认值

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

BLOBTEXT 以及 JSON 不可以设置默认值。

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

  • 如果该类型可以使用 NULL 作为值,那么这个列会在定义时添加隐式的默认值设置 DEFAULT NULL
  • 如果该类型无法使用 NULL 作为值,那么这个列在定义时不会添加隐式的默认值设置。

对于一个设置了 NOT NULL 但是没有显式设置 DEFAULT 的列,当 INSERTREPLACE 没有涉及到该列的值时,TiDB 根据当时的 SQL_MODE 进行不同的行为:

  • 如果此时是 strict sql mode,在事务中的语句会导致事务失败并回滚,非事务中的语句会直接报错。
  • 如果此时不是 strict sql mode,TiDB 会为这列赋值为列数据类型的隐式默认值。

此时隐式默认值的设置按照如下规则:

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