MySQL数字类型int与tinyint、float与decimal如何选择

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

最近在准备给开发做一个mysql数据库开发规范方面培训,一步一步来,结合在生产环境发现的数据库方面的问题,从几个常用的数据类型说起。

int、tinyint与bigint

它们都是(精确)整型数据类型,但是占用字节数和表达的范围不同。首先没有这个表就说不过去了:

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

只需要知道对应类型占多少字节就能推算出范围了,比如int占 4 bytes,即4*8=32bits,大约10位数字,也能理解为什么int默认显示位数是11。

遇到比较多的是tinyint和bigint,tinyint一般用于存放status,type这种数值小的数据,不够用时可能会用smallint。bigint一般用于自增主键。

为了避免数据库被过度设计,布尔、枚举类型也采用tinyint。

还有一点也是经常被提到的关于 int(M) 中M的理解,int型数据无论是int(4)还是int(11),都已经占用了 4 bytes 存储空间,M表示的只是显示宽度(display width, max value 255),并不是定义int的长度。

例如:

mysql> CREATE TABLE `tc_integer` (
  `f_id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
  `f_type` tinyint,
  `f_flag` tinyint(1),
  `f_num` smallint(5) unsigned ZEROFILL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> desc tc_integer;
+----------------+-------------------------------+------+-----+---------+----------------+
| Field          | Type                          | Null | Key | Default | Extra          |
+----------------+-------------------------------+------+-----+---------+----------------+
| f_id           | bigint(20)                    | NO   | PRI | NULL    | auto_increment |
| f_type         | tinyint(4)                    | YES  |     | NULL    |                |
| f_flag         | tinyint(1)                    | YES  |     | NULL    |                |
| f_num          | smallint(5) unsigned zerofill | YES  |     | NULL    |                |
+----------------+-------------------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
AI 代码解读

插入几条数据看一下:
<!-- more -->

mysql> insert into tc_integer values(1, 1, 1, 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into tc_integer values(9223372036854775808, 127, 127, 65535);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'f_id' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select i.*, length(i.f_flag) as len_flag from tc_integer i;
+---------------------+--------------+---------------+----------------+----------+
| f_id                | f_type       | f_flag        | f_num          | len_flag |
+---------------------+--------------+---------------+----------------+----------+
|                   1 |            1 |             1 |          00001 |        1 |
| 9223372036854775807 |          127 |           127 |          65535 |        3 |
+---------------------+--------------+---------------+----------------+----------+
2 rows in set (0.00 sec)

mysql> select * from tc_integer where f_num=' 01' and f_num=1 and f_num=f_flag;
+------+--------------+---------------+----------------+
| f_id | f_type       | f_flag        | f_num          |
+------+--------------+---------------+----------------+
|    1 |            1 |             1 |          00001 |
+------+--------------+---------------+----------------+
1 row in set (0.00 sec)
AI 代码解读

上面的实验说明了几个问题:

  • f_id列插入比最大值还大的数,出现warnings,并且最终的值自动变成 9223372036854775807 。这个坑曾经在迁移到阿里RDS时遇到过,他们的迁移工具是java写的,结果我们的主键值大于java INTEGER里面的最大限制,导致 duplicate key问题。

  • f_flag的显示宽度为1,但并不影响更多位数的显示。也证实了tinyint(1)并不像char(1)那样限制存储长度

  • f_num定义成无符号的zerofill类型,能存储的最大数值是65535,而signed才是32767。(当列上使用zerofill时,unsigned会自动加上)

  • zerofill的作用是在显示检索结果的时候,左边用0补齐到display width,实际存储时不补0的,仅作为返回结果meta data的一部分。查询的条件值忽略0和空格

  • length()在numeric类型中作用于char_length()一样,因为字节数已经固定了。

zerofill的使用可能会在复杂join时因为了解不够深入而带来问题,所以最终的结论也很简单:除非极端的特殊需要,尽量不用zerofill,建表时这类int无需指定 (11) 这样的显示宽度。

float与decimal

MySQL使用DECIMAL类型去存储对精度要求比较高的数值,比如金额,也叫定点数,decimal在mysql内存是以字符串存储的。声明语法是DECIMAL(M,D),占用字节 M+2 bytes。M是数字最大位数(精度precision),范围1-65;D是小数点右侧数字个数(标度scale),范围0-30,但不得超过M。

比如定义DECIMAL(7,3)

  • 能存的数值范围是 -9999.999 ~ 9999.999,占用9个字节

  • 123.12 -> 123.120,因为小数点后未满3位,补0

  • 123.1245 -> 123.125,小数点只留3位,多余的自动四舍五入截断

  • 12345.12 -> 保存失败,因为小数点未满3位,补0变成12345.120,超过了7位。严格模式下报错,非严格模式存成9999.999

MySQL使用FLOATDOUBLE来表示近似数值类型,这是因为十进制0.1在电脑里用二进制是无法精确表示的,只能尽可能的接近

单精度浮点数float占4字节,float标准语法允许通过FLOAT(M)的形式指定精度,但是这个精度值M只是决定存储大小: 0-23与默认不指定效果相同,24-53就变成双精度的DOUBLE了。

float还有非MySQL自己实现的非标准语法FLOAT(M,D),代表最多存储M个数字长度,其中小数点后数字个数为D。效果与 DECIMAL(M,D)很相似。

double 和 float 的区别是double精度高,有效数字16位(float精度7位)。但double消耗内存是float的两倍,占8字节,double的运算速度比float慢得多。

msyql> create table tc_float(fid int primary key auto_increment,f_float float, f_float10 float(10), f_float25 float(25), f_float7_3 float(7,3), f_float9_2 float(9,2), f_float30_3 float(30,3), f_decimal9_2 decimal(9,2));

mysql> insert into tc_float(f_float,f_float10,f_float25) values(123456,123456,123456);
mysql> insert into tc_float(f_float,f_float10,f_float25) values(1234567.89,12345.67,1234567.89);
mysql> select * from tc_float;
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
| fid | f_float  | f_float10 | f_float25  | f_float7_3 | f_float9_2 | f_float30_3 | f_decimal9_2 |
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
|   1 |   123456 |    123456 |     123456 | NULL       | NULL       | NULL        | NULL         |
|   2 |  1234570 |   12345.7 | 1234567.89 | NULL       | NULL       | NULL        | NULL         |
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
AI 代码解读
  • 可以看到float与float(10)是没区别的,float默认能精确到6位有效数字

mysql> insert into tc_float(f_float9_2,f_decimal9_2) values(123456.78,123456.78);
mysql> insert into tc_float(f_float9_2,f_decimal9_2) values(1234567.1,1234567.125);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1265 | Data truncated for column 'f_decimal9_2' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from tc_float;
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
| fid | f_float  | f_float10 | f_float25  | f_float7_3 | f_float9_2 | f_float30_3 | f_decimal9_2 |
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
|   6 | NULL     | NULL      | NULL       | NULL       |  123456.78 | NULL        |    123456.78 |
|   9 | NULL     | NULL      | NULL       | NULL       | 1234567.12 | NULL        |   1234567.13 |
+-----+----------+-----------+------------+------------+------------+-------------+--------------+

mysql> insert into tc_float(f_float7_3) values(12345.1);
ERROR 1264 (22003): Out of range value for column 'f_float7_3' at row 1
AI 代码解读
  • float(9,2)与decimal(9,2)是很像的,并没有前面提到24位一下6位有效数字的限制

  • 他们俩之间的差别就在精度上,f_float9_2本应该是 1234567.10,结果小数点变成 .12 。f_decimal9_2因为标度为2,所以 .125 四舍五入成 .13

  • 将 12345.1 插入f_float7_3列,因为转成标度3时 12345.100,整个位数大于7,所以 out of range 了

另外在编程中应尽量避免做浮点数的比较,否则可能会导致一些潜在的问题。

坚决不允许使用float去存money,使用decimal更加稳妥,但使用decimal做除法依旧会产生浮点型,所以特殊情况请考虑使用整型,货币单位使用 分 ,或者除法在最后进行。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
打赏
0
0
0
0
35
分享
相关文章
【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
**简介**:在MySQL迁移到崖山环境时,若字段类型为char(2),而应用存储的数据仅为&#39;0&#39;或&#39;1&#39;,查询时崖山会自动补空格。原因是mysql的sql_mode可能启用了PAD_CHAR_TO_FULL_LENGTH模式,导致保留CHAR类型尾随空格。解决方法是与应用确认数据需求,可将崖山环境中的char类型改为varchar类型以规避补空格问题,适用于所有版本。
[oeasy]python076_int这个词怎么来的_[词根溯源]整数类型_int_integer_touch
本文探讨了“int”一词的起源及其与整数类型的关联。通过词根溯源,揭示“int”来源于“integer”,意为“完整的数”,与零碎的分数相对。同时分析了相关词汇如“tact”(接触)、“touch”(触摸)及衍生词,如“tangential”(切线的)、“intagible”(无形的)和“integral”(完整的、不可或缺的)。文章还结合编程语言特性,解释了Python作为动态类型、强类型语言的特点,并总结了整型变量的概念与意义。最后预告了后续内容,提供了学习资源链接。
146 11
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
[oeasy]python077_int类型怎么用_整数运算_integer_进制转化_int类
本文主要讲解了Python中`int`类型的应用与特性。首先回顾了`int`词根的溯源,探讨了整型变量的概念及命名规则(如匈牙利命名法)。接着分析了整型变量在内存中的存储位置和地址,并通过`type()`和`id()`函数验证其类型和地址。还介绍了整型变量的运算功能,以及如何通过`int()`函数将字符串转化为整数,支持不同进制间的转换(如二进制转十进制)。此外,文章提及了关键字`del`的使用场景,对比了Python与C语言中`int`的区别,并总结了整型与字符串类型的差异,为后续深入学习奠定基础。
90 1
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
473 1
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
173 62
MySQL功能模块探秘:数据库世界的奇妙之旅
]带你轻松愉快地探索MySQL 8.4.5的核心功能模块,从SQL引擎到存储引擎,从复制机制到插件系统,让你在欢声笑语中掌握数据库的精髓!

热门文章

最新文章

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等