MySql的列类型

Mysql共有三大类列类型，分别为：

1. 数值类型

2. 时间日期类型

3. 字符类型

下图罗列了Mysql的所有数据类型：

file

下表罗列了每种数据结构所占用的存储空间，以及表示的数据范围

列类型	类型参数	存储需求（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个字节
DOUBLE [PRECISION],REAL		双精度浮点数， 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 个字节。

证明：

create table t_type_bit (
  id int(20) primary key,
  b1 bit, # 代表bit(1) 存储长度为1位，占用 (1+7)/8 1个字节
  -- b2 bit(0), 不可以创建长度为0的bit类型
  b3 bit(64), # 最大长度64，占用 (64+7)/8  8个字节
  b4 bit(4) # 最大长度4
)

插入：

-- bit类型插入时，只可以插入二进制值
insert into t_type_bit (id, b1, b3, b4) values (1, b'0', b'1000000', b'11');

查询：

-- 以十进制显示
select b1+0 from t_type_bit; #0
select b3+0 from t_type_bit; #64

--以二进制形式显示
select bin(b1+0) from t_type_bit; # 0
select bin(b3+0) from t_type_bit; # 1000000

-- 以八进制形式显示
select oct(b1+0) from t_type_bit; # 0
select oct(b3+0) from t_type_bit; # 100

-- 以十六进制形式显示
select hex(b1+0) from t_type_bit; #0
select hex(b3+0) from t_type_bit; #40

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

CREATE TABLE test_bin (    bin_id BINARY(16) NOT NULL ) Engine=InnoDB;

INSERT INTO test_bin(bin_id) VALUES(UNHEX('FA34E10293CB42848573A4E39937F479'));INSERT INTO test_bin(bin_id) VALUES(UNHEX(?)); 或 INSERT INTO test_bin(bin_id) VALUES(x'FA34E10293CB42848573A4E39937F479');

SELECT HEX(bin_id) AS bin_id FROM test_bin; SELECT HEX(bin_id) AS bin_id FROM test_bin WHERE bin_id = UNHEX('FA34E10293CB42848573A4E39937F479'); SELECT HEX(bin_id) AS bin_id FROM test_bin WHERE bin_id = UNHEX(?); SELECT HEX(bin_id) AS bin_id FROM test_bin WHERE bin_id = x'FA34E10293CB42848573A4E39937F479'; 查询结果： bin_id -------------------------- FA34E10293CB42848573A4E39937F479

备注： 使用MySQL内置的 UUID() 创建一个函数返回 BINARY（16）类型的UUID值:

CREATE FUNCTION uu_id() RETURNS binary(16) RETURN UNHEX(REPLACE(UUID(),'-','')); 或 CREATE FUNCTION uu_id() RETURNS binary(16) RETURN UNHEX(REVERSE(REPLACE(UUID(),'-',''))); 使用： INSERT INTO test_bin(bin_id) VALUES(uu_id());

BLOB 和 TEXT

BLOB 和 TEXT的异同： https://blog.csdn.net/zuiaituantuan/article/details/6115938

ENUM 和 SET

https://www.cnblogs.com/benbenzhu/p/5604598.html

create table enum_set_table (
  id     int auto_increment primary key,
  gender enum ('M', 'F'),
  hobby  set ('music', 'movie', 'swimming', 'footbal')
);
-- enum和set都可以直接插入字符串
insert into enum_set_table(id,gender,hobby) values(null,'M','music');
-- 一个enum值，多个set值，二者均使用选项的字符串格式
insert into enum_set_table(id,gender,hobby) values(null,'F','music,movie,footbal');
-- 个enum值，一个set值，二者均使用选项的数字格式
insert into enum_set_table(id,gender,hobby) values(null,1,1);
-- 一个enum值，多个set值，二者均使用选项的数字格式，其中enum的值 2<=>'F',15=1+2+4+8 <=> 'music,movie,swimming,footbal'
insert into enum_set_table(id,gender,hobby) values(null,2,15);
-- 一个enum值，多个set值，enum值使用选项的字符串格式，set值使用选项的数字格式，7=1+2+4 <=> 'music,movie,swimming'
insert into enum_set_table(id,gender,hobby) values(null,'F',7);

null

null值表示一个 a missing unknown value，不确定的值，所以不可以使用 =、 !=、 <> 等判断。

建表时，通常加入 not null default 0,这是因为：

select 2 > 1; -- 真为1
select 1 > 2; -- 假为0
select '李四' != null; -- 返回null，即假
select null = null; -- 返回null，即假
select null != null; -- 返回null, 即假

/*
null 是空，无法使用 = != <> 等运算符比较
需要使用 is not null / is null 来比较
*/

select null is null; -- 1
select null is not null ; -- 0

null处理较为复杂。

列不使用null的原因

1. null的比较需要使用is null 和 is not null，比较不方便

2. 效率低，影响索引效果（null内部是其他值存储的，同样占用存储空间）