数据类型

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

整数类型

数据类型存储空间最小值最大值
TINYINT1 byte-128127
SMALLINT2 byte-3276832767
INT4 byte-21474836482147483647
BIGINT8 byte-92233720368547758089223372036854775807
TINYINT UNSIGNED1 byte0255
SMALLINT UNSIGNED2 byte065535
INT UNSIGNED4 byte04294967295
BIGINT UNSIGNED8 byte018446744073709551615

示例

  • 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);
  4. mysql> select * from inttable order by 2 asc;
  5. +------+----------+
  6. | a | tinyint8 |
  7. +------+----------+
  8. | 1 | 0 |
  9. | 1 | 253 |
  10. | 1 | 254 |
  11. | 1 | 255 |
  12. +------+----------+
  13. 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);
  5. mysql> select * from inttable;
  6. +------+------------+
  7. | a | smallint16 |
  8. +------+------------+
  9. | 1 | 0 |
  10. | 1 | 65535 |
  11. | 1 | 65534 |
  12. | 1 | 65534 |
  13. | 1 | 65533 |
  14. +------+------------+
  15. 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);
  5. mysql> select * from inttable order by a desc, 2 asc;
  6. +------+------------+
  7. | a | int32 |
  8. +------+------------+
  9. | 1 | 0 |
  10. | 1 | 4294967291 |
  11. | 1 | 4294967293 |
  12. | 1 | 4294967294 |
  13. | 1 | 4294967295 |
  14. +------+------------+
  15. 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 primary key );
  4. insert into inttable values (122345515, 0xFFFFFFFFFFFFE), (1234567, 0xFFFFFFFFFFFF0);
  5. mysql> select * from inttable;
  6. +-----------+------------------+
  7. | a | big |
  8. +-----------+------------------+
  9. | 122345515 | 4503599627370494 |
  10. | 1234567 | 4503599627370480 |
  11. +-----------+------------------+
  12. 2 rows in set (0.01 sec)

浮点类型

数据类型存储空间精度最小值最大值语法表示
FLOAT324 bytes23 bits-3.40282e+0383.40282e+038FLOAT(M, D)
M 表示的是最大长度,D 表示的显示的小数位数。M 的取值范围为(1=< M <=255)。
D 的取值范围为(1=< D <=30),且 M >= D。
带精度的浮点数展示出要求精度的位数,在位数不足时,会进行末尾补 0。
FLOAT648 bytes53 bits-1.79769e+3081.79769e+308DOUBLE(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);
  4. mysql> select * from floatt1;
  5. +------+
  6. | a |
  7. +------+
  8. | 1.00 |
  9. | 2.50 |
  10. | 3.56 |
  11. | 4.68 |
  12. +------+
  13. 4 rows in set (0.00 sec)
  14. -- Create a table named "floattable" with 1 attributes of a "float"
  15. create table floattable ( a float not null default 1, big float(20,5) primary key);
  16. insert into floattable (big) values (-1),(12345678.901234567),(92233720368547.75807);
  17. mysql> select * from floattable order by a desc, big asc;
  18. +------+----------------------+
  19. | a | big |
  20. +------+----------------------+
  21. | 1 | -1.00000 |
  22. | 1 | 12345679.00000 |
  23. | 1 | 92233718038528.00000 |
  24. +------+----------------------+
  25. 3 rows in set (0.01 sec)
  26. mysql> select min(big),max(big),max(big)-1 from floattable;
  27. +----------+----------------------+----------------+
  28. | min(big) | max(big) | max(big) - 1 |
  29. +----------+----------------------+----------------+
  30. | -1.00000 | 92233718038528.00000 | 92233718038527 |
  31. +----------+----------------------+----------------+
  32. 1 row in set (0.05 sec)

字符串类型

数据类型存储空间长度语法表示描述
char24 bytes0 ~ 4294967295CHAR定长字符串
varchar24 bytes0 ~ 4294967295VARCHAR变长字符串
binary255 bytes0 ~ 65535BINARY(M)类似于 CHAR,二进制字符串
varbinary255 bytes0 ~ 65535VARBINARY(M)类似于 VARCHAR,二进制字符串
text1 GBother types mappingTEXT长文本数据,不区分 TINY TEXT、MEDIUM TEXT 和 LONG TEXT
blob1 GBother types mappingBLOB二进制的长文本数据,不区分 TINY BLOB、MEDIUM BLOB 和 LONGBLOB

示例

  • 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");
  7. mysql> select name,age from names;
  8. +-------+------+
  9. | name | age |
  10. +-------+------+
  11. | Abby | 24 |
  12. | Bob | 25 |
  13. | Carol | 23 |
  14. | Dora | 29 |
  15. +-------+------+
  16. 4 rows in set (0.00 sec)
  • BINARY 和 VARBINARY
  1. -- Create a table named "names" with 2 attributes of a "varchar" and a "char"
  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");
  7. mysql> select name,age from names;
  8. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  9. | name | age |
  10. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. | 0x41626279 | 0x323400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
  12. | 0x426F62 | 0x323500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
  13. | 0x4361726F6C | 0x323300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
  14. | 0x446F7261 | 0x323900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
  15. +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. 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_');
  10. mysql> select * from texttest where a like 'ab\_def' order by 1 asc;
  11. +--------+
  12. | a |
  13. +--------+
  14. | ab_def |
  15. +--------+
  16. 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_');
  10. mysql> select * from blobtest where a like 'ab\_def' order by 1 asc;
  11. +----------------+
  12. | a |
  13. +----------------+
  14. | 0x61625F646566 |
  15. +----------------+
  16. 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);
  4. mysql> select * from jsontest;
  5. +-------------+------+
  6. | a | b |
  7. +-------------+------+
  8. | {"t1": "a"} | 1 |
  9. | {"t1": "b"} | 2 |
  10. +-------------+------+
  11. 2 rows in set (0.01 sec)

时间与日期

数据类型存储空间精度最小值最大值语法表示
Time8 bytemicrosecond-2562047787:59:59.9999992562047787:59:59.999999hh:mm:ss.ssssss
Date4 byteday0001-01-019999-12-31YYYY-MM-DD/YYYYMMDD
DateTime8 bytemicrosecond0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999YYYY-MM-DD hh:mi:ssssss
TIMESTAMP8 bytemicrosecond0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999YYYYMMDD 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("");
  5. mysql> select * from time_02;
  6. +----------+
  7. | t1 |
  8. +----------+
  9. | 00:02:00 |
  10. | NULL |
  11. +----------+
  12. 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');
  4. mysql> select * from datetest order by a asc;
  5. +------------+
  6. | a |
  7. +------------+
  8. | 2022-01-01 |
  9. | 2022-01-02 |
  10. | 2022-01-03 |
  11. | 2022-01-04 |
  12. +------------+
  • 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');
  4. mysql> select * from datetimetest order by a asc;
  5. +---------------------+
  6. | a |
  7. +---------------------+
  8. | 2020-01-01 00:00:00 |
  9. | 2022-01-02 00:00:00 |
  10. | 2022-01-02 00:00:01 |
  11. | 2022-01-02 00:00:02 |
  12. +---------------------+
  13. 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');
  4. mysql> select * from timestamptest;
  5. +---------------------+
  6. | a |
  7. +---------------------+
  8. | 2020-01-01 00:00:00 |
  9. | 2022-01-02 00:00:00 |
  10. | 2022-01-02 00:00:01 |
  11. | 2022-01-02 00:00:02 |
  12. +---------------------+

Bool

数据类型存储空间
True1 byte
False1 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);
  4. mysql> select * from booltest;
  5. +-------+-------+
  6. | a | b |
  7. +-------+-------+
  8. | false | true |
  9. | true | false |
  10. | true | true |
  11. | false | false |
  12. | NULL | NULL |
  13. +-------+-------+
  14. 5 rows in set (0.00 sec)

定点类型 Decimal

数据类型存储空间精度语法表示
Decimal648 byte18 位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。
Decimal12816 byte38 位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);
  4. mysql> select * from decimalTest;
  5. +---------+---------------------------+
  6. | a | b |
  7. +---------+---------------------------+
  8. | 123.457 | 123456.123456789141124136 |
  9. +---------+---------------------------+
  10. 1 row in set (0.01 sec)