MySQL数据类型之数值型
整型
| 类型 | 占用字节数 | 最小值~最大值（有符号） | 最小值~最大值（无符号） |
|---|---|---|---|
| tinyint | 1 | -128~127 | 0~255 |
| smallint | 2 | -215~215-1 | 0~2^16-1 |
| mediumint | 3 | -223~223-1 | 0~2^24-1 |
| int | 4 | -231~231-1 | 0~2^32-1 |
| bigint | 8 | -263~263-1 | 0~2^64-1 |
无符号和有符号的区别
整数默认为有符号，无符号表示禁止负数，取值从0开始。无符号在进行运算时超出精确范围时会报错，如下：
mysql> create table test_unsigned(a int unsigned, b int unsigned);mysql> insert into test_unsigned values(1, 2);mysql> select b - a from test_unsigned;+-------+| b - a |+-------+| 1 |+-------+mysql> select a - b from test_unsigned; --运行出错ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test_unsigned`.`a` - `test`.`test_unsigned`.`b`)'
为了避免这个错误，需要对数据库参数sql_mode设置为NO_UNSIGNED_SUBTRACTION，允许相减的结果为signed，这样才能得到最终想要的结果。
mysql> set sql_mode='NO_UNSIGNED_SUBTRACTION';Query OK, 0 rows affected (0.00 sec)mysql> select a - b from test_unsigned;+-------+| a - b |+-------+| -1 |+-------+1 row in set (0.02 sec)
int(n)中的n是什么含义
先来看下面的演示：
mysql> use test;Database changedmysql> create table test_int_n(a int(4) zerofill, b int(4));Query OK, 0 rows affected (0.06 sec)mysql> insert into test_int_n values(1, 1);Query OK, 1 row affected (0.03 sec)mysql> insert into test_int_n values(123456, 123456);Query OK, 1 row affected (0.01 sec)mysql> select * from test_int_n;+--------+--------+| a | b |+--------+--------+| 0001 | 1 || 123456 | 123456 |+--------+--------+2 rows in set (0.02 sec)
说明：
- int(n)中的n是显示宽度，不表示存储的数字的长度的上限。
- zerofill表示当存储的数字长度<N时，用数字0填充左边，直至补满长度n，这一点用命令行才能看出，有些工具不会显示前面的0。
- 当存储数字的长度超过n时 ，按照实际存储的数字显示。
- 当没有zerofill时，n没有任何作用。
int类型id的自动增长
主键自动增长问题，下面的sql语法有错误吗？
mysql> create table test_auto_increment(a int auto_increment, b int); -- 错误mysql> create table test_auto_increment(a int auto_increment primary key, b int); --正确
只有列的类型为整型，并且这列有索引时才能自增。
以下结果是什么？
mysql> insert into test_auto_increment values(null, 1);mysql> insert into test_auto_increment values(0, 2);mysql> insert into test_auto_increment values(-1, 3);mysql> insert into test_auto_increment values(null, 4),(100, 5),(null, 6),(10, 7),(null, 8);mysql> select * from test_auto_increment;+-----+------+| a | b |+-----+------+| -1 | 3 || 1 | 1 || 2 | 2 || 3 | 4 || 10 | 7 || 100 | 5 || 101 | 6 || 102 | 8 |+-----+------+8 rows in set (0.00 sec)
结论:
- 当插入的值为null或0时，主键列会自增，自增后的值会是所有列中的最大的值+1。
- 当插入的值为负数时，如果这个值不存在，则直接插入。
- 当插入的值为正数时，如果这个值不存在，则直接插入。
- 当最大的值所在记录被删除后，后面插入的记录仍然会从最大值开始自增。
- 使用delete删除所有的数据后，主键依然会从曾经的最大值开始自增，而使用truncate则会从1开始重新开始自增。
id自增增大到最大后？
如果integer类型的id增大到最大值后继续自增会怎么样：
mysql> insert into test_auto_increment values(2147483647, 99);Query OK, 1 row affected (0.00 sec)mysql> insert into test_auto_increment values(null, 100);ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
id自增的回溯问题
mysql> select * from account;+-------+---------+| id | balance |+-------+---------+| 10000 | 100 || 10001 | 100 || 10002 | 100 |+-------+---------+3 rows in set (0.02 sec)mysql> delete from account where id = 10002;Query OK, 1 row affected (0.01 sec)mysql> show create table account;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| account | CREATE TABLE `account` (`id` int(11) NOT NULL AUTO_INCREMENT,`balance` int(11) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=latin1 |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.03 sec)
发现目前下一个id为10003，然后重启mysql：
mysql> show create table account;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| account | CREATE TABLE `account` (`id` int(11) NOT NULL AUTO_INCREMENT,`balance` int(11) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=latin1 |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.04 sec)
重启后发现目前下一个id为10002，即自增值发生回溯。
若要彻底解决这个问题，有以下方法：
- 升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；
- 若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键。
- 不做物理删除，使用逻辑删除。
其实，在海量互联网架构设计过程中，为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型。
浮点型
| 类型 | 占用字节数 | 取值范围 |
|---|---|---|
| float(M,N) | 4 | -3.402823466E+38 ~ -1.175494351E-38、0、1.175494351E-38 ~ 3.402823466E+38 |
| double(M,N) | 8 | -1.7976931348623157E+308 ~ - 2.2250738585072014E-308、0、2.2250738585072014E-308 ~ 1.7976931348623157E+308 |
| decimal(M,N) | 最大65 |
MySQL之前的版本中存在浮点类型float和double，但这些类型因为不是高精度，也不是SQL标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。
更重要的是，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。
decimal(M,N)：M表示整个数字的长度，D表示小数位的长度，如果数值为负数，负号不包含在M里面。M的最大值为65，D的最大值为30，M的缺省值为10，D的缺省值为0。
然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用decimal类型，而更推荐使用bigint类型。
最佳实践
资金字段设计
在用户余额、基金账户余额、数字钱包、零钱等的业务设计中，由于字段都是资金字段，通常习惯使用DECIMAL类型作为字段的选型，因为这样可以精确到分，如：DECIMAL(8,2)。
在海量互联网业务的设计标准中，并不推荐用DECIMAL类型，而是更推荐将DECIMAL转化为整型类型。也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。如1元在数据库中用整型类型100存储。
金额字段的取值范围如果用DECIMAL表示的，如何定义长度呢？因为类型DECIMAL是个变长字段，若要定义金额字段，则定义为DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为999999.99，百万级的资金存储。
用户的金额至少要存储百亿的字段，而统计局的GDP金额字段则可能达到数十万亿级别。用类型DECIMAL定义，不好统一。
另外重要的是，类型DECIMAL是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用BIGINT来存储金额相关的字段。
字段存储时采用分存储，即便这样BIGINT也能存储千兆级别的金额。这里，1兆=1万亿。
这样的好处是，所有金额相关字段都是定长字段，占用8个字节，存储高效。另一点，直接通过整型计算，效率更高。
注意，在数据库设计中，我们非常强调定长存储，因为定长存储的性能更好。
IP地址的设计
IP地址一般使用char或varchar进行存储，但是当进行查找和统计时，字符类型不是很高效。MySQL数据库内置了两个IP相关的函数INET_ATON()、INET_NTOA()，可以实现IP地址和整数类型的转换。转换后使用可以INT UNSIGNED 来存储IP，转换后的数字是连续的，提高了查询性能，占用空间更小。
mysql> select INET_ATON('192.168.0.1');+--------------------------+| INET_ATON('192.168.0.1') |+--------------------------+| 3232235521 |+--------------------------+1 row in set (0.02 sec)mysql> select INET_NTOA(3232235521);+-----------------------+| INET_NTOA(3232235521) |+-----------------------+| 192.168.0.1 |+-----------------------+1 row in set (0.02 sec)
布满荆棘的人生
左手的ㄟ右手
ゞ 浴缸里的玫瑰
怼烎@
还没有评论，来说两句吧...