常用数据类型

SQL新手 2022-12-08

164

在 OceanBase 数据库的 MySQL 模式下常用的数据类型包括：数值、日期时间、字符、大对象、文本、枚举、集合、JSON 等。本文为您介绍各数据类型及描述。

数值类型

数值类型包括：

整数类型：BOOL/BOOLEAN/TINYINT、SMALLINT、MEDIUMINT、INT/INTEGER 和 BIGINT。
定点类型：DECIMAL 和 NUMERIC。
浮点类型：FLOAT/DOUBLE。
Bit-Value 类型：BIT。

分类	类型	使用范围	描述
数值类型-整型	BIGINT	有符号：[-2⁶³, 2⁶³ - 1] 无符号：[0, 2⁶⁴ - 1]	用于表示一个大整数。
	INT INTEGER	有符号：[-2³¹, 2³¹ - 1] 无符号：[0, 2³² - 1]	用于表示一个正常大小的整数。
	SMALLINT	有符号：[-2¹⁵, 2¹⁵ - 1] 无符号：[0, 2¹⁶ - 1]	用于表示一个小值整数。
	BOOL BOOLEAN TINYINT	有符号：[-2⁷, 2⁷ - 1] 无符号：[0, 2⁸ - 1]	TINYINT 用于表示一个非常小的整数。 BOOL/BOOLEAN 类型是 TINYINT 的同义词。零值表示错误，非零值表示正确。
数值类型-定点	DECIMAL	值域和精度取决于 Precision 和 Scale，以及是否为无符号。M 是可以存储的总位数（Precision），D 是小数点后的位数（Scale）。小数点和负数符号“-”不计入 M。如果 D 为 0，则值没有小数点或小数部分。整数部分最大有效位数等于 M 减去 D 的值，即 Precision 减去 Scale 的值。所有带有 DECIMAL 列的基本计算（+、-、*、/）都使用 65 位的精度。	DECIMAL 等同于 NUMERIC。
数值类型-浮点	FLOAT	有符号：[-2¹²⁸, 2¹²⁸] 无符号：[-2¹⁰²⁴, 2¹⁰²⁴] 精度 7 位。	用于表示一个小的（单精度）浮点数。
数值类型-浮点	DOUBLE	有符号：[-2¹⁰²⁴, 2¹⁰²⁴] 无符号：[0, 2¹⁰²⁴] 精度 15 位。	用于表示正常大小（双精度）浮点数。
数值类型-整型/定点/浮点	NUMBER NUMBER(P) NUMBER(P, S)	P(Precision) 为精度，S(Scale) 表示小数点右边的数字个数，精度最大值为 38，Scale 的取值范围为 -84 到 127。 P 和 S 都有表示定点数。 S 为 0 表示整型。 P 和 S 都不指定，表示浮点数，最大精度 38。	包括整数、定点数、浮点数。
Bit-Value 类型	BIT	位数据类型，其数据有两种取值：0 和 1，长度为1位。	用于存储位值。

日期时间类型

日期时间类型包括：DATETIME、TIMESTAMP、DATE、TIME 和 YEAR。

分类	类型	使用范围	描述
时间类型	DATE	YYYY-MM-DD，只包含日期。	用于表示具有日期部分但没有时间部分的值。
	TIME	HH:MM:SS[.fraction]，只包含时间。	可以表示一天中的某个时间，还可以表示经过的时间或两个事件之间的时间间隔（可能远大于 24 小时）。
	DATETIME	YYYY-MM-DD HH:MM:SS[.fraction]，包含日期时间(不考虑时区)。	用于表示同时包含日期和时间部分的值。
	TIMESTAMP	日期时间(考虑时区)。	用于表示同时包含日期和时间部分的值。
	YEAR	YYYY,[1901, 2155]	用于表示 4 位数字格式的年份。

字符类型

字符类型包括：CHAR、VARCHAR、BINARY 和 VARBINARY。

分类	类型	使用范围	描述
字符类型-变长	VARCHAR(N)	最长 256K，字符集 UTF8MB4。	声明的长度为可以存储的最大字符数。
字符类型-变长	VARBINARY	最长 256K，字符集 BINARY。	与 VARCHAR 类型相似，但其存储二进制字节串。
字符类型-定长	CHAR(N)	最大 256，字符集 UTF8MB4。	声明的长度为可以存储的最大字符数。
字符类型-定长	BINARY	最大 256，字符集 BINARY。	与 CHAR 类型相似，但其存储二进制字节串。

大对象类型

大对象类型包括：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

分类	类型	使用范围	描述
类型	TINYBLOB	最大 256K，不超过 256 个字符的二进制文本数据。	是最大长度为 256 个字节的 BLOB 类型。
	BLOB	最大 65K，二进制形式的长文本数据。	是一个二进制大对象类型，最大长度为65536字节，可以存储可变数量的数据。
	MEDIUMBLOB	最大 16M，二进制形式的中等长度文本数据。	是最大长度为 16,777,216 字节的 BLOB 类型。
	LONGBLOB	最大 4G，二进制形式的极大文本数据。	列的最大长度为 65,536 个字节。

文本类型

文本类型包括：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

分类	类型	使用范围	描述
类型	TINYTEXT	最大 255 个字节，[2⁸ - 1]。	是最大长度为 256 个字符的 TEXT 类型。如果值中包含多字节字符，则最大有效长度会变小。
	TEXT	最大 65K，[2¹⁶ - 1]。	列的最大长度为 65,536 个字符。如果值包含多字节字符，则最大有效长度会变小。
	MEDIUMTEXT	最大 16M，[2²⁴-1]。	是最大长度为 16,777,216 个字符的 TEXT 类型。如果值包含多字节字符，则最大有效长度会变小。
	LONGTEXT	最大 48M。	是最大长度为 50331648 或 48 M 个字符的 TEXT 类型。如果值包含多字节字符，则最大有效长度会变小。LONGTEXT 列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。

枚举类型

枚举类型：是指只能在给定的范围内选一个值，如性别 sex 男M/女F。

分类	类型	使用范围	描述
类型	ENUM	最多 65535 个元素，每个元素最长 255 个字符，字符集 UTF8MB4。	是一个只能有一个值的字符串对象，该值必须来自列表 `'value1', 'value2', ...`，枚举值必须是带引号的字符串文字。

集合类型

集合类型：是指在给定的范围内可以选择一个或一个以上的值。

分类	类型	使用范围	描述
类型	SET	最多 65535 个元素，每个元素最长 255 个字符，字符集 UTF8MB4。	是一个字符串对象，它可以有零个或多个值，每个值都必须来自列表 `'Value1', 'Value2', ...`

JSON 数据类型

分类	类型	使用范围	描述
类型	JSON	数据可以是对象的形式，也可以是数组的形式。	是一种采用轻量的文本数据格式来存储和表示数据。

示例

示例 1

创建 全类型表。

CREATE TABLE `全类型表` (
  `col1` int(11) NOT NULL AUTO_INCREMENT COMMENT '第一列', 
  `col2` integer DEFAULT NULL COMMENT '第二列',
  `col3` decimal(10,2) DEFAULT NULL COMMENT '第三列',
  ` col4` number DEFAULT NULL COMMENT '第四列',
  `col5` tinyint(4) DEFAULT NULL COMMENT '第五列',
  `col6` smallint(6) DEFAULT NULL COMMENT '第六列',
  `col7` mediumint(9) DEFAULT NULL COMMENT '第七列',
  `col8` bigint(20) DEFAULT NULL COMMENT '第八列',
  `col9` float(10,2) DEFAULT NULL COMMENT '第九列',
  `col10` double(10,2) DEFAULT NULL COMMENT '第十列',
  `col11` varchar(10) DEFAULT NULL COMMENT '第十一列',
  `col12` char(10) NOT NULL DEFAULT '中文字段' COMMENT '第十二列',
  `col13` text DEFAULT NULL COMMENT '第十三列',
  `col14` tinytext DEFAULT NULL COMMENT '第十四列',
  `col15` mediumtext DEFAULT NULL COMMENT '第十五列',
  `col16` longtext DEFAULT NULL COMMENT '第十六列',
  `col17` blob DEFAULT NULL COMMENT '第十七列',
  `col18` tinyblob DEFAULT NULL COMMENT '第十八列',
  `col19` longblob DEFAULT NULL COMMENT '第十九列',
  `col20` mediumblob DEFAULT NULL COMMENT '第二十列',
  `col21` binary(16) DEFAULT NULL COMMENT '第二十一 列',
  `col22` varbinary(16) DEFAULT NULL COMMENT '第二十二列',
  `col23` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '第二十三列',
  `col24` time DEFAULT NULL COMMENT '第二十四列',
  `col25` date DEFAULT NULL COMMENT '第二十五列',
  `col26` datetime DEFAULT NULL COMMENT '第二十六列',
  `col27` year(4) DEFAULT NULL COMMENT '第二十七列',
  PRIMARY KEY (`col1`)
) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = '全类型表';

将数据插入 全类型表 。

obclient> INSERT INTO `全类型表` VALUES(101,2,3,4,5,6,7,8,9.1,10.1,'十一','十二','十三','十四','十五','十六','十七','十八','十九','二十','二十一','二十二','19700101','00:00:00','19700101','19700101','1970');
Query OK, 1 row affected

查询 全类型表。

obclient> select * from `全类型表`;
+------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
| col1 | col2 | col3 |  col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11  | col12  | col13  | col14  | col15  | col16  | col17  | col18  | col19  | col20  | col21            | col22     | col23               | col24    | col25      | col26               | col27 |
+------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
|  101 |    2 | 3.00 |     4 |    5 |    6 |    7 |    8 | 9.10 | 10.10 | 十一   | 十二   | 十三   | 十四   | 十五   | 十六   | 十七   | 十八   | 十九   | 二十   | 二十一           | 二十二    | 1970-01-01 00:00:00 | 00:00:00 | 1970-01-01 | 1970-01-01 00:00:00 |  1970 |
+------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
1 row in set

示例 2

创建 t_bool 表。

obclient> CREATE TABLE t_bool(c1 bool);
Query OK, 0 rows affecte

将数据插入 t_bool 表。

obclient> INSERT INTO t_bool VALUES(1);
Query OK, 1 row affected

查询 t_bool 表。

obclient> SELECT * FROM t_bool;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set

示例 3

创建 t_boolean 表。

obclient> CREATE TABLE t_boolean(c1 boolean);
Query OK, 0 rows affected

将数据插入 t_boolean 表。

obclient> INSERT INTO t_boolean VALUES(1);
Query OK, 1 row affected

查询 t_boolean 表。

obclient> SELECT * FROM t_boolean;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set

示例 4

创建 t_bit 表。

obclient> CREATE TABLE t_bit(c1 bit(8));
Query OK, 0 rows affected

将数据插入 t_bit 表。

obclient> INSERT INTO t_bit VALUES(101);
Query OK, 1 row affected

查询 t_bit 表。

obclient> SELECT c1,bin(c1),hex(c1) FROM t_bit;
+------+---------+---------+
| c1   | bin(c1) | hex(c1) |
+------+---------+---------+
| e    | 1100101 | 65      |
+------+---------+---------+
1 row in set

示例 5

创建 t_enum 表。

obclient> CREATE TABLE t_enum(c1 enum('RED','GREEN','BLUE'));
Query OK, 0 rows affected

将数据插入 t_enum 表。

obclient> INSERT INTO t_enum VALUES('RED');
Query OK, 1 row affected

查询 t_enum 表。

obclient>  SELECT * FROM t_enum;
+------+
| c1   |
+------+
| RED  |
+------+
1 row in set

示例 6