mysql8学习笔记22--MySQL数据库设计--数据类型之整数类型、固定浮点类型、浮点类型、bit类型

数据类型之整数类型

• MySQL不仅支持标准SQL中的integer和smallint类型，还支持一些自己的扩展的整数类型

• 下表中指明了具体的类型，存储消耗的字节数，最小最大取值范围，unsigned代表不允许负数，则正整数的取值范围扩大一倍

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-2⁶³`	`0`	`2⁶³-1`	`2⁶⁴-1`

mysql> create table temp(sno tinyint);
Query OK, 0 rows affected (0.09 sec)

mysql> show create table temp;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| temp  | CREATE TABLE `temp` (
  `sno` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from temp;
Empty set (0.00 sec)

mysql> insert into temp(sno) values(-128);
Query OK, 1 row affected (0.02 sec)

mysql> insert into temp(sno) values(-129);
ERROR 1264 (22003): Out of range value for column 'sno' at row 1
mysql> insert into temp(sno) values(127);
Query OK, 1 row affected (0.09 sec)

mysql> insert into temp(sno) values(128);
ERROR 1264 (22003): Out of range value for column 'sno' at row 1
mysql> create table temp2(sno tinyint unsigned);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into temp2(sno) values(0);
Query OK, 1 row affected (0.06 sec)

mysql> insert into temp2(sno) values(-1);
ERROR 1264 (22003): Out of range value for column 'sno' at row 1
mysql> insert into temp2(sno) values(255);
Query OK, 1 row affected (0.04 sec)

mysql> insert into temp2(sno) values(256);
ERROR 1264 (22003): Out of range value for column 'sno' at row 1
mysql>

• MySQL可以为整数类型指定宽度，比如INT(11)，这个限制对大多数应用没有意义，因为这不是限制值的合法范围，对于存储和计算来说，INT(1)和INT(20)是相同的，只是对一些MySQL的交互工具规定了显示字符的个数，比如MySQL命令行客户端。

mysql> insert into temp3(sno) values(1);
Query OK, 1 row affected (0.07 sec)

mysql> insert into temp3(sno) values(100000);
Query OK, 1 row affected (0.03 sec)

mysql> select * from temp3;
+--------+
| sno    |
+--------+
|      1 |
| 100000 |
+--------+
2 rows in set (0.00 sec)

mysql>

mysql> alter table temp3 add id2 int(20) zerofill;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from temp3;
+--------+------+
| sno    | id2  |
+--------+------+
|      1 | NULL |
| 100000 | NULL |
+--------+------+
2 rows in set (0.00 sec)

mysql> insert into temp3(sno,id2) values(1,1000);
Query OK, 1 row affected (0.03 sec)

mysql> select * from temp3;
+--------+----------------------+
| sno    | id2                  |
+--------+----------------------+
|      1 |                 NULL |
| 100000 |                 NULL |
|      1 | 00000000000000001000 |
+--------+----------------------+
3 rows in set (0.00 sec)

数据类型之固定浮点类型

• Decimal和numeric数据类型用来存储高精度数据，一般只在对小数进行精确计算时才使用，比如涉及财务数据的时候

• DECIMAL[(M[,D])] [UNSIGNED]

• 在MySQL中，numeric和decimal的含义相同

mysql> create table temp_20210505(sno1 decimal(10,5),sno2 numeric(10,5));
Query OK, 0 rows affected (0.32 sec)

mysql> desc temp_20210505;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sno1  | decimal(10,5) | YES  |     | NULL    |       |
| sno2  | decimal(10,5) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

• Decimal的使用方法举例为decimal(5,2)

• 其中的5代表为精度，表示了可以使用多少位数字

• 其中的2代表小数点后面的小数位数

mysql> alter table temp_20210505 modify sno2 decimal(5,2);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc temp_20210505;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| sno1  | decimal(10,5) | YES  |     | NULL    |       |
| sno2  | decimal(5,2)  | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into temp_20210505(sno1,sno2) values(99999.99999,999.99);
Query OK, 1 row affected (0.03 sec)

mysql> insert into temp_20210505(sno1,sno2) values(99999.99999,999.999);
ERROR 1264 (22003): Out of range value for column 'sno2' at row 1
mysql> insert into temp_20210505(sno1,sno2) values(99999.99999,9999.99);
ERROR 1264 (22003): Out of range value for column 'sno2' at row 1
mysql>

• 此例子的取值范围为-999.99到999.99

• 当不需要指定小数时，可以使用decimal(M),decimal(M,0)表示

• 当直接使用decimal时，则默认的M为10

• M的最大取值为65，D的最大取值为30，当D为0时可以用来存储比BIGINT更大范围的整数值

mysql> alter table temp_20210505 modify sno2 decimal(65,30);
Query OK, 1 row affected (0.33 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table temp_20210505 modify sno2 decimal(65,31);
ERROR 1425 (42000): Too big scale 31 specified for column 'sno2'. Maximum is 30.
mysql> alter table temp_20210505 modify sno2 decimal(66,30);
ERROR 1426 (42000): Too-big precision 66 specified for 'sno2'. Maximum is 65.
mysql>

• 当指定unsigned，表示不允许负数

• MySQL对decimal字段采用每4个字节存储9个数字的方式，例如decimal(18,9)小数点两边各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

数据类型之浮点类型

• 浮点类型中包含float和double两种，与decimal相比是不精确类型

mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.21, 
    -> 9876543.12, 9876543.12);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from temp2;#float小数点部分有问题。
+------------+------------+------------+
| id         | id2        | id3        |
+------------+------------+------------+
| 1234567.25 | 1234567.21 | 1234567.21 |
| 9876543.00 | 9876543.12 | 9876543.12 |
+------------+------------+------------+
2 rows in set (0.00 sec)

mysql>

mysql> create table temp9(id double,id2 double);
Query OK, 0 rows affected (0.32 sec)

mysql> insert into temp9(id,id2) values(1.235,1.235);
Query OK, 1 row affected (0.05 sec)

mysql> select id - id2 from temp9;
+----------+
| id - id2 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> insert into temp9(id,id2) values(3.3,4.4);
Query OK, 1 row affected (0.10 sec)

mysql> select id - id2 from temp9;#double的小数点部分也有问题。
+---------------------+
| id - id2            |
+---------------------+
|                   0 |
| -1.1000000000000005 |
+---------------------+
2 rows in set (0.00 sec)

mysql>

• FLOAT[(M,D)] [UNSIGNED]中的M代表可以使用的数字位数，D则代表小数点后的小数位数

• Unsigned(无)代表不允许使用负数

• Float的取值范围为-3.402823466E+38 to -1.175494351E-38, 0, and

1.175494351E-38 to 3.402823466E+38

• DOUBLE[(M,D)] [UNSIGNED]中的M代表可以使用的数字位数，D则代表小数点后的小数位数

• Double的取值范围对比float要大，-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308

• 在存储同样范围的值时，通常比decimal使用更少的空间，float使用4个字节存储，double使用8个字节。

SQL语句中编写的浮点值可能与内部表示的值不同。尝试在比较中将浮点值视为精确值可能会导致问题。它们还受平台或实现依赖性的约束。该 FLOAT和 DOUBLE数据类型都受到这些问题。对于DECIMAL 列，MySQL执行的操作精度为65位十进制数字，这应该可以解决最常见的不准确性问题。

数据类型之bit类型

• Bit数据类型用来存储bit值

• BIT(M)代表可以存储M个bit，M的取值范围为1到64

• 如果手工指定bit值，则可以使用b’value’格式，比如b’111’和b‘10000000’分别代表7和128

• 除非特殊情况，否则尽量不要使用这个类型