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

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)
posted @ 2022-10-10 09:21  自由如风fly  阅读(19)  评论(0)  编辑  收藏  举报