关于数据库:MatrixOne从入门到实践05数据类型介绍

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

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

在MatrixOne建表时，给字段定义一个失当的数据类型是非常重要的。比方一下准则：

• 尽可能应用数字类型来代替字符串类型
• 尽可能应用更加精确的大小来形容字段类型
• 如果数据没有Null存在，那么能够在定义字段时应用Not Null

类型介绍

整数类型

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

浮点类型

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

字符串类型

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

日期与工夫类型

该类数据类型示意日期或者工夫类型

Bool类型

该类型示意布尔值类型

准确数值类型Decimal

示例

整数类型

• 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, 1mysql> insert into tinyint values(a);ERROR 20204 (HY000): truncated type TINYINT value a for column id, 1mysql> 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, 1mysql> 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, 1mysql> 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)。

浮点数类型

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

• 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.456mysql> select cast('123.456' as Decimal(5,3));ERROR 20203 (HY000): invalid argument DECIMAL64, bad value 123.456mysql> 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)