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 | -263 |
0 |
263-1 |
264-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
• 除非特殊情况，否则尽量不要使用这个类型
