关于MySQL中的字段长度以及占用空间总结.md
一、字符串类型
1、CHAR与VARCHAR
MySQL中常用的字符串类型有
CHAR
和VARCHAR
两种类型,建表时两者后面都必须跟上一个数字,否者会报错。例如:CHAR(20)、VARCHAR(20),那这个数字是用来做什么的呢?
CHAR(N)、VARCHAR(N),括号里的N表示的是该字段可容纳的最大字符数,简单来说就是能存入多少个字符。n的取值范围为:0-255。
注意:在MySQL早期的版本中n指的是字节数,但那是十多年前的版本,无需关心。
占用空间
我们都知道CHAR是定长字符串、而VARCHAR是可变长字符串,那二者之间具体有何区别呢?
我们可以借助MySQL内置函数
CHAR_LENGTH()
和LENGTH()
来查看,CHAR_LENGTH()
可以查看字符串的长度(字符个数),LENGTH()
可以查看字符串的字节数。
1、CHAR(定长字符串)
以CHAR(4)
的字段为例,表示该字段固定容纳4个字符。
-
如果存入字符不满4个时用自动用空格补齐4个字符,比如:我们存入
'aa'
(2字节),但是实际存入的是'aa '
(4字节),末尾会多出两个空格。值得注意的是,尽管数据库底层存入的是4个字节,但是对于空格补齐的部分,在我们执行DQL语句检索时会自动被去除。如果我们用函数在DQL
中使用函数LENGTH()
、CHAR_LENGTH()
来计算字段值长度时只会计算去除掉补齐空格后的字符串。 -
如果存入字符数等于4则原样存入,占用空间取决于实际存入的字符以及使用的字符集,比如存入
'abcd'
是4个字节,但是存入'张三李四'
就是8个字节。 -
如果存入超过4个字符会自动截断,比如存入
'abcdefg'
,会被MySQL截断为'abcd'
存入数据库。在高版本MySQL会直接报错(ERROR 1406 (22001) Data too long for column),这是由于新版本MySQL对字段严格检查。如果想使用自动截断的方式可找到MySQL配置文件
my.ini
,将sql-mode
那一行去掉STRICT_TRANS_TABLES
即可
2、VARCHAR(可变长字符串)
VARCHAR类型不会自动补齐空格,但是存入的不仅包含我们需要存入字符串数据,还包含描述我们所要存入数据的元数据
。所以VARCHAR类型字段值占用空间包含两部分:1、我们存入字符串的占用空间。2、元数据的占用空间。
如果存入超出范围的字符串处理方式同CHAR一样。
当然,这个占用空间还会受到其他字段值的影响,因为MySQL中一行记录的最大占用空间为65535个字节,这个空间是所有列共享的。
总结:字符类型的占用空间是由实际存入的字符内容以及字符集决定的,因为不同字符在字符集中占用的字节数是不一样的。
二、整数类型
整数类型的‘长度’是显示宽度。显示宽度不影响存储,只影响在检索输出时的格式,并且得满足一定条件这个显示宽度才能影响输出格式。
什么时候显示宽度有效?
只有在定义一个字段的时候指定:以零填充(zerofill
),存入的整数不满足这个显示宽度,在检索输出的时候会自动用零补齐。简单来说就是当显示宽度为n,存入了m位数(m<n),SELECT
出来这个字段值就是填充(n-m)个0。
假设有这样一个字段:
age int(6) unsigned zerofill);
# 注意:zerofill必须同时加上unsigned(无符号)。
当我们插入数据:
insert into user(id,age) values(2,33);
查询时输出结果:
mysql> select * from user;
+----+--------+
| id | age |
+----+--------+
| 1 | 000002 |
| 2 | 000033 |
+----+--------+
可以看见输出的33
前面用零补齐了,一共6位,在实际开发中这样的功能就显得十分鸡肋了。
占用空间
整数类型的占用空间是固定的,不会像字符串那样由实际存入内容决定。占用空间详情如下:
整数类型 | 占用空间 |
---|---|
tinyint | 1个字节 |
smallint | 2个字节 |
mediumint | 3个字节 |
int | 4个字节 |
bigint | 8个字节 |
取值范围
换算单位为:1个字节(Byte)等于8个比特位(Bit),也就是2的8次方。以tinyint为例,tinyint占用1个字节,就是2的8次方,共256种值。然后我们还要讨论有符号和无符号,如果是无符号(unsigned),取值范围就是0-255。如果是有符号,第一个比特位用来表示正负这两种符号,剩余7位表示数字,也就是2的7次方,为128。正数由于包含了0,所以要少一位为0-127。所以取值范围就是**-128-127**。
总结:无符号的取值范围:[ 0 , 28n-1 ],有符号整数类型取值范围:[ -28(n-1) , 28(n-1)-1 ]
注意:这里的n指的是占用的字节数。
补充:当存入的值超过了取值范围后会自动截断,存入的值是这个数据类型取值范围内最接近你所存入的值边界值。比如一个tinyint字段存入-3306,那么我们都知道tinyint的取值范围是[-128,127],这时MySQL会取接近-3306的边界值-128存入数据库。当然这时有符号的情况,无符号的取值范围是[0,255],那么你会惊讶地发现存入的-3306变成了0,不过理解了上述内容之后也就一点也不惊讶了。
注意:在高版本MySQL超取值范围会直接报错(ERROR 1406 (22001) Data too long for column),这是由于新版本MySQL对字段严格检查。如果想使用自动截断的方式可找到MySQL配置文件my.ini
,将sql-mode
那一行去掉STRICT_TRANS_TABLES
即可。
三、浮点类型与定点类型
1、浮点型(Float、Double)
浮点型的占用空间是固定的:Float-4个字节、Double-8个字节。取值范围同上。
2、定点型(Decimal)
Decimal的定义方式为:DECIMAL(m,n),其中m表示最大位数,n表示小数点右侧的位数。
注意:这里的位指的是十进制位,不是二进制位。
取值范围
例如:DECIMAL(6,2)表示最大存入6位数,小数点后能存2位,因此可以存入4(6-2)位,如果是有符号的话取值范围就是[ -9999.99 , 9999.99 ]。
存储方式以及占用空间
DECIMAL的存储方式和其他数据类型都不同,它是以字符串形式存储的。假设一个字段为DECIMAL(3,0),当我们存入100时,实际上存入的1
、0
、0
这三个字符拼接而成的字符串的二进制值,由于一个数字字符占用1个字节,所以共占用三个字节的空间。然后还要存入描述该数据的元数据
,元数据固定占用2个字节,所以共占用5个字节。
总结:DECIMAL(M,N)会占用(M+2)个字节。
四、日期类型
五、总结
六、参考文献
1、https://blog.csdn.net/weixin_31905417/article/details/113109793
七、交流
欢迎入群交流IT技术:951657281