MySql的列类型
Mysql共有三大类列类型,分别为:
数值类型
时间日期类型
字符类型
下图罗列了Mysql的所有数据类型:
下表罗列了每种数据结构所占用的存储空间,以及表示的数据范围
列类型 | 类型参数 | 存储需求(byte) | 最小表示(无符号) | 最大表示(无符号) | 最小表示(有符号) | 最大表示(有符号) |
---|---|---|---|---|---|---|
数值类型 | ||||||
BIT(M) | 1 <= M <= 64 | (M+7)/8, 1 ~ 8 | 1 bit | 64 bit | ||
TINYINT | 1 | 0 | 255 | -128 | 127 | |
SMALLINT | 2 | 0 | 65535 | -32768 | 32767 | |
MEDIUMINT | 3 | 0 | 16777215 | -8388608 | 8388607 | |
INT, INTEGER | 4 | 0 | 2^32-1 | -2^31 | 2^31-1 | |
BIGINT | 8 | 0 | 2^64-1 | -2^63 | 2^63-1 | |
FLOAT(P) | 单精度浮点数, 如果0 <= P <= 24为4个字节, 如果25 <= P <= 53为8个字节,如果不填P,为4个字节 | |||||
| 双精度浮点数, 8字节 | |||||
DECIMAL(M,D), DEC(M, D) NUMERIC(M,D) | 变长(0-4个字节),参见下面的讨论 |
日期时间类型:
列类型 | 存储需求 |
---|---|
DATE | 3个字节 |
DATETIME | 8个字节 |
TIMESTAMP | 4个字节 |
TIME | 3个字节 |
YEAR | 1个字节 |
字符串类型的存储需求:
列类型 | 存储需求 |
---|---|
CHAR(M) | M个字符,0 <= M <= 255, 如果是utf-8,当M=255时,将会占用 255*3个字节 |
VARCHAR(M) | L+1个字符,其中L <= M 且0 <= M <= 65535,最大存储空间不可以超过65535个字节,如果是utf-8,每个字符占用3个字节,则M的最大值为 65535/3个字节 |
BINARY(M) | M个字节,0 <= M <= 255 |
VARBINARY(M) | L+1个字节,其中L <= M 且0 <= M <= 255 |
TINYBLOB, TINYTEXT | L+1个字节,其中L < 28 |
BLOB, TEXT | L+2个字节,其中L < 216 |
MEDIUMBLOB, MEDIUMTEXT | L+3个字节,其中L < 224 |
LONGBLOB, LONGTEXT | L+4个字节,其中L < 232 |
ENUM(‘value1’,’value2’,…) | 1或2个字节,取决于枚举值的个数(最多65,535个值) |
SET(‘value1’,’value2’,…) | 1、2、3、4或者8个字节,取决于set成员的数目(最多64个成员) |
上表的M只是为了说明占用空间大小,在实际创建表中char(20)、varchar(20),20指的是字符而不是字节(4.0版本以上,以下指的是字节);那么字符和字节的转换要看字符集,utf-8下,1字符=3字节;gbk下,1字符=2字节。
数值类型
数值类型又分为整型和小数型。
整型
参数M: 参数M代表整型数字的显示宽度,不代表存储位数。
BIT(M)
BIT(M)代表位字段值,即用来存储二进制数,他的值在插入时必须时 b'value'
这种二进制形式,value代表数字的二进制表示。其中,参数M的大小为 0~64,表示位字段值的最大长度,最大64,所以最多占用8个字节。所以占用字节大概约 (M+7)/8
个字节。
证明:
插入:
查询:
TINYINT
TINYINT占用空间一个字节。无符号表示的范围为 0~255
,即 0~2^8-1
共有 2^8
种可能。有符号表示的范围为 -128~127
,即 -2^7~2^7-1
共有 2^8
种可能。
计算机中使用 0 和 1 表示一个数是正数还是负数,按照构思,应该如下表示:
0 0000000
->0 1111111
:0~127
1 0000000
->1 1111111
:-127~-0
如果像上面表示正负数,那么一个字节的位置本应该存储256种可能,现在因为有了 +0
和 -0
,只能存储255种可能,就导致了存储空间的浪费。因此,计算机中的负数的计算,并不是按照上面的方式,而是按照补码的方式(一种规则)计算的。
整数的计算是正确的
0 0000000
->0 1111111
补码规则: 负数 = 绝对值位-128
:
1111 1111
–> 绝对值 127 – 128 = -1
1000 0000
–> 绝对值 0 – 128 = -127
这样就节省了存储空间,范围为 -128~127
之间
补码:计算机中二进制形式的补数。
补数:初中时我们学过互补角,两个角加起来等于180度,则称两个角互补。
补码 可以把减法运算变成加法运算,因为在计算机中只有加法器。
模 定义:一个负整数与其补码的和,比如上面的180度就是模。
补码计算定义:非负数的补码是其原码本身;负数的补码是其绝对值的原码最高位符号位不变,其它位取反,再加1。 (取反+1 即 减1, 因为浪费了一个位置 +0和-,所以对负数区间减一 )
比如:
假设用1个字节存储数字3和-3:
数字3 的二进制原码为 0000 0011
,补码为 0000 0011
数字-3 的二进制原码为 1000 0011
,补码为 1111 1110
计算机在进行 1-3
运算时,首先对 -3
进行补码运算,得到 1111 1110
,再加1,得到 1111 1111
,然后再将补码转换为原码, -1 按位取反得到: 1000 0011
即-3
这样就解决了负数的二进制计算问题。
整型的可选参数
unsinged:无符号 建表时,默认时有符号的singed
M:显示宽度,即补零宽度,需要配合 zerofill 使用,如果宽度不足,前面会补0, 不代表存储大小
如果设置了zerofill,默认为unsinged,不可改为有符号整型。
小数型 (浮点数/定点型)
浮点数在计算机中的表示是很复杂的,IEEE浮点存储规范。
FLOAT(M,D)
M代表精度,D代表标度,比如 FLOAT(6, 2)
,可以表示 -9999.99
到 9999.99
的数,如果加上unsigned,则表示 0
到 9999.99
。
DOUBLE和DOUBLE PRECISION以及REAL
在mysql中,DOUBLE和DOUBLE PRECISION,以及 REAL 的作用相同,都是表示双精度浮点数,占用八个字节。
DECIMAL(M,D) 和 DEC(M,D)和 NUMERIC(M,D)
三者相同,都代表定点数,使用字符串的方式存储数值。
使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节,并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求:
剩余的位数 | 字节数目 |
---|---|
0 | 0 |
1 | 1 |
2 | 1 |
3 | 2 |
4 | 2 |
5 | 3 |
6 | 3 |
7 | 4 |
8 | 4 |
9 | 4 |
字符类型
binary 与 varbinary
参考: https://my.oschina.net/jsan/blog/336898
备注: 使用MySQL内置的 UUID() 创建一个函数返回 BINARY(16)类型的UUID值:
BLOB 和 TEXT
BLOB 和 TEXT的异同: https://blog.csdn.net/zuiaituantuan/article/details/6115938
ENUM 和 SET
https://www.cnblogs.com/benbenzhu/p/5604598.html
null
null值表示一个 a missing unknown value
,不确定的值,所以不可以使用 =
、 !=
、 <>
等判断。
建表时,通常加入 not null default 0
,这是因为:
null处理较为复杂。
列不使用null的原因
null的比较需要使用
is null
和is not null
,比较不方便效率低,影响索引效果(null内部是其他值存储的,同样占用存储空间)
最后更新于