MatrixOne从入门到实践05——数据类型介绍

姚鹤龄

2023-12-01

MatrixOne从入门到实践——数据类型介绍

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

在MatrixOne建表时，给字段定义一个恰当的数据类型是十分重要的。比如一下原则：

尽可能使用数字类型来代替字符串类型
尽可能使用更加准确的大小来描述字段类型
如果数据没有Null存在，那么可以在定义字段时使用Not Null

类型介绍

整数类型

该类数据类型代表为严格数值类型

数据类型	存储空间	最小值	最大值
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

浮点类型

该类数据类型代表近似数值类型

数据类型	存储空间	精度	语法表示
FLOAT32	4 byte	23 bits	FLOAT
FLOAT64	8 byte	53 bits	DOUBLE

字符串类型

该类数据类型表示字符串类型

数据类型	存储空间	语法表示
String	24 byte	CHAR, VARCHAR

日期与时间类型

该类数据类型表示日期或者时间类型

数据类型	存储空间	精度	最小值	最大值	语法表示
Date	4 byte	day	1000-01-01	9999-12-31	YYYY-MM-DD
DateTime	8 byte	second	0001-01-01 00:00:00.000000	9999-12-31 23:59:59.999999	YYYY-MM-DD hh:mi:ssssss
TIMESTAMP	8 byte	second	1970-01-01 00:00:01.000000	2038-01-19 03:14:07.999999	YYYYMMDD hh:mi:ss.ssssss

Bool类型

该类型表示布尔值类型

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

精确数值类型Decimal

数据类型	存储空间	精度	语法表示
Decimal64	8 byte	19位	Decimal(N,S), N范围(1,18), S范围(0,N)
Decimal128	16 byte	38位	Decimal(N,S), N范围(19,38), S范围(0,N)

示例

整数类型

tinyint

mysql> select cast('127' as tinyint) ;
+----------------------+
| cast(127 as tinyint) |
+----------------------+
|                  127 |
+----------------------+
1 row in set (0.00 sec)

mysql> select cast('-128' as tinyint) ;
+-----------------------+
| cast(-128 as tinyint) |
+-----------------------+
|                  -128 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select cast('128' as tinyint) ;
ERROR 20201 (HY000): data out of range: data type int, value '128'
mysql> select cast('-129' as tinyint) ;
ERROR 20201 (HY000): data out of range: data type int, value '-129'

mysql> create table tinyint (id tinyint  NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from tinyint;
Empty set (0.00 sec)

mysql> insert into tinyint values('0');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tinyint;
+------+
| id   |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

mysql> insert into tinyint values('a');
ERROR 20204 (HY000): truncated type TINYINT value a for column id, 1
mysql> insert into tinyint values(a);
ERROR 20204 (HY000): truncated type TINYINT value a for column id, 1
mysql> insert into tinyint values(128);
ERROR 20204 (HY000): truncated type TINYINT value 128 for column id, 1

可以看出，tinyint的范围为 [-128,127]，超过这个范围或者不符合tinyint类型的数据插入都会报错。

SMALLINT

mysql> create table smallint (id smallint  NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from smallint;
Empty set (0.01 sec)

mysql> insert into smallint values('0');
Query OK, 1 row affected (0.01 sec)

mysql> select * from smallint;
+------+
| id   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> insert into smallint values('-32768');
Query OK, 1 row affected (0.00 sec)

mysql> select * from smallint;
+--------+
| id     |
+--------+
|      0 |
| -32768 |
+--------+
2 rows in set (0.01 sec)

mysql> insert into smallint values('-32767');
Query OK, 1 row affected (0.00 sec)

mysql> select * from smallint;
+--------+
| id     |
+--------+
|      0 |
| -32768 |
| -32767 |
+--------+
3 rows in set (0.00 sec)

mysql> insert into smallint values('-32769');
ERROR 20204 (HY000): truncated type SMALLINT value -32769 for column id, 1

可以看出，smallint的范围为 [-32768,32767]，超过这个范围或者不符合smallyint类型的数据插入都会报错。

INT

mysql> insert into int values('-32769');
Query OK, 1 row affected (0.00 sec)

mysql> select * from int;
+--------+
| id     |
+--------+
| -32769 |
+--------+
1 row in set (0.00 sec)

mysql> insert into int values('-2147483648');
Query OK, 1 row affected (0.00 sec)

mysql> insert into int values('-2147483649');
ERROR 20204 (HY000): truncated type INT value -2147483649 for column id, 1
mysql> select * from int;
+-------------+
| id          |
+-------------+
|      -32769 |
| -2147483648 |
+-------------+
2 rows in set (0.00 sec)

可以看出，int的范围为 [-2147483648,2147483647]，超过这个范围或者不符合int类型的数据插入都会报错。

BIGINT

mysql> create table bigint (id bigint  NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from bigint;
Empty set (0.00 sec)

mysql> insert into bigint values('-2147483649');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bigint values('9223372036854775808');
ERROR 20204 (HY000): truncated type BIGINT value 9223372036854775808 for column id, 1
mysql> insert into bigint values('-9223372036854775808');
Query OK, 1 row affected (0.00 sec)

mysql> select * from bigint;
+----------------------+
| id                   |
+----------------------+
|          -2147483649 |
| -9223372036854775808 |
+----------------------+
2 rows in set (0.00 sec)

取值范围如果加了unsigned，则最小值为0，最大值翻倍，如tinyint unsigned的取值范围为(0~255)。

浮点数类型

浮点型在数据库中存放的是近似值

数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

FLOAT32

mysql> select cast('123.45678' as float(5,3));
+--------------------------------+
| cast(123.45678 as float(5, 3)) |
+--------------------------------+
|                        123.457 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123.45678' as float(5,4));
+--------------------------------+
| cast(123.45678 as float(5, 4)) |
+--------------------------------+
|                       123.4568 |
+--------------------------------+
1 row in set (0.00 sec)

FLOAT64

mysql> select cast('123.45678905646545481' as double(14,9));
+----------------------------------------------+
| cast(123.45678905646545481 as double(14, 9)) |
+----------------------------------------------+
|                           123.45678905646545 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123.45678905646545481' as double(18,12));
+-----------------------------------------------+
| cast(123.45678905646545481 as double(18, 12)) |
+-----------------------------------------------+
|                            123.45678905646545 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123.45678905646545481' as double);
+---------------------------------------+
| cast(123.45678905646545481 as double) |
+---------------------------------------+
|                    123.45678905646545 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('12223.45678905646545481' as double);
+-----------------------------------------+
| cast(12223.45678905646545481 as double) |
+-----------------------------------------+
|                      12223.456789056465 |
+-----------------------------------------+
1 row in set (0.00 sec)

double 默认会保存17位数字

字符串类型

char

无法手动指定char的个数，没有字符个数的限制，目前版本没有限制长度大小，可以导入超过24 byte 的字符

mysql> select cast('aasdfqg' as char);
+-----------------------+
| cast(aasdfqg as char) |
+-----------------------+
| aasdfqg               |
+-----------------------+
1 row in set (0.00 sec)

varchar

无法手动指定char的个数，没有字符个数的限制，目前版本没有限制长度大小，可以导入超过24 byte 的字符串

mysql> select cast('aasdfqg' as varchar);
+--------------------------+
| cast(aasdfqg as varchar) |
+--------------------------+
| aasdfqg                  |
+--------------------------+
1 row in set (0.00 sec)

时间与日期类型

date

mysql> select cast ('2022-10-08 14:55:00' as date);
+-----------------------------------+
| cast(2022-10-08 14:55:00 as date) |
+-----------------------------------+
| 2022-10-08                        |
+-----------------------------------+
1 row in set (0.01 sec)

datetime

mysql> select cast ('2022-10-08 14:55:00' as datetime);
+-------------------------------------------+
| cast(2022-10-08 14:55:00 as datetime(26)) |
+-------------------------------------------+
| 2022-10-08 14:55:00                       |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast ('2022-10-08' as datetime);
+----------------------------------+
| cast(2022-10-08 as datetime(26)) |
+----------------------------------+
| 2022-10-08 00:00:00              |
+----------------------------------+
1 row in set (0.01 sec)

timestamp

mysql> select cast ('2022-10-08 14:55:00' as timestamp);
+--------------------------------------------+
| cast(2022-10-08 14:55:00 as timestamp(26)) |
+--------------------------------------------+
| 2022-10-08 14:55:00                        |
+--------------------------------------------+
1 row in set (0.00 sec)

Bool类型

除了数值 0 或者字符串 ‘0’ 能被转换为 FALSE，其余任何数值和字符串数值转换均为TRUE

mysql> select cast('465' as boolean);
+----------------------+
| cast(465 as boolean) |
+----------------------+
| true                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select cast(465 as boolean);
+----------------------+
| cast(465 as boolean) |
+----------------------+
| true                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select cast(0 as boolean);
+--------------------+
| cast(0 as boolean) |
+--------------------+
| false              |
+--------------------+
1 row in set (0.00 sec)

mysql> select cast('0' as boolean);
+--------------------+
| cast(0 as boolean) |
+--------------------+
| false              |
+--------------------+
1 row in set (0.00 sec)

精确数值类型Decimal

DECIMAL列的声明语法是DECIMAL(M, D). M是有效数字的位数，取值范围是1到38，D是小数位数，取值范围是1到38，但是不能大于M。如果不指定D，默认为0。如果不指定M，默认为10。

DECIMAL列的数值以二进制的形式进行存储，在MatrixOne内部，只有decimal64和decimal128这两种表示形式。在0-18位精度内，一个Decimal数值占用8个字节的存储空间，在19-38位精度内，一个Decimal数值占用16个字节的存储空间。

mysql> select cast('123.456' as Decimal(6,2));
+--------------------------------+
| cast(123.456 as decimal(6, 2)) |
+--------------------------------+
|                         123.46 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123.456' as Decimal(5,2));
+--------------------------------+
| cast(123.456 as decimal(5, 2)) |
+--------------------------------+
|                         123.46 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123.456' as Decimal(4,2));
ERROR 20203 (HY000): invalid argument DECIMAL64, bad value 123.456
mysql> select cast('123.456' as Decimal(5,3));
ERROR 20203 (HY000): invalid argument DECIMAL64, bad value 123.456
mysql> select cast('123.456' as Decimal(10,3));
+---------------------------------+
| cast(123.456 as decimal(10, 3)) |
+---------------------------------+
|                         123.456 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123.456' as Decimal(5,2));
+--------------------------------+
| cast(123.456 as decimal(5, 2)) |
+--------------------------------+
|                         123.46 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select cast('0.0456' as Decimal(5,2));
+-------------------------------+
| cast(0.0456 as decimal(5, 2)) |
+-------------------------------+
|                          0.05 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select cast('0.012456' as Decimal(5,2));
+---------------------------------+
| cast(0.012456 as decimal(5, 2)) |
+---------------------------------+
|                            0.01 |
+---------------------------------+
1 row in set (0.00 sec)

MatrixOne从入门到实践05——数据类型介绍

MatrixOne从入门到实践——数据类型介绍

类型介绍

整数类型

浮点类型

字符串类型

日期与时间类型

Bool类型

精确数值类型Decimal

示例

整数类型

浮点数类型

字符串类型

时间与日期类型

Bool类型

精确数值类型Decimal

相关阅读

相关文章

相关问答

相关文档