乐趣区

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

jiezi

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

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

在 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)
退出移动版