关于MySQL中的字段长度以及占用空间总结.md

一、字符串类型

1、CHAR与VARCHAR

MySQL中常用的字符串类型有CHARVARCHAR两种类型,建表时两者后面都必须跟上一个数字,否者会报错。例如: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位,在实际开发中这样的功能就显得十分鸡肋了。

占用空间

整数类型的占用空间是固定的,不会像字符串那样由实际存入内容决定。占用空间详情如下:

整数类型占用空间
tinyint1个字节
smallint2个字节
mediumint3个字节
int4个字节
bigint8个字节

取值范围

换算单位为: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时,实际上存入的100这三个字符拼接而成的字符串的二进制值,由于一个数字字符占用1个字节,所以共占用三个字节的空间。然后还要存入描述该数据的元数据,元数据固定占用2个字节,所以共占用5个字节。

总结:DECIMAL(M,N)会占用(M+2)个字节。

四、日期类型

五、总结

六、参考文献

1、https://blog.csdn.net/weixin_31905417/article/details/113109793

七、交流

欢迎入群交流IT技术:951657281

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐