MySQL数据类型

一、数值类型

1.1 int类

类型	字节	最小值	最大值
tinyint	1	-2^7	2^7 - 1
tinyint unsigned	1	0	2^8 - 1
smallint	2	-2^15	2^15 - 1
smallint unsigned	2	0	2^16 - 1
mediumint	3	-2^23	2^23 - 1
mediumint unsigned	3	0	2^24 - 1
int	4	-2^31	2^31 - 1
int unsigned	4	0	2^32 - 1
bigint	8	-2^63	2^63 - 1
bigint unsigned	8	0	2^64 - 1

1.2 bit类

bit[(M)]:位字段类型，M表示每个值的位数，范围为1~64，如果M不写默认为1

 bit存放数据显示时，是按照ASCII码表来显示的，例如下面的例子

CREATE TABLE t1 (id int, b bit(8));
INSERT INTO t1 (id, b) VALUES (1, 10);
INSERT INTO t1 (id, b) VALUES (2, 65);

 可以看到，此时对于id= 1的字段，显示不出来，bit是按照ASCII码表来显示的，而A的ASCII码表值为65，所以能显示出来。

1.3 小数类型

1.3.1 float

float(m,d) [unsigned]:m表示显示长度，d指定小数位数，占4位

float类型在MySQL中保存时会进行四舍五入

CREATA TABLE t1 (num float(4,2));
--插入三组数据
INSERT INTO t1 VALUES (99.99);
INSERT INTO t1 VALUES (99.994);
INSERT INTO t1 VALUES (99.995);

 小数类型也可以声明为UNSIGNED，但是小数类型和INT类型不一样，小数类型声明为浮点类型能表示的范围是有符号的一半。

double和float差不多，不做介绍了

1.3.2 decimal

decimal(m,d) [unsigned]:m表示指定长度，d指定小数位数，占16位

• decimal和float用法一模一样，区别上就是decimal精度上比float高

• decimal最大位m为65，最大小数位d为30，如果d省略默认是0，如果m省略默认是10

• float最大精度为7位

下面测试一下float的精度

CREATE TABLE t3 (
    id int,
    num1 float(10,8),
    num2 decimal(10,8)
);

INSERT INTO t3 (id, num1, num2) VALUES (1, 10.12345678, 10.12345678);

上面可以看到，当精度超过7位的时候，float的精度就不准了，因此需要高精度一般建议使用decimal。

二、字符串类型

2.1 char

char(L)：固定字符长度，L表示可存储的长度，单位为字符，最大长度可以为255字符

• char的单位为字符，而不是字节，字符可以是汉字和字母

• 汉字和字母不用，汉字所占字节数在不同的编码下有所不同

• char最多只能存储255个字符

2.2 varchar

varchar(L)：可变长度字符串，L表示字符长度，最大长度为65535个字节

• L表示可以存储的最大字符长度，而其字符串所占的最大长度只能为65535个字节。

• L最大值为多大，和表的编码相关。

• varchar的长度可以指定为0-65535，但是里面有1-3个字节用于记录varchar的数据大小，因此其有效长度为65532个字节。

• 当编码为utf8时候，L的最大长度为65532/3=21844，因为utf8中，一个中文字符占3个字节；当编码为gbk时，L的最大长度为65532/2=32766，因为gbk一个中文字符占2个字节。

2.3 char和varchar

• char是定长的，类似于C++中的数组。例如：定义char(10)，即使你只存放了abc那其所占的还是10字节。

• varchar是变长的，类似于C++中的string。所用字符实际为多少，则分配多少。

• char比较浪费空间，但是效率略高。varchar相反。

三、日期和时间

常用的有三种：

• date：日期，表示为yyyy-mm-dd，占用三个字节

• datetime：时间日期，表示为yyyy-mm-dd HH:ii:ss，占八个字节

• timestamp：时间戳，占四个字节

四、enum和set

4.1 enum

• enum枚举类型，该类型提供了若干个选项，该字段只能存储选项中的一个值，实际上为了效率存储，存储的还是数组1，2，3……。

CREATE TABLE IF NOT EXISTS t4(
	id int,
	name varchar(10),
	specialty enum('Java','C++','C#','Python')
) engine=InnoDB default charset=utf8;

--正常插入
INSERT INTO t4 (id ,name, specialty) VALUES (1,'张三','C++');
INSERT INTO t4 (id ,name, specialty) VALUES (1,'李四','Java');
INSERT INTO t4 (id ,name, specialty) VALUES (1,'王五','C#');

SELECT * FROM t4;

#插入枚举不存在的类型会报错
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','Rust');

#插入数字
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','0');
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','1');
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','2');
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','3');
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','4');
INSERT INTO t4 (id ,name, specialty) VALUES (2,'张三','5');

• 1用数字插入一样是可以的，因为enum存储的就是数字

 

• enum中，0表示空，后续类推

 4.2 set

set和enum不用，enum只能选择一个，而set可以选择多个

CREATE TABLE IF NOT EXISTS t5(
	id int,
	name varchar(10),
	favour set('Java','C++','C#','Python')
) engine=InnoDB default charset=utf8;

INSERT INTO t5 (id ,name, favour) VALUES (1,'张三','C++,Java');
INSERT INTO t5 (id ,name, favour) VALUES (1,'李四','Java,Python');
INSERT INTO t5 (id ,name, favour) VALUES (1,'王五','C#,C++,Java');

---

 但是使用set的时候，如果要进行查询，例如查询爱好是C++的：

SELECT * FROM t5 WHERE favour='C++';

此时查出来是空表，因为如果利用条件查询，只能查出来favour只有C++的。

如果要查有C++，需要使用函数：find_in_set(str, str_list)

SELECT * FROM t5 WHERE find_in_set(favour='C++');

---

set也可以利用数字插入

INSERT INTO t5 (id ,name, favour) VALUES (2,'张三','0');
INSERT INTO t5 (id ,name, favour) VALUES (2,'张三','1');
INSERT INTO t5 (id ,name, favour) VALUES (2,'张三','2');
INSERT INTO t5 (id ,name, favour) VALUES (2,'张三','3');
INSERT INTO t5 (id ,name, favour) VALUES (2,'张三','4');
INSERT INTO t5 (id ,name, favour) VALUES (2,'张三','5');

 

• 0表示空，1表示Java，2表示C++，3表示的却是Java，C++

• 因为set是用比特位表示的

• 0000（0）表示空

• 1000（1）Java置1，表示Java

• 1100（3）Java和C++都置1，表示Java，C++