数据类型数值类型

举报
xcc-2022 发表于 2022/07/24 11:11:47 2022/07/24
【摘要】 9.1　数值类型MySQL中的数值类型包括整数类型、浮点数类型和定点数类型。本节就对MySQL中的数值类型进行简单的介绍。9.1.1　整数类型MySQL中的整数类型包括TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和BIGINT。不同的整数类型，其所需要的存储空间和数值范围不尽相同。MySQL中整数类型所需的存储空间如表9-1所示。表9-1　整数类型所需的存储...

9.1　数值类型

MySQL中的数值类型包括整数类型、浮点数类型和定点数类型。本节就对MySQL中的数值类型进行简单的介绍。


9.1.1　整数类型

MySQL中的整数类型包括TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和BIGINT。不同的整数类型，其所需要的存储空间和数值范围不尽相同。

MySQL中整数类型所需的存储空间如表9-1所示。

表9-1　整数类型所需的存储空间

由表9-1可以看出，在MySQL中，不同的整数类型所需要的存储空间的大小也是不同的。其中，TINYINT类型所占用的存储空间最小，为1个字节；BIGINT类型占用的存储空间最大，为8个字节。在实际工作中，读者需要根据实际情况选择合适的整数类型。

不同的整数类型除了所需的存储空间不同外，所表示的数值范围也是不同的。不同的整数类型所表示的数值范围如表9-2所示。

表9-2　整数类型表示的数值范围

由表9-2可以看出，不同的整数类型所表示的数值范围不同。同一种整数类型，有符号与无符号所表示的数值范围也不同。其中，有符号整数的最小值是一个负数，无符号整数的最小值是0。

如果使用的数据类型超出了整数类型的范围，则MySQL会抛出相应的错误。因此在实际使用的时候，应该首先确认好数据的取值范围，然后根据确认的结果选择合适的整数类型。

接下来，在MySQL中创建数据表t1，这个表中只有一个INT类型的字段id。

mysql> CREATE TABLE t1(id INT); 
Query OK, 0 rows affected (0.07 sec)

使用SHOW CREATE TABLE语句查看t1的创建信息。

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.09 sec)

实际上，MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

创建数据表t2，将INT类型的字段id的显示宽度设置为6。

mysql> CREATE TABLE t2(id INT(6));
Query OK, 0 rows affected, 1 warning (0.10 sec)

查看t2表在MySQL内部的创建信息。

mysql> SHOW CREATE TABLE t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

t2表中id字段的显示宽度为6。

注意：整数类型的显示宽度与数据类型的取值范围无关。显示宽度只是指定最大显示的数字个数，如果在数据表中插入了大于显示宽度，但是并没有超过整数类型的数值范围的数据，依然可以正确地插入数据，并且能够正确地显示。

接下来向数据表t2中插入两条数据，一条数据没有超出显示的宽度6，另一条数据超出了显示的宽度6。

mysql> INSERT INTO t2(id) VALUES (1), (1111111);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

分别向t2表中插入了两条数据，一条数据为1，另一条数据为1111111，数字1并没有超出id字段的显示宽度，数字1111111超出了id字段的显示宽度。

查询t2表中的数据。

mysql> SELECT * FROM t2;
+---------+
| id      |
+---------+
|       1 |
| 1111111 |
+---------+
2 rows in set (0.01 sec)

虽然在创建数据表的时候设置了id字段的显示宽度为6，但是数字1111111并没有超出INT类型表示的数值范围，因此依然能够正确地插入数据库中并显示。

整数类型的显示宽度能够配合ZEROFILL使用。ZEROFILL表示在数字的显示位数不够时，可以用字符0进行填充。

创建数据表t3，包含两个字段id1和id2，id1不指定显示宽度，id2指定显示宽度为6，并且两个字段指定当数字的显示位数不够时，使用字符0进行填充。

mysql> CREATE TABLE t3(id1 INT ZEROFILL, id2 INT(6) ZEROFILL);
Query OK, 0 rows affected, 3 warnings (0.02 sec)

向数据表t3中插入数据。

mysql> INSERT INTO t3(id1, id2) VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)

查询t3表中的数据。

mysql> SELECT * FROM t3;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000001 | 000001 |
+------------+--------+
1 row in set (0.00 sec)

查询字段id1和字段id2时，当插入的数字小于设置的显示宽度时，使用字符0进行了填充。

当整数类型设置了显示宽度并且使用字符0填充时，如果向数据表中插入时超出了显示宽度，但是并没有超出整数类型范围的数字时，MySQL也不会报错，原因是插入的数字超出了显示宽度，无须再用字符0进行填充。

再次向数据表t3中插入数据。

mysql> INSERT INTO t3(id1, id2) VALUES(1, 1111111);
Query OK, 1 row affected (0.00 sec)

为id2字段插入的数字1111111已经超出了字段的显示宽度，但是并没有超出INT类型的数值范围。此时，再次查询数据表t3中的数据。

mysql> SELECT * FROM t3;
+------------+---------+
| id1        | id2     |
+------------+---------+
| 0000000001 |  000001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)

当插入的数据超出了字段的显示宽度，但是并没有超出INT类型的数值范围时，能够正确插入与显示数据，并且不再使用字符0进行填充。也就是说，当插入的数据超出了字段的显示宽度，但是并没有超出INT类型的数值范围时，插入的数据并不会受显示宽度的限制。

所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。特别地，如果在MySQL中创建数据表时，指定数据字段为ZEROFILL，则MySQL会自动为当前列添加UNSIGNED属性。

查看t3表在MySQL中的建表信息。

mysql> SHOW CREATE TABLE t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id1` int(10) unsigned zerofill DEFAULT NULL,
  `id2` int(6) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在创建数据表t3时并没有指定id1字段和id2字段的属性UNSIGNED，而只是为id1字段和id2字段指定了ZEROFILL，此时，MySQL自动为id1和id2添加了UNSIGNED属性。

在MySQL中，整数类型还有一个属性是AUTO_INCREMENT。AUTO_INCREMENT的值一般从1开始，每行自动加1。如果在标识为AUTO_INCREMENT的整数列中插入NULL，则MySQL会在此列中插入一个比该列当前最大值大1的数值。

一个表中最多只能有一个列被设置为AUTO_INCREMENT。设置为AUTO_INCREMENT的列需要定义为NOT NULL，并且定义为PRIMARY KEY，或者定义为NOT NULL并且定义为UNIQUE。

可以使用如下语句创建数据表t4。t4表中存在两个字段，即id字段和age字段，其中id字段是INT类型的主键，并且设置为自动递增类型，age字段是INT类型。

mysql> CREATE TABLE t4(
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> age int
    -> );
Query OK, 0 rows affected (0.02 sec)

也可以使用如下语句创建数据表：

mysql> CREATE TABLE t4(
    -> id INT NOT NULL AUTO_INCREMENT,
    -> age int,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

接下来向t4表中插入数据。

mysql> INSERT INTO t4 (age) values(18),(20),(16);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

向数据表t4中插入了3条数据，age字段的值分别为18、20和14，并没有向id字段插入数据。

接下来查询数据表t4的数据。

mysql> SELECT * from t4;
+----+------+
| id | age  |
+----+------+
|  1 |   18 |
|  2 |   20 |
|  3 |   16 |
+----+------+
3 rows in set (0.00 sec)

MySQL自动为AUTO_INCREMENT类型的整型列设置了自动递增的整数值。

也可以将设置为AUTO_INCREMENT的整型列定义为UNIQUE，建立数据表t5。

mysql> CREATE TABLE t5 (
    -> id INT NOT NULL AUTO_INCREMENT UNIQUE,
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)

在数据表t5中并没有将id字段设置为主键，而是将id字段设置为唯一索引，此时向数据表t5中插入数据。

mysql> INSERT INTO t5 (age) values (16),(19),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

上面的命令向数据表t5中插入了3行数据，为age字段分别赋值16、19和13，但是并没有为id赋值。

查看数据表t5中的数据。

mysql> SELECT * from t5;
+----+------+
| id | age  |
+----+------+
|  1 |   16 |
|  2 |   19 |
|  3 |   13 |
+----+------+
3 rows in set (0.00 sec)

在数据表t5中，MySQL同样为设置为AUTO_INCREMENT的整型列id设置了自动递增的整数值。

注意：其他整数类型的用法与INT类型相同，笔者以INT类型为例进行了介绍，读者可以用其他整数类型进行验证，这里不再赘述。


9.1.2　浮点数类型

浮点数类型主要有两种：单精度浮点数FLOAT和双精度浮点数DOUBLE。浮点数类型所需的存储空间如表9-3所示。

表9-3　浮点数所需的存储空间

由表9-3可以看出，单精度浮点数FLOAT类型占用4个字节的存储空间，双精度浮点数DOUBLE类型占用8个字节的存储空间。

对于浮点数来说，有符号与无符号所表示的数值范围也是不同的，浮点数表示的数值范围如表9-4所示。

表9-4　浮点数表示的数值范围

由表9-4可以看出，不同类型的浮点数的取值范围不同，相同类型的浮点数，有符号与无符号时，取值范围也是不同的。

浮点数类型中的FLOAT和DOUBLE类型在不指定数据精度时，默认会按照实际的计算机硬件和操作系统决定的数据精度进行显示。如果用户指定的精度超出了浮点数类型的数据精度，则MySQL会自动进行四舍五入操作。

创建数据表t6，t6表中包含两个字段f和d，f字段是FLOAT类型，d字段是DOUBLE类型。

mysql> CREATE TABLE t6 (f FLOAT, d DOUBLE);
Query OK, 0 rows affected (0.01 sec)

接下来向数据表t6中插入数据。

mysql> INSERT INTO t6 (f, d) VALUES (3.14, 5.98);
Query OK, 1 row affected (0.00 sec)

查看t6表中的数据。

mysql> SELECT * FROM t6;
+------+------+
| f    | d    |
+------+------+
| 3.14 | 5.98 |
+------+------+
1 row in set (0.00 sec)

可以看出，能够正确地插入并显示数据。接下来再次向t6表中插入数据。

mysql> INSERT INTO t6 (f, d) VALUES (3.144444444444444, 5.98999999999999999999999);
Query OK, 1 row affected (0.00 sec)

此时，再次查看t6表中的数据。

mysql> SELECT * FROM t6;
+---------+------+
| f       | d    |
+---------+------+
|    3.14 | 5.98 |
| 3.14444 | 5.99 |
+---------+------+
2 rows in set (0.00 sec)

可以看到，为FLOAT类型和DOUBLE类型插入超出数据类型精度的数据时，MySQL对插入的数据进行了四舍五入处理。

对于浮点数来说，可以使用(M,D)的方式进行表示，(M,D)表示当前数值包含整数位和小数位一共会显示M位数字，其中，小数点后会显示D位数字，M又被称为精度，D又被称为标度。

创建数据表t7，t7表中包含FLOAT类型的字段f和DOUBLE类型的字段d，并为两个字段设置精度和标度。

mysql> CREATE TABLE t7 (
    -> f FLOAT(5,2),
    -> d DOUBLE(5,2)
    -> );
Query OK, 0 rows affected, 2 warnings (0.11 sec)

接下来分别向f字段和d字段插入数值3.14。

mysql> INSERT INTO t7 (f, d) VALUES (3.14, 3.14);      
Query OK, 1 row affected (0.01 sec)

查看t7表中的数据。

mysql> SELECT * FROM t7;
+------+------+
| f    | d    |
+------+------+
| 3.14 | 3.14 |
+------+------+
1 row in set (0.00 sec)

可以发现，能够正确地插入并显示数据。

再次向t7表中插入数据，此时在f字段中插入数值3.141，在d字段中插入数值3.14。执行命令如下：

mysql> INSERT INTO t7 (f, d) VALUES (3.141, 3.14);
Query OK, 1 row affected (0.00 sec)

查看t7表中的数据。

mysql> SELECT * FROM t7; 
+------+------+
| f    | d    |
+------+------+
| 3.14 | 3.14 |
| 3.14 | 3.14 |
+------+------+
2 rows in set (0.00 sec)

FLOAT类型的字段f，由于标度的长度限制，最后一位数字被舍弃了，最终插入数据库中的数值为3.14。

接下来再次向t7表中插入数据，此时，在f字段中插入数值3.14，d字段中插入数值3.141。

mysql> INSERT INTO t7 (f, d) VALUES (3.14, 3.141);      
Query OK, 1 row affected (0.16 sec)

再次查看t7表中的数据。

mysql> SELECT * FROM t7;
+------+------+
| f    | d    |
+------+------+
| 3.14 | 3.14 |
| 3.14 | 3.14 |
| 3.14 | 3.14 |
+------+------+
3 rows in set (0.00 sec)

当双精度DOUBLE类型的数据设置了精度和标度时，由于标度的限制，同样会舍弃超出标度限制的数字。

综上所述，浮点数不写精度和标度时，会按照计算机硬件和操作系统决定的数据精度进行显示。如果用户指定的精度超出了浮点数类型的数据精度，则MySQL会自动进行四舍五入操作，数据能够插入MySQL中，并能够正常显示。


9.1.3　定点数类型

MySQL中的定点数类型只有DECIMAL一种类型。DECIMAL类型也可以使用(M,D)进行表示，其中，M被称为精度，是数据的总位数；D被称为标度，表示数据的小数部分所占的位数。定点数在MySQL内部是以字符串的形式进行存储的，它的精度比浮点数更加精确，适合存储表示金额等需要高精度的数据。

DECIMAL(M,D)类型的数据的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。而DECIMAL的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。

使用定点数类型表示数据时，当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。

创建数据表t8，在t8数据表中包含两个字段，即d1和d2，两个字段的类型分别为DECIMAL和DECIMAL(5,2)。也就是说，d1字段使用默认的精度和标度，d2字段的精度为5，标度为2。

mysql> CREATE TABLE t8 (
    -> d1 DECIMAL,
    -> d2 DECIMAL(5, 2)
    -> );
Query OK, 0 rows affected (0.56 sec)

接下来，查看t8数据表的建表信息。

mysql> SHOW CREATE TABLE t8 \G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `d1` decimal(10,0) DEFAULT NULL,
  `d2` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在创建数据表t8时并没有为d1字段设置精度和标度，此时，MySQL会自动为d1字段设置精度为10，标度为0。

向t8表中插入数据。

mysql> INSERT INTO t8 (d1, d2) VALUES (3.14, 3.14); 
Query OK, 1 row affected, 1 warning (0.00 sec)

此时，MySQL并没有报错，只是会显示一个警告，接下来查看警告信息。

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'd1' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.01 sec)

MySQL的警告信息中显示d1字段的值被截断了。接下来查看t8表中的数据。

mysql> SELECT * FROM t8;
+------+------+
| d1   | d2   |
+------+------+
|    3 | 3.14 |
+------+------+
1 row in set (0.00 sec)

在插入数据时，d1字段的值为3，d2字段的值为3.14，再次证明了当创建数据表时，如果不给DECIMAL类型的字段设置精度和标度，则DECIMAL默认的精度为10，标度为0。此时，向数据表中插入数据会舍弃所有小数部分。

再次向t8表中插入数据。

mysql> INSERT INTO t8 (d1, d2) VALUES (1, 3.141);
Query OK, 1 row affected, 1 warning (0.00 sec)

此时，向d1字段插入数值1，t2字段插入数据3.141。查看t8表中的数据。

mysql> SELECT * FROM t8;
+------+------+
| d1   | d2   |
+------+------+
|    3 | 3.14 |
|    1 | 3.14 |
+------+------+
2 rows in set (0.00 sec)

d2字段中的值，由于精度和标度的限制也被截断了。也就是说，在定点数类型中，如果小数位数超出了标度的限制，则会被截断处理。

接下来再次向t8表中插入数据。

mysql> INSERT INTO t8 (d1, d2) VALUES (11111111111, 3.14);
ERROR 1264 (22003): Out of range value for column 'd1' at row 1

此时，向t8表的d1字段中插入一个超出了精度和标度的数值，MySQL报错了。

对比浮点数类型和定点数类型，可以总结出如下不同之处：

·浮点数类型中的FLOAT类型和DOUBLE类型在不指定精度时，默认会按照计算机硬件和操作系统决定的精度进行表示；而定点数类型中的DECIMAL类型不指定精度时，默认为DECIMAL(10,0)。

·当数据类型的长度一定时，浮点数能够表示的数据范围更大，但是浮点数会引起精度问题，不适合存储高精度类型的数据。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据结构
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入