MySQL中长度的含义
前言
在MySQL数据库表的设计过程中,有些数据类型后面必须要指定长度,例如
varchar(255)、int(10)
,那么问题来了,这里的长度具体指什么呢?我们应该如何更合理的去设计字段的长度。在设计之前,我们首先要明白他们的含义是什么
一、字符和字节的区别
字节(Byte) 是计量单位,表示数据量多少,通常情况下一字节等于八位。
字符(Character) 计算机中使用的字母、数字、字和符号,比如’A’、‘B’、‘$’、'&'
等。
一byte占8个bit位:
一般在英文状态下一个字母或字符占用一个字节,一个汉字用两个字节表示。
一下罗列出了常见的编码方式中字符所占的字节数量:
- ASCII 码中,一个英文字母(不分大小写)为一个字节,一个中文汉字为两个字节。
- UTF-8 编码中,一个英文字为一个字节,一个中文为三个字节。
- Unicode 编码中,一个英文为一个字节,一个中文为两个字节。
- 符号:英文标点为一个字节,中文标点为两个字节。例如:英文句号 . 占1个字节的大小,中文句号 。占2个字节的大小。
- UTF-16 编码中,一个英文字母字符或一个汉字字符存储都需要 2 个字节(Unicode 扩展区的一些汉- 字存储需要 4 个字节)。
- UTF-32 编码中,世界上任何字符的存储都需要 4 个字节。
二、字符串类型
在日常生活场景中,CHAR和VARCHAR是最常见的字符串类型,两者的后面都需要跟上一个数字表示长度,例如:
1| CHAR(10)
2| VARCHAR(10)
char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
MySQL版本 < 4.1: VARCHAR以字节为单位存储。假设全部为常用汉字(UTF-8 3字节编码长度),则VARCHAR(255)共可存放约85个汉字。
MySQL版本 >= 4.1: VARCHAR以字符为单位存储。假设输入仍然为常用汉字,则VARCHAR(255)可以存放255个汉字。
占用空间
CHAR(n)和VARCHAR(n)
前者为定长字符串,n的值为多少,就会占用多少的空间;
后者为可变字符串,是根据你实际放进去的字符数量来确定占用多少空间的,n为它的最大值;
为了更好的理解这个内容。我们把n当作8为例:
对于 CHAR(8) 表示固定容纳8个字符,当少于8个字符时,会使用空格填充空缺的部分,使其达到8个字符。如果超过8个字符,会自动截断超出部分。例如你存入数据为 ‘,~vSA’ ,实际会存入 ‘,~vSA’ (,~vSA后有3个空格),因此占用8个字节。以下以几个案例作为演示:
(1) ‘1a阿Be’ —— 字符数为5,少3个用空格补齐,因此实际存入 '1a阿Be ’ ,字符数:8,字节数:1+1+3+1+1+1+1+1=10
(2)‘a啊b哈ccccccccc’ —— 字符数超出8,仅保留前8个字符,因此实际存入 ‘a啊b哈cccc’ ,字符数:8,字节数:1+3+1+3+1+1+1+1=12
(3)‘a啊和哈cccc’ —— 字符数刚好为8,不需要截断和补齐,因此实际存入 ‘a啊和哈cccc’ ,字符数:8,字节数:1+3+3+3+1+1+1+1=14
通过这一个特性,就可以知道在日常设计数据库表的时候,如果某些字段是定长的,可以使用CHAE类型的字符串,这样可以提高存储和查询效率
三、整数类型
常用的整数数据类型有 tinyint ,smallint ,mediumint , int ,bigint 共计5,分别对应1、2、3、4、8字节。
此时设置的长度是指显示位宽。占用空间是固定的,均与其后设置的 n 无关。
例如 int(n),设置字段类型为 int ,则无论 n 设置什么,它占用的空间就是4个字节,32位取值个数就是2^32。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) 2^8 | 极小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535)2^16 | 小整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215)2^24 | 中整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295)2^32 | 整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
在设计数据库之前,先对自己设计的数据有一定的了解,知道它的范围区间,在参考以上所述,合理使用对应的数据类型,可以极大的提高了存储空间的使用率,在一定程度上也可以使SQL性能有所增加。
四、浮点数
对于小数的表示,MySQL分为两种方式:浮点数和定点数。浮点数包括float(单精度),double(双精度),而定点数只有decimal一种表示。定点数在MySQL内部以字符串的形式存放,比浮点数更精确,所以适合用来表示货币等精度高的数据。
区别:
- 浮点数和定点数都可以用类型名称后加“(M,D)”的方式来表示,“(M,D)”表示该值一共显示M为数字(整数+小数位),其中D位位于小数点后面,M和D又称为精度和标度,例如,定义为float(7,4)的一个列可以显示为-999.9999。
- MySQL保存值得时候进行四舍五入,如果在float(7,4)列内插入999.00009,近似结果是999.0001。值得注意的是,浮点数后面跟“(M,D)”的用法是非标准用法,如果要用于数据库的迁移,则最好不要这么使用。float个double在不指定精度时,默认会按照实际精度(由实际的硬件和操作系统决定)来显示,而decimal在不指定精度的时候,磨人的整数位是10,小数位是0。
- 浮点数如果不写精度和标度,则会按照实际精度值显示,如果有精度和标度,则会自动四舍五入插入,系统不会报错,;
而decimal(定位数)如果不写精度,则默认按照decimal(10,0)来进行操作,并且如果超出了精度和标度值,系统会报错。