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

头像
MatrixOrigin
     上海
    阅读 10 分钟
    头图

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

    MatrixOne的数据类型是和MySQL数据类型定义一致，可以参考：https://dev.mysql.com/doc/ref...

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

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

    类型介绍

    整数类型

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

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

    浮点类型

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

    数据类型存储空间精度语法表示
    FLOAT324 byte23 bitsFLOAT
    FLOAT648 byte53 bitsDOUBLE

    字符串类型

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

    数据类型存储空间语法表示
    String24 byteCHAR, VARCHAR

    日期与时间类型

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

    数据类型存储空间精度最小值最大值语法表示
    Date4 byteday1000-01-019999-12-31YYYY-MM-DD
    DateTime8 bytesecond0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999YYYY-MM-DD hh:mi:ssssss
    TIMESTAMP8 bytesecond1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999YYYYMMDD hh:mi:ss.ssssss

    Bool类型

    该类型表示布尔值类型

    数据类型存储空间
    True1 byte
    False1 byte

    精确数值类型Decimal

    数据类型存储空间精度语法表示
    Decimal648 byte19位Decimal(N,S), N范围(1,18), S范围(0,N)
    Decimal12816 byte38位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)
    数据库分布式
    MatrixOrigin
    4 声望2 粉丝

    新一代超融合异构数据库

    « 上一篇
    MatrixOne从入门到实战04——MatrixOne的连接和建表
    下一篇 »
    MatrixOne从入门到实战06——视图实践

    引用和评论

    推荐阅读
    头像
    万字长文 | MatrixOne 的 MySQL 协议实现

    MatrixOrigin阅读 213

    头像
    数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

    超神经HyperAI赞 4阅读 4.6k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.6k

    头像
    MongoDB 8.0已全面可用

    MongoDB数据平台阅读 15.2k

    头像
    简道云搭载阿里云MongoDB数据库，帮助数以万计企业重构业务系统

    MongoDB数据平台阅读 14.5k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 519

    头像
    活动报名｜走进蚂蚁上海S空间，探索大模型背景下的私域知识库构建

    TuGraph赞 1阅读 2.5k

    0 条评论
    得票最新