# 数据类型 MatrixOne 的数据类型与 MySQL 数据类型的定义一致，可参考 [MySQL 8.0 数据类型参考](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 ```text -- Create a table named "inttable" with 2 attributes of a "tinyint", a "tinyint unsigned", create table inttable ( a tinyint not null default 1, tinyint8 tinyint unsigned primary key); insert into inttable (tinyint8) values (0),(255), (0xFE), (253); mysql> select * from inttable order by 2 asc; +------+----------+ | a | tinyint8 | +------+----------+ | 1 | 0 | | 1 | 253 | | 1 | 254 | | 1 | 255 | +------+----------+ 4 rows in set (0.03 sec) ``` - SMALLINT 和 SMALLINT UNSIGNED ```text -- Create a table named "inttable" with 2 attributes of a "smallint", a "smallint unsigned", drop table inttable; create table inttable ( a smallint not null default 1, smallint16 smallint unsigned); insert into inttable (smallint16) values (0),(65535), (0xFFFE), (65534), (65533); mysql> select * from inttable; +------+------------+ | a | smallint16 | +------+------------+ | 1 | 0 | | 1 | 65535 | | 1 | 65534 | | 1 | 65534 | | 1 | 65533 | +------+------------+ 5 rows in set (0.01 sec) ``` - INT 和 INT UNSIGNED ```text -- Create a table named "inttable" with 2 attributes of a "int", a "int unsigned", drop table inttable; create table inttable ( a int not null default 1, int32 int unsigned primary key); insert into inttable (int32) values (0),(4294967295), (0xFFFFFFFE), (4294967293), (4294967291); mysql> select * from inttable order by a desc, 2 asc; +------+------------+ | a | int32 | +------+------------+ | 1 | 0 | | 1 | 4294967291 | | 1 | 4294967293 | | 1 | 4294967294 | | 1 | 4294967295 | +------+------------+ 5 rows in set (0.01 sec) ``` - BIGINT 和 BIGINT UNSIGNED ```text -- Create a table named "inttable" with 2 attributes of a "bigint", a "bigint unsigned", drop table inttable; create table inttable ( a bigint, big bigint unsigned primary key ); insert into inttable values (122345515, 0xFFFFFFFFFFFFE), (1234567, 0xFFFFFFFFFFFF0); mysql> select * from inttable; +-----------+------------------+ | a | big | +-----------+------------------+ | 122345515 | 4503599627370494 | | 1234567 | 4503599627370480 | +-----------+------------------+ 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。| ### 示例 ```text -- Create a table named "floatt1" with precision, a trailing zero is added when the number of bits falls short create table floatt1(a float(5, 2)); insert into floatt1 values(1), (2.5), (3.56), (4.678); mysql> select * from floatt1; +------+ | a | +------+ | 1.00 | | 2.50 | | 3.56 | | 4.68 | +------+ 4 rows in set (0.00 sec) -- Create a table named "floattable" with 1 attributes of a "float" create table floattable ( a float not null default 1, big float(20,5) primary key); insert into floattable (big) values (-1),(12345678.901234567),(92233720368547.75807); mysql> select * from floattable order by a desc, big asc; +------+----------------------+ | a | big | +------+----------------------+ | 1 | -1.00000 | | 1 | 12345679.00000 | | 1 | 92233718038528.00000 | +------+----------------------+ 3 rows in set (0.01 sec) mysql> select min(big),max(big),max(big)-1 from floattable; +----------+----------------------+----------------+ | min(big) | max(big) | max(big) - 1 | +----------+----------------------+----------------+ | -1.00000 | 92233718038528.00000 | 92233718038527 | +----------+----------------------+----------------+ 1 row in set (0.05 sec) ``` ## 二进制类型 | 数据类型 | 存储空间 | 最小值 | 最大值 | 语法表示 | 描述 | | --------| --------| ----- | -------------------- | -------- | ---- | | BIT | 1bytes | 0 | 18446744073709551615 | BIT(M) | 存储 bit 数据的数据类型，M 支持的范围：1 到 64。M 默认为 1，如果存储的数据不足 M bits，则靠左补 0 补齐长度 | ### 示例 ```text create table t1 (a bit); mysql> desc t1;--bit(M) 默认 M 为 1 +-------+--------+------+------+---------+-------+---------+ | Field | Type | Null | Key | Default | Extra | Comment | +-------+--------+------+------+---------+-------+---------+ | a | BIT(1) | YES | | NULL | | | +-------+--------+------+------+---------+-------+---------+ 1 row in set (0.01 sec) create table t2 (a bit(8)); -- 用 bit-value literal 语法赋值 insert into t2 values (0b1); insert into t2 values (b'1'); mysql> select * from t2; +------------+ | a | +------------+ | 0x01 | | 0x01 | +------------+ 2 rows in set (0.00 sec) truncate table t2; --用 hex-value literal 语法赋值 insert into t2 values (0x10); insert into t2 values (x'10'); mysql> select * from t2; +------------+ | a | +------------+ | 0x10 | | 0x10 | +------------+ 2 rows in set (0.00 sec) truncate table t2; --支持用 int 类型赋值，但是 int 的二进制表示长度不能超过 bit 类型的长度 insert into t2 values (255);--a = b'11111111' mysql> insert into t2 values (256);--256 的 2 进制表示长度超过 8 ERROR 20301 (HY000): invalid input: data too long, type width = 8, val = 100000000 mysql> select * from t2; +------------+ | a | +------------+ | 0xFF | +------------+ 1 row in set (0.00 sec) truncate table t2; --浮点型数据，会先将浮点型四舍五入转成 int 型，然后按照 int 类型赋值 insert into t2 values (2.1);--a = b'00000010' mysql> select * from t2; +------------+ | a | +------------+ | 0x02 | +------------+ 1 row in set (0.00 sec) truncate table t2; --字符型数据存储的是其编码值，同样整个字符串转换成的编码总长度不能超过 bit 类型 insert into t2 values ('a');--a = b'01100001' mysql> insert into t2 values ('啊');--utf8('啊') = 0xe5958a； ERROR 20301 (HY000): invalid input: data too long, type width = 8, val = 111001011001010110001010 mysql> select * from t2; +------------+ | a | +------------+ | 0x61 | +------------+ 1 row in set (0.00 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 ```text -- Create a table named "names" with 2 attributes of a "varchar" and a "char" create table names(name varchar(255),age char(255)); insert into names(name, age) values('Abby', '24'); insert into names(name, age) values("Bob", '25'); insert into names(name, age) values('Carol', "23"); insert into names(name, age) values("Dora", "29"); mysql> select name,age from names; +-------+------+ | name | age | +-------+------+ | Abby | 24 | | Bob | 25 | | Carol | 23 | | Dora | 29 | +-------+------+ 4 rows in set (0.00 sec) ``` - BINARY 和 VARBINARY ```text -- Create a table named "names" with 2 attributes of a "VARBINARY" and a "BINARY" create table names(name varbinary(255),age binary(255)); insert into names(name, age) values('Abby', '24'); insert into names(name, age) values("Bob", '25'); insert into names(name, age) values('Carol', "23"); insert into names(name, age) values("Dora", "29"); mysql> select name,age from names; +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | name | age | +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 0x41626279 | 0x323400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | | 0x426F62 | 0x323500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | | 0x4361726F6C | 0x323300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | | 0x446F7261 | 0x323900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.01 sec) ``` - TEXT ```text -- Create a table named "texttest" with 1 attribute of a "text" create table texttest (a text); insert into texttest values('abcdef'); insert into texttest values('_bcdef'); insert into texttest values('a_cdef'); insert into texttest values('ab_def'); insert into texttest values('abc_ef'); insert into texttest values('abcd_f'); insert into texttest values('abcde_'); mysql> select * from texttest where a like 'ab\_def' order by 1 asc; +--------+ | a | +--------+ | ab_def | +--------+ 1 row in set (0.01 sec) ``` - BLOB ```text -- Create a table named "blobtest" with 1 attribute of a "blob" create table blobtest (a blob); insert into blobtest values('abcdef'); insert into blobtest values('_bcdef'); insert into blobtest values('a_cdef'); insert into blobtest values('ab_def'); insert into blobtest values('abc_ef'); insert into blobtest values('abcd_f'); insert into blobtest values('abcde_'); mysql> select * from blobtest where a like 'ab\_def' order by 1 asc; +----------------+ | a | +----------------+ | 0x61625F646566 | +----------------+ 1 row in set (0.01 sec) ``` - ENUM ```text -- Create a table named "enumtest" with 1 attribute of a "enum" CREATE TABLE enumtest (color ENUM('red', 'green', 'blue')); INSERT INTO enumtest (color) VALUES ('red'); mysql> SELECT * FROM enumtest WHERE color = 'green'; +-------+ | color | +-------+ | green | +-------+ 1 row in set (0.01 sec) ``` ## JSON 数据类型 |JSON 数据类型 | 解释 | |---|---| |对象 |对象使用 `{}` 括起来，元素之间用 `,` 分隔。JSON 对象中的值/键可以为 String、Nubmber、Bool、时间。| |数组 | 数组使用 `[]` 括起来，元素之间用逗号 `,` 分隔。JSON 数组中值可以为 String、Nubmber、Bool、时间。| ### 示例 ```text -- Create a table named "jsontest" with 1 attribute of a "json" create table jsontest (a json,b int); insert into jsontest values ('{"t1":"a"}',1),('{"t1":"b"}',2); mysql> select * from jsontest; +-------------+------+ | a | b | +-------------+------+ | {"t1": "a"} | 1 | | {"t1": "b"} | 2 | +-------------+------+ 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| 时间与日期部分类型支持在插入数据时添加以下 hint 值： - `Time`：{t 'xx'}，{time 'xx'} - `Date`：{d 'xx'}，{date 'xx'} - `TIMESTAMP`：{ts 'xx'}，{timestamp 'xx'} ### 示例 - TIME ```text -- Create a table named "timetest" with 1 attributes of a "time" create table time_02(t1 time); insert into time_02 values(200),(time'23:29:30'),({t'12:11:12'}),(''); mysql> select * from time_02; +----------+ | t1 | +----------+ | 00:02:00 | | 23:29:30 | | 12:11:12 | | NULL | +----------+ 4 rows in set (0.01 sec) ``` - DATE ```text -- Create a table named "datetest" with 1 attributes of a "date" create table datetest (a date not null, primary key(a)); insert into datetest values ({d'2022-01-01'}), ('20220102'),(date'2022-01-03'),({d now()}); mysql> select * from datetest; +------------+ | a | +------------+ | 2022-01-01 | | 2022-01-02 | | 2022-01-03 | | 2024-03-19 | +------------+ 4 rows in set (0.00 sec) ``` - DATETIME ```text -- Create a table named "datetimetest" with 1 attributes of a "datetime" create table datetimetest (a datetime(0) not null, primary key(a)); insert into datetimetest values ('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345'); mysql> select * from datetimetest order by a asc; +---------------------+ | a | +---------------------+ | 2020-01-01 00:00:00 | | 2022-01-02 00:00:00 | | 2022-01-02 00:00:01 | | 2022-01-02 00:00:02 | +---------------------+ 4 rows in set (0.02 sec) ``` - TIMESTAMP ```text -- Create a table named "timestamptest" with 1 attribute of a "timestamp" create table timestamptest (a timestamp(0) not null, primary key(a)); insert into timestamptest values ('20200101000000'), (timestamp'2022-01-02 11:30:40'), ({ts'2022-01-02 00:00:01'}), ({ts current_timestamp}); mysql> select * from timestamptest; +---------------------+ | a | +---------------------+ | 2020-01-01 00:00:00 | | 2022-01-02 11:30:40 | | 2022-01-02 00:00:01 | | 2024-03-19 17:22:08 | +---------------------+ 4 rows in set (0.00 sec) ``` ## Bool 类型 | 数据类型 | 存储空间 | | ---- | ---- | | True | 1 byte | |False|1 byte| ### 示例 ```text -- Create a table named "booltest" with 2 attribute of a "boolean" and b "bool" create table booltest (a boolean,b bool); insert into booltest values (0,1),(true,false),(true,1),(0,false),(NULL,NULL); mysql> select * from booltest; +-------+-------+ | a | b | +-------+-------+ | false | true | | true | false | | true | true | | false | false | | NULL | NULL | +-------+-------+ 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。 | ### 示例 ```text -- Create a table named "decimalTest" with 2 attribute of a "decimal" and b "decimal" create table decimalTest(a decimal(6,3), b decimal(24,18)); insert into decimalTest values(123.4567, 123456.1234567891411241355); mysql> select * from decimalTest; +---------+---------------------------+ | a | b | +---------+---------------------------+ | 123.457 | 123456.123456789141124136 | +---------+---------------------------+ 1 row in set (0.01 sec) ``` ## UUID 类型 |UUID 类型 | 解释 | |---|---| |[UUID](uuid-type.md) | 由 32 个 16 进制数字和 4 个连字符‘-’组成 UUID 值，形式为 8-4-4-4-12，标准的 UUID 示例：`a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11`。| ### 示例 ```text -- 创建一个名为 't1' 的新表，并设置 'a' 列为 UUID 类型，同时将 'a' 列设置为主键 create table t1(a uuid primary key); -- 向 't1' 表的 'a' 列插入一个新的 UUID 值 insert into t1 values (uuid()); -- 查询 't1' 表中 'a' 列的值转换为字符串后的长度 mysql> select length(cast(a as varchar)) from t1; +----------------------------+ | length(cast(a as varchar)) | +----------------------------+ | 36 | +----------------------------+ 1 row in set (0.01 sec) -- 查询 t1 表中的所有记录，其值是一个 UUID mysql> select * from t1; +--------------------------------------+ | a | +--------------------------------------+ | 948d8e4e-1b00-11ee-b656-5ad2460dea50 | +--------------------------------------+ 1 row in set (0.00 sec) ``` ## 向量数据类型 |类型 | 解释 | |------------|--------------------- | |vecf32 | 向量列类型为 float32 | |vecf64 | 向量列类型为 float64 | ### 示例 ```text create table t1(n1 vecf32(3), n2 vecf64(2)); insert into t1 values("[1,2,3]",'[4,5]'); mysql> select * from t1; +-----------+--------+ | n1 | n2 | +-----------+--------+ | [1, 2, 3] | [4, 5] | +-----------+--------+ 1 row in set (0.00 sec) ``` ## Datalink 数据类型 |类型 | 解释 | |------------|--------------------- | |datalink | 用于存储指向文档 (例如 satge) 或文件链接的特殊数据类 | ### 示例 ```text drop table test01; create table test01 (col1 int, col2 datalink); create stage stage01 url='file:///Users/admin/case/'; insert into test01 values (1, 'file:///Users/admin/case/t1.csv'); insert into test01 values (2, 'file:///Users/admin/case/t1.csv?size=2'); insert into test01 values (3, 'file:///Users/admin/case/t1.csv?offset=4'); insert into test01 values (4, 'file:///Users/admin/case/t1.csv?offset=4&size=2'); insert into test01 values (5, 'stage://stage01/t1.csv'); insert into test01 values (6, 'stage://stage01/t1.csv?size=2'); insert into test01 values (7, 'stage://stage01/t1.csv?offset=4'); insert into test01 values (8, 'stage://stage01/t1.csv?offset=4&size=2'); mysql> select * from test01; +------+-------------------------------------------------+ | col1 | col2 | +------+-------------------------------------------------+ | 1 | file:///Users/admin/case/t1.csv | | 2 | file:///Users/admin/case/t1.csv?size=2 | | 3 | file:///Users/admin/case/t1.csv?offset=4 | | 4 | file:///Users/admin/case/t1.csv?offset=4&size=2 | | 5 | stage://stage01/t1.csv | | 6 | stage://stage01/t1.csv?size=2 | | 7 | stage://stage01/t1.csv?offset=4 | | 8 | stage://stage01/t1.csv?offset=4&size=2 | +------+-------------------------------------------------+ 8 rows in set (0.01 sec) ```