TiDB数据类型

柔情只为你懂 2023-06-16 12:53 4阅读 0赞

# 数据类型概述 #

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

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

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

# 数据类型的默认值 #

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

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

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

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

对于一个设置了 `NOT NULL` 但是没有显式设置`DEFAULT` 的列，当 `INSERT`、`REPLACE` 没有涉及到该列的值时，TiDB 根据当时的 `SQL_MODE` 进行不同的行为：

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

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

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

# 数值类型 #

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

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

## 整数类型 ##

TiDB 支持 MySQL 所有的整数类型，包括`INTEGER`/`INT`、`TINYINT`、`SMALLINT`、`MEDIUMINT` 以及`BIGINT`，完整信息参考[这篇][Link 1]文档。  
字段说明：

<table> 
 <thead> 
  <tr> 
   <th>语法元素</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>M</td> 
   <td>类型显示宽度，可选</td> 
  </tr> 
  <tr> 
   <td>UNSIGNED</td> 
   <td>无符号数，如果不加这个标识，则为有符号数</td> 
  </tr> 
  <tr> 
   <td>ZEROFILL</td> 
   <td>补零标识，如果有这个标识，TiDB 会自动给类型增加 UNSIGNED 标识，但是没有做补零的操作</td> 
  </tr> 
 </tbody> 
</table>

### 类型定义 ###

#### `BIT` 类型 ####

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

BIT[(M)]

#### `BOOLEAN` 类型 ####

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

BOOLEAN

#### `TINYINT` 类型 ####

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

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

#### `SMALLINT` 类型 ####

`SMALLINT` 类型。有符号数的范围是 `[-32768, 32767]`。无符号数的范围是 `[0, 65535]`。

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

#### `MEDIUMINT` 类型 ####

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

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

#### `INTEGER` 类型 ####

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

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

或者：

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

#### `BIGINT` 类型 ####

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

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

#### 存储空间以及取值范围 ####

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

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

## 浮点类型 ##

TiDB 支持 MySQL 所有的浮点类型，包括 FLOAT、DOUBLE，完整信息参考[这篇][Link 2]博客。  
字段说明：

<table> 
 <thead> 
  <tr> 
   <th>语法元素</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>M</td> 
   <td>小数总位数</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>小数点后位数</td> 
  </tr> 
  <tr> 
   <td>UNSIGNED</td> 
   <td>无符号数，如果不加这个标识，则为有符号数</td> 
  </tr> 
  <tr> 
   <td>ZEROFILL</td> 
   <td>补零标识，如果有这个标识，TiDB会自动给类型增加UNSIGNED标识</td> 
  </tr> 
 </tbody> 
</table>

### 类型定义 ###

#### `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]

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

#### `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]

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

#### 存储空间 ####

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

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>FLOAT</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>FLOAT§</td> 
   <td>如果 0 &lt;= p &lt;= 24 为 4 个字节, 如果 25 &lt;= p &lt;= 53 为 8 个字节</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8</td> 
  </tr> 
 </tbody> 
</table>

## 定点类型 ##

TiDB 支持 MySQL 所有的定点类型，包括 DECIMAL、NUMERIC，完整信息参考这篇博客。  
字段说明：

<table> 
 <thead> 
  <tr> 
   <th>语法元素</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>M</td> 
   <td>小数总位数</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>小数点后位数</td> 
  </tr> 
  <tr> 
   <td>UNSIGNED</td> 
   <td>无符号数，如果不加这个标识，则为有符号数</td> 
  </tr> 
  <tr> 
   <td>ZEROFILL</td> 
   <td>补零标识，如果有这个标识，TiDB 会自动给类型增加 UNSIGNED 标识</td> 
  </tr> 
 </tbody> 
</table>

### 类型定义 ###

#### `DECIMAL` 类型 ####

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

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

# 日期和时间类型 #

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

### 类型定义 ###

#### `DATE` 类型 ####

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

DATE

#### `TIME` 类型 ####

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

TIME[(fsp)]

大写的注意：

`注意 TIME 的缩写形式。例如，‘11:12’ 表示 ‘11:12:00’ 而不是 ‘00:11:12’。但是，’1112’ 表示 ‘00:11:12’。这些差异取决于 : 字符的存在与否。`

#### `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。

DATETIME[(fsp)]

#### `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’。

TIMESTAMP[(fsp)]

#### `YEAR` 类型 ####

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

YEAR[(4)]

# 字符串类型 #

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

### 类型定义 ###

#### `CHAR` 类型 ####

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

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

#### `VARCHAR` 类型 ####

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

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

#### `TEXT` 类型 ####

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

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

#### `TINYTEXT` 类型 ####

类似于 `TEXT`，区别在于最大列长度为 255。

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

#### `MEDIUMTEXT` 类型 ####

类似于 `TEXT`，区别在于最大列长度为 16,777,215。

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

#### `LONGTEXT` 类型 ####

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

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

#### `BINARY`类型 ####

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

BINARY(M)

#### `VARBINARY`类型 ####

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

VARBINARY(M)

#### `TINYBLOB` 类型 ####

类似于 `BLOB`，区别在于最大列长度为 255。

TINYBLOB

#### `BLOB` 类型 ####

二进制大文件。`M`表示最大列长度，范围是 0 到 65535。

BLOB[(M)]

#### `MEDIUMBLOB` 类型 ####

类似于 `BLOB`，区别在于最大列长度为 16,777,215。

MEDIUMBLOB

#### `LONGBLOB` 类型 ####

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

LONGBLOB

#### `ENUM` 类型 ####

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

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

例如：

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

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

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>数字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>"</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>‘apple’</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>‘orange’</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>‘pear’</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

更多信息参考 [MySQL 枚举文档][MySQL]。

#### `SET` 类型 ####

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

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：

<table> 
 <thead> 
  <tr> 
   <th>成员</th> 
   <th>十进制表示</th> 
   <th>二进制表示</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>‘a’</td> 
   <td>1</td> 
   <td>0001</td> 
  </tr> 
  <tr> 
   <td>‘b’</td> 
   <td>2</td> 
   <td>0010</td> 
  </tr> 
  <tr> 
   <td>‘c’</td> 
   <td>4</td> 
   <td>0100</td> 
  </tr> 
  <tr> 
   <td>‘d’</td> 
   <td>8</td> 
   <td>1000</td> 
  </tr> 
 </tbody> 
</table>

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

更多信息参考 [MySQL 集合文档][MySQL 1]。

# `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 (id,detail) VALUES (1, '{"name": "Beijing", "population": 100}');
    SELECT id FROM city WHERE population >= 100;

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html
[Link 3]: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
[Link 4]: https://dev.mysql.com/doc/refman/5.7/en/string-types.html
[MySQL]: https://dev.mysql.com/doc/refman/5.7/en/enum.html
[MySQL 1]: https://dev.mysql.com/doc/refman/5.7/en/set.html