数据类型
- 整数类型
  - 示例
- 浮点类型
  - 示例
- 字符串类型
  - 示例
- JSON 数据类型
  - 示例
- 时间与日期
  - 示例
- Bool
  - 示例
- 定点类型 Decimal
  - 示例
- UUID 类型
  - 示例

数据类型

MatrixOne 的数据类型与 MySQL 数据类型的定义一致，可参考： <https://dev.mysql.com/doc/refman/8.0/en/data-types.html>

整数类型

数据类型	存储空间	最小值	最大值
TINYINT	1 byte	-128	127
SMALLINT	2 byte	-32768	32767
INT	4 byte	-2147483648	2147483647
BIGINT	8 byte	-9223372036854775808	9223372036854775807
TINYINT UNSIGNED	1 byte	0	255
SMALLINT UNSIGNED	2 byte	0	65535
INT UNSIGNED	4 byte	0	4294967295
BIGINT UNSIGNED	8 byte	0	18446744073709551615

示例

TINYINT and TINYINT UNSIGNED

1. -- Create a table named "inttable" with 2 attributes of a "tinyint", a "tinyint unsigned",
2. create table inttable ( a tinyint not null default 1, tinyint8 tinyint unsigned primary key);
3. insert into inttable (tinyint8) values (0),(255), (0xFE), (253);

5. mysql> select * from inttable order by 2 asc;
6. +------+----------+
7. | a    | tinyint8 |
8. +------+----------+
9. |    1 |        0 |
10. |    1 |      253 |
11. |    1 |      254 |
12. |    1 |      255 |
13. +------+----------+
14. 4 rows in set (0.03 sec)

SMALLINT 和 SMALLINT UNSIGNED

1. -- Create a table named "inttable" with 2 attributes of a "smallint", a "smallint unsigned",
2. drop table inttable;
3. create table inttable ( a smallint not null default 1, smallint16 smallint unsigned);
4. insert into inttable (smallint16) values (0),(65535), (0xFFFE), (65534), (65533);

6. mysql> select * from inttable;
7. +------+------------+
8. | a    | smallint16 |
9. +------+------------+
10. |    1 |          0 |
11. |    1 |      65535 |
12. |    1 |      65534 |
13. |    1 |      65534 |
14. |    1 |      65533 |
15. +------+------------+
16. 5 rows in set (0.01 sec)

INT 和 INT UNSIGNED

1. -- Create a table named "inttable" with 2 attributes of a "int", a "int unsigned",
2. drop table inttable;
3. create table inttable ( a int not null default 1, int32 int unsigned primary key);
4. insert into inttable (int32) values (0),(4294967295), (0xFFFFFFFE), (4294967293), (4294967291);

6. mysql> select * from inttable order by a desc, 2 asc;
7. +------+------------+
8. | a    | int32      |
9. +------+------------+
10. |    1 |          0 |
11. |    1 | 4294967291 |
12. |    1 | 4294967293 |
13. |    1 | 4294967294 |
14. |    1 | 4294967295 |
15. +------+------------+
16. 5 rows in set (0.01 sec)

BIGINT 和 BIGINT UNSIGNED

1. -- Create a table named "inttable" with 2 attributes of a "bigint", a "bigint unsigned",
2. drop table inttable;
3. create table inttable ( a bigint, big bigint unsigned primary key );
4. insert into inttable values (122345515, 0xFFFFFFFFFFFFE), (1234567, 0xFFFFFFFFFFFF0);

6. mysql> select * from inttable;
7. +-----------+------------------+
8. | a         | big              |
9. +-----------+------------------+
10. | 122345515 | 4503599627370494 |
11. |   1234567 | 4503599627370480 |
12. +-----------+------------------+
13. 2 rows in set (0.01 sec)

浮点类型

数据类型	存储空间	精度	最小值	最大值	语法表示
FLOAT32	4 bytes	23 bits	-3.40282e+038	3.40282e+038	FLOAT(M, D) M 表示的是最大长度，D 表示的显示的小数位数。M 的取值范围为（1=< M <=255）。 D 的取值范围为（1=< D <=30），且 M >= D。带精度的浮点数展示出要求精度的位数，在位数不足时，会进行末尾补 0。
FLOAT64	8 bytes	53 bits	-1.79769e+308	1.79769e+308	DOUBLE(M, D) M 表示的是最大长度，D 表示的显示的小数位数。M 的取值范围为（1=< M <=255）。 D 的取值范围为（1=< D <=30），且 M >= D。带精度的浮点数展示出要求精度的位数，在位数不足时，会进行末尾补 0。

示例

1. -- Create a table named "floatt1" with precision, a trailing zero is added when the number of bits falls short
2. create table floatt1(a float(5, 2));
3. insert into floatt1 values(1), (2.5), (3.56), (4.678);

5. mysql> select * from floatt1;
6. +------+
7. | a    |
8. +------+
9. | 1.00 |
10. | 2.50 |
11. | 3.56 |
12. | 4.68 |
13. +------+
14. 4 rows in set (0.00 sec)

16. -- Create a table named "floattable" with 1 attributes of a "float"
17. create table floattable ( a float not null default 1, big float(20,5) primary key);
18. insert into floattable (big) values (-1),(12345678.901234567),(92233720368547.75807);

20. mysql> select * from floattable order by a desc, big asc;
21. +------+----------------------+
22. | a    | big                  |
23. +------+----------------------+
24. |    1 |             -1.00000 |
25. |    1 |       12345679.00000 |
26. |    1 | 92233718038528.00000 |
27. +------+----------------------+
28. 3 rows in set (0.01 sec)

30. mysql> select min(big),max(big),max(big)-1 from floattable;
31. +----------+----------------------+----------------+
32. | min(big) | max(big)             | max(big) - 1   |
33. +----------+----------------------+----------------+
34. | -1.00000 | 92233718038528.00000 | 92233718038527 |
35. +----------+----------------------+----------------+
36. 1 row in set (0.05 sec)

字符串类型

数据类型	存储空间	长度	语法表示	描述
char	24 bytes	0 ~ 4294967295	CHAR	定长字符串
varchar	24 bytes	0 ~ 4294967295	VARCHAR	变长字符串
binary	255 bytes	0 ~ 65535	BINARY(M)	类似于 CHAR，二进制字符串
varbinary	255 bytes	0 ~ 65535	VARBINARY(M)	类似于 VARCHAR，二进制字符串
text	1 GB	other types mapping	TEXT	长文本数据，不区分 TINY TEXT、MEDIUM TEXT 和 LONG TEXT
blob	1 GB	other types mapping	BLOB	二进制的长文本数据，不区分 TINY BLOB、MEDIUM BLOB 和 LONGBLOB
enum	1 byte 或 2 bytes	0 ~ 65535	enum	一个枚举类型。它是一个字符串对象，只能从 `value1`、`value2` 等值列表中选择一个值，或者是 `NULL` 或特殊的 ‘’ 错误值。枚举值在内部表示为整数。

示例

CHAR 和 VARCHAR

1. -- Create a table named "names" with 2 attributes of a "varchar" and a "char"
2. create table names(name varchar(255),age char(255));
3. insert into names(name, age) values('Abby', '24');
4. insert into names(name, age) values("Bob", '25');
5. insert into names(name, age) values('Carol', "23");
6. insert into names(name, age) values("Dora", "29");

8. mysql> select name,age from names;
9. +-------+------+
10. | name  | age  |
11. +-------+------+
12. | Abby  | 24   |
13. | Bob   | 25   |
14. | Carol | 23   |
15. | Dora  | 29   |
16. +-------+------+
17. 4 rows in set (0.00 sec)

BINARY 和 VARBINARY

1. -- Create a table named "names" with 2 attributes of a "VARBINARY" and a "BINARY"
2. create table names(name varbinary(255),age binary(255));
3. insert into names(name, age) values('Abby', '24');
4. insert into names(name, age) values("Bob", '25');
5. insert into names(name, age) values('Carol', "23");
6. insert into names(name, age) values("Dora", "29");

8. mysql> select name,age from names;
9. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10. | name         | age                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
11. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. | 0x41626279   | 0x323400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
13. | 0x426F62     | 0x323500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
14. | 0x4361726F6C | 0x323300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
15. | 0x446F7261   | 0x323900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
16. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17. 4 rows in set (0.01 sec)

TEXT

1. -- Create a table named "texttest" with 1 attribute of a "text"
2. create table texttest (a text);
3. insert into texttest values('abcdef');
4. insert into texttest values('_bcdef');
5. insert into texttest values('a_cdef');
6. insert into texttest values('ab_def');
7. insert into texttest values('abc_ef');
8. insert into texttest values('abcd_f');
9. insert into texttest values('abcde_');

11. mysql> select * from texttest where a like 'ab\_def' order by 1 asc;
12. +--------+
13. | a      |
14. +--------+
15. | ab_def |
16. +--------+
17. 1 row in set (0.01 sec)

BLOB

1. -- Create a table named "blobtest" with 1 attribute of a "blob"
2. create table blobtest (a blob);
3. insert into blobtest values('abcdef');
4. insert into blobtest values('_bcdef');
5. insert into blobtest values('a_cdef');
6. insert into blobtest values('ab_def');
7. insert into blobtest values('abc_ef');
8. insert into blobtest values('abcd_f');
9. insert into blobtest values('abcde_');

11. mysql> select * from blobtest where a like 'ab\_def' order by 1 asc;
12. +----------------+
13. | a              |
14. +----------------+
15. | 0x61625F646566 |
16. +----------------+
17. 1 row in set (0.01 sec)

ENUM

1. -- Create a table named "enumtest" with 1 attribute of a "enum"
2. CREATE TABLE enumtest (color ENUM('red', 'green', 'blue'));
3. INSERT INTO enumtest (color) VALUES ('red');
4. mysql> SELECT * FROM enumtest WHERE color = 'green';
5. +-------+
6. | color |
7. +-------+
8. | green |
9. +-------+
10. 1 row in set (0.01 sec)

JSON 数据类型

JSON 数据类型	解释
对象	对象使用 `{}` 括起来，元素之间用 `,` 分隔。JSON 对象中的值/键可以为 String、Nubmber、Bool、时间。
数组	数组使用 `[]` 括起来，元素之间用逗号 `,` 分隔。JSON 数组中值可以为 String、Nubmber、Bool、时间。

示例

1. -- Create a table named "jsontest" with 1 attribute of a "json"
2. create table jsontest (a json,b int);
3. insert into jsontest values ('{"t1":"a"}',1),('{"t1":"b"}',2);

5. mysql> select * from jsontest;
6. +-------------+------+
7. | a           | b    |
8. +-------------+------+
9. | {"t1": "a"} |    1 |
10. | {"t1": "b"} |    2 |
11. +-------------+------+
12. 2 rows in set (0.01 sec)

时间与日期

数据类型	存储空间	精度	最小值	最大值	语法表示
Time	8 byte	microsecond	-2562047787:59:59.999999	2562047787:59:59.999999	hh:mm:ss.ssssss
Date	4 byte	day	0001-01-01	9999-12-31	YYYY-MM-DD/YYYYMMDD
DateTime	8 byte	microsecond	0001-01-01 00:00:00.000000	9999-12-31 23:59:59.999999	YYYY-MM-DD hh:mi:ssssss
TIMESTAMP	8 byte	microsecond	0001-01-01 00:00:00.000000	9999-12-31 23:59:59.999999	YYYYMMDD hh:mi:ss.ssssss

示例

TIME

1. -- Create a table named "timetest" with 1 attributes of a "time"
2. create table time_02(t1 time);
3. insert into time_02 values(200);
4. insert into time_02 values("");

6. mysql> select * from time_02;
7. +----------+
8. | t1       |
9. +----------+
10. | 00:02:00 |
11. | NULL     |
12. +----------+
13. 2 rows in set (0.00 sec)

DATE

1. -- Create a table named "datetest" with 1 attributes of a "date"
2. create table datetest (a date not null, primary key(a));
3. insert into datetest values ('2022-01-01'), ('20220102'),('2022-01-03'),('20220104');

5. mysql> select * from datetest order by a asc;
6. +------------+
7. | a          |
8. +------------+
9. | 2022-01-01 |
10. | 2022-01-02 |
11. | 2022-01-03 |
12. | 2022-01-04 |
13. +------------+

DATETIME

1. -- Create a table named "datetimetest" with 1 attributes of a "datetime"
2. create table datetimetest (a datetime(0) not null, primary key(a));
3. insert into datetimetest values ('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345');

5. mysql> select * from datetimetest order by a asc;
6. +---------------------+
7. | a                   |
8. +---------------------+
9. | 2020-01-01 00:00:00 |
10. | 2022-01-02 00:00:00 |
11. | 2022-01-02 00:00:01 |
12. | 2022-01-02 00:00:02 |
13. +---------------------+
14. 4 rows in set (0.02 sec)

TIMESTAMP

1. -- Create a table named "timestamptest" with 1 attribute of a "timestamp"
2. create table timestamptest (a timestamp(0) not null, primary key(a));
3. insert into timestamptest values ('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345');

5. mysql> select * from timestamptest;
6. +---------------------+
7. | a                   |
8. +---------------------+
9. | 2020-01-01 00:00:00 |
10. | 2022-01-02 00:00:00 |
11. | 2022-01-02 00:00:01 |
12. | 2022-01-02 00:00:02 |
13. +---------------------+

Bool

数据类型	存储空间
True	1 byte
False	1 byte

示例

1. -- Create a table named "booltest" with 2 attribute of a "boolean" and b "bool"
2. create table booltest (a boolean,b bool);
3. insert into booltest values (0,1),(true,false),(true,1),(0,false),(NULL,NULL);

5. mysql> select * from booltest;
6. +-------+-------+
7. | a     | b     |
8. +-------+-------+
9. | false | true  |
10. | true  | false |
11. | true  | true  |
12. | false | false |
13. | NULL  | NULL  |
14. +-------+-------+
15. 5 rows in set (0.00 sec)

定点类型 Decimal

数据类型	存储空间	精度	语法表示
Decimal64	8 byte	18 位	Decimal(N,S) N 表示数字位数的总数，范围是 (1 ~ 18)，小数点和 -（负数）符号不包括在 N 中。如果 N 被省略，默认值应该取最大，即取值 18。 S 表示是小数点（标度）后面的位数，范围是 (0 ~ N) 如果 S 是 0，则值没有小数点或分数部分。如果 S 被省略，默认是 0，例如 Decimal(10)，等同于 Decimal(10, 0) 例如 Decimal(10,8)，即表示数字总长度为 10，小数位为 8。
Decimal128	16 byte	38 位	Decimal(N,S) N 表示数字位数的总数，范围是 (18 ~ 38)，小数点和 -（负数）符号不包括在 N 中。如果 N 被省略，默认值应该取最大，即取值 38。 S 表示是小数点（标度）后面的位数，范围是 (0 ~ N) 如果 S 是 0，则值没有小数点或分数部分。如果 S 被省略，默认是 0，例如 Decimal(20)，等同于 Decimal(20, 0)。例如 Decimal(20,19)，即表示数字总长度为 20，小数位为 19。

示例

1. -- Create a table named "decimalTest" with 2 attribute of a "decimal" and b "decimal"
2. create table decimalTest(a decimal(6,3), b decimal(24,18));
3. insert into decimalTest values(123.4567, 123456.1234567891411241355);

5. mysql> select * from decimalTest;
6. +---------+---------------------------+
7. | a       | b                         |
8. +---------+---------------------------+
9. | 123.457 | 123456.123456789141124136 |
10. +---------+---------------------------+
11. 1 row in set (0.01 sec)

UUID 类型

UUID 类型	解释
UUID	由 32 个 16 进制数字和 4 个连字符‘-’组成 UUID 值，形式为 8-4-4-4-12，标准的 UUID 示例：`a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11`。

示例

1. -- 创建一个名为 't1' 的新表，并设置 'a' 列为 UUID 类型，同时将 'a' 列设置为主键
2. create table t1(a uuid primary key);

4. -- 向 't1' 表的 'a' 列插入一个新的 UUID 值
5. insert into t1 values (uuid());

7. -- 查询 't1' 表中 'a' 列的值转换为字符串后的长度
8. mysql> select length(cast(a as varchar)) from t1;
9. +----------------------------+
10. | length(cast(a as varchar)) |
11. +----------------------------+
12. |                         36 |
13. +----------------------------+
14. 1 row in set (0.01 sec)

16. -- 查询 t1 表中的所有记录，其值是一个 UUID
17. mysql> select * from t1;
18. +--------------------------------------+
19. | a                                    |
20. +--------------------------------------+
21. | 948d8e4e-1b00-11ee-b656-5ad2460dea50 |
22. +--------------------------------------+
23. 1 row in set (0.00 sec)

数据类型概览

数据类型

整数类型

示例

浮点类型

示例

字符串类型

示例

JSON 数据类型

示例

时间与日期

示例

Bool

示例

定点类型 Decimal

示例

UUID 类型

示例