摘要:与它们都是精确整型数据类型,但是占用字节数和表达的范围不同。为了避免数据库被过度设计,布尔枚举类型也采用。的显示宽度为,但并不影响更多位数的显示。声明语法是,占用字节。
最近在准备给开发做一个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)
插入几条数据看一下:
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)
上面的实验说明了几个问题:
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与decimalMySQL使用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使用FLOAT和DOUBLE来表示近似数值类型,这是因为十进制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 | +-----+----------+-----------+------------+------------+------------+-------------+--------------+
可以看到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
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做除法依旧会产生浮点型,所以特殊情况请考虑使用整型,货币单位使用 分 ,或者除法在最后进行。
参考MySQL各数据类型的区别
MySQL manual Out-of-Range and Overflow Handling
MySQL FLOAT vs DEC: working with fraction and decimal
Never use floats for money
本文链接地址:http://seanlook.com/2016/04/29/mysql-numeric-int-float/
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/46703.html
摘要:如果描述为字符串,类型的值应该使用连字号作为分隔符分开,而类型的值应该使用冒号作为分隔符分开。类型字段可以从集合中取得一个值或使用值,除此之外的输入将会使在这个字段中插入一个空字符串。 本篇博客稍微有点长,它实际上包括两个内容:一是mysql字段类型的介绍,二是在mysql建表过程中是如何正确选择这些字段类型; 字段类型 数值 MySQL 的数值数据类型可以大致划分为两个类别,一个是整...
摘要:数据类型整数数字类型整数和实数数字表示对应最大存储位数如表示不允许负数则范围为常规数据库中只是表示控制显示字符的个数是个和存储和计算是一样的即照样可以存储位数实数实数有分数部分和类型支持使用标准的浮点运算近似计算占用个字节占用个 数据类型 整数 数字类型:整数和实数 tinyint(8) smallint(16) mediuint(24) int(32) bigint(64) 数...
摘要:要确保没有低估需要存储的值的范围,更小是相对与数据类型的最大值范围来讲的。如果无法确定哪个数据类型是最好的,就选择你认为不会超过范围的最小类型。整数类型有两个类型的数字整数和实数。列默认为,这与其他的数据类型不一样。 简述 良好的逻辑设计和物理设计是高性能系统的基石,比如反范式设计可以加快某些类型的查询同时也会影响另外一些类型的查询效率,所以我们必须重视Mysql对于数据库的设计(本...
阅读 1754·2021-11-15 18:09
阅读 3555·2021-09-26 09:46
阅读 648·2021-09-06 15:13
阅读 2528·2021-08-23 09:43
阅读 1873·2019-08-30 15:54
阅读 2112·2019-08-30 13:56
阅读 2376·2019-08-26 11:31
阅读 2933·2019-08-26 10:56