1. 数据类型分类

1.1 数值类型

数据类型	说明
BIT(M)	位类型，M为指定位数，默认值为1，范围1-64
TINYINT [UNSIGNED]	带符号的范围为-128~127，无符号的的范围为0 ~ 255，默认有符号
BOOL	使用1表示真，0表示假
SMALLINT [UNSIGNED]	代符号的是 -2^15 ~ 2^15-1, 无符号的范围是 0~2^16-1
INT [UNSIGNED]	代符号的是 -2^31 ~ 2^31-1, 无符号的范围是 0~2^32-1
BIGINT [UNSIGMED]	代符号的是 -2^63 ~ 2^63-1, 无符号的范围是 0~2^64-1
FLOAT[(M,D)] [UNSIGNED]	M指定长度，D指定小数位数，占用4字节
DOUBLE[(M,D)] [UNSIGNED]	精度比FLOAT高，占用8字节
DECIMAL[(M,D)] [UNSIGNED]	M指定长度，D指定小数位数

类型	所占字节	最小值	最大值	(无符号)最小值	(最大值)无符号
TINYINT	1	-128	127	0	255
SMALLINT	2	-32768	32767	0	65535
MEDIUMINT	3	-8388608	8388607	0	16777215
INT	4	-2147483648	2147483647	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	0	18446744073709551615

1.2 文本和二进制类型

数据类型	说明
CHAR(size)	固定长度字符串，最大为255
VARCHAR(size)	可变长度字符串，最大为65525
BLOB	二进制数据
TEXT	大文本，不支持全文索引，不支持默认值

1.3 日期类型

数据类型	说明
DATE	YYYY-MM-DD，仅存储日期部分，不包含时间部分，占三个字节
DATETIME	YYYY-MM-DD HH:MM:SS，不受时区影响，存储的是固定的时间点，占8个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS，可以设置为自动更新为当前时间（使用 DEFAULT CURRENT_TIMESTAMP),占4个字节

2 数值类型

2.1 TINYINT 类型

2.1.1 默认有符号类型

1. 创建表
   命令：create table t1 (test tinyint );
   
2. 插入规范的值
   命令：insert into t1 values(1);
   
3. 插入越界的值
   命令：insert into t1 values(128);
   

2.1.2 无符号类型

1. 添加列 untest
   命令：alter table t1 add column untest tinyint unsigned;
   
2. 插入规范值
   命令：insert into t1 (untest) values(128);
   
3. 插入越界值
   命令：insert into t1 (untest) values(256);
   

2.2 INT 类型

2.2.1 默认有符号类型

1. 创建表
   命令：create table t2 (test int);
   

2. 插入规范数据
   命令：insert into t2 values(1);
   

3. 插入越界数据
   命令：insert into t2 (test) values(2147483648);
   

2.2.2 无符号类型

1. 新增列 untest
   命令：alter table t2 add column untest int unsigned;
   

2. 插入规范数据
   命令：insert into t2 (untest) values(1);
   

3. 插入 有符号类型越界的 2147483648
   命令：insert into t2 (untest) values(2147483648);
   
   注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

2.3 BIT 类型

2.3.1 语法

bit[(M)]: 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

存储和取值：

• 存储：BIT 类型存储的是二进制位，每个位可以是0或1。
• 取值：可以使用 CAST 或 CONVERT 函数将 BIT 类型转换为其他类型，以便更容易地查看和处理。

2.3.2 举例

1. 创建表
   命令：create table t3 (test bit(8));
   

2. 插入 1
   命令：insert into t3 values(1);
   

3. 查看数据
   命令：select * from t2;
   
   为什么没有数据呢？

   是空值？肯定不是。因为空值的位置会打印出NULL，NULL表示未知的值。这里没打印NULL说明这里有值，但是为什么会打印不出来呢？

   因为，bit字段在显示时，是按照ASCII码对应的值显示。10对应的没有ASCII码值。

4. 插入一个有的对应ASCII码值的数
   命令：inset into t3 values(64),(65),(66);
   

5. 再次查看数据
   
   66对应的ASCII码为‘A’

6. 如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间。
   

   插入 2
   
   一个比特位只能存储1或0，存储2需要两个比特位。

2.4 FLOAT 类型

2.4.1 语法

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

示例:
float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。
如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99

2.4.2 默认有符号类型

1. 创建表
   命令：create table t1 (test float(4,2));
   

2. 插入规范数据
   命令：insert into t1 values(2.20)
   

3. 插入三位小数的数据 33.333
   命令：insert into t1 values(33.333);
   
   查询存储的数据：select * from t1;
   
   可以看到，存储的数据是已经四舍五入过的。

4. 插入三位小数的数据 99.999
   命令：insert into t2 values(99.999);
   
   为什么存储不进去呢？
   答：99.999四舍五入后是100.00，而创建的test列的范围是-99.99~99.99,100.00已经越界了。

2.4.3 无符号类型

1. 添加列
   命令：alter table t1 add column untest float unsigned;
   

2. 插入规范数据
   

3. 插入负数
   

2.4.4 插入特大值

1. 创建表
   命令：create table t2 (n float);
   
2. 插入特大值后在查看插入的数据
   

2.5 DECIMAL 类型

2.5.1 语法

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数，必须需要指定m和d。

decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99

decimal和float很像，但是有区别:
float和decimal表示的精度不一样
decimal精度比float高

说明：

• float表示的精度大约是7位。
• decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。

建议：如果希望小数的精度高，推荐使用decimal。

2.5.2 举例

1. 创建表
   命令：create table t3 (test decimal(15,2));

2. 插入特大值后再查询。
   

3. 字符串类型

3.1 解释MySQL的字符

在utf8中，一个汉字占3个字节；在gbk中一个汉字占2个字节。
那么在MySQL中设置字段为char(2) ，字符集为utf8，为什么能输入“中国呢” ？
答:MySQL中的一个字符表示的是：就是一个字符。而在C/C++中，一个字符代表的是一个字节。

3.2 CHAR 类型

3.2.1 语法

语法：
char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

3.2.2 举例

1. 创建表
   命令：create table t1 (name char(10));
   

2. 插入，插入的字符串要用单引号/双引号引注
   

3.3 VARCHAR 类型（重点）

3.3.1 语法

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

3.3.2 举例

1. 创建表
   命令：create table t2 (name varchar(10));
   

2. 插入，插入的字符串要用单引号/双引号引注
   命令：insert into t2 values('李明');
   

3.3.3 varchar 能开多大空间？

关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字
  节数是65532。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符
  占用2字节）。

在MySQL的表中，数据是按行存储的，其实每一行最大可使用的字节也是65535，再去除需要的站位字符其实也就不能完全存储65535字节大小的数据。可以只设置一行，该行是varchar(21844)

1. 验证使用utf8字符集最大存储21844个字符
   
   显然，使用utf8字符集最大存储21844个字符。

2. 验证每行最大存储65535个字节
   
   

   当设置一个字段为65535字节的列后，再添加一个字段为1bit的列失败，显然，每一列的字节为65535。

3.4 CHAR 和 VARCHAR 的比较

实际存储	char(4)	varchar(4)	char占用字节	varchar占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
a	a	a	4*3=12	1*3+1=4
abdce			数据超过长度	数据超过长度

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4. 日期和时间类型

4.1 简单说明

常用的日期有如下三个：

• date :日期 ‘yyyy-mm-dd’ ，占用三字节
• datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用
  四字节

4.2 示例

1. 创建表
   命令：create table t1 (t1 date, t2 datetime, t3 timestamp);
   

2. 插入，要用单引号把时间引注
   命令：insert into t1(t1,t2) values('2020-10-10, 2020-08-18');
   

3. 查看数据
   
   没有给t3 列插入数据，为什么t3会有数据呢？
   t3字段是timestamp时间戳，时间戳不用自己插入，当插入数据和更改数据的时候会自动更新。

4. 再插入数据，然后进行查看
   

5. 更新数据
   命令：update t1 set t1='2024-8-8';
   

5.ENUM 枚举

5.1 语法

• enum：枚举，“单选”类型；
• enum(‘选项1’,‘选项2’,‘选项3’,…);该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；
• 当我们添加枚举值时，也可以添加对应的数字编号（下标从1开始）。

5.2 示例

1. 创建表, 选择 男或女，单选
   命令：create table t1(gender enum('男', '女'));
   
2. 插入 ‘男’
   命令：insert into t1 values('男');
   
3. 插入 1
   命令：insert into t1 values(1);
   
4. 查询 男生的一列，where gender = '男'
   命令：select * from t1 where gender = '男';
   
5. 查询 男生的一列，where gender = 1
   命令：select * from t1 where gender = 1;
   

6. SET 集合

6.1 语法

• set：集合，“多选”类型；
• set(‘选项值1’,‘选项值2’,‘选项值3’, …);该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；
• 出于效率考虑，这些值实际存储的是==“数字”==，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，…
  最多64个。

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

6.2 举例

1. 创建表，集合中的元素可多选。
   命令：create table t1(hobby set('代码', '羽毛球', '乒乓球' ,''足球', '游泳'));
   

2. 插入数据，直接说明集合中的元素。引号中为集合的元素，用逗号隔开
   

3. 插入数据，用数值。
   
   同学们可以发现，插入的数不是和集合中的元素一一对应，那么这是按什么来标准来执行的呢？
   集合不是直接采用下标，而是用bit位
   从低位到高位，对应set的从左到右。
   加入插入的是 1， 1的bit位是 0001，最后一位是1，也就是‘代码’
   插入 2 , 2的bit位是 0010 ，倒数第二位是1，也就是‘羽毛球’
   插入3,3的bit位是 0011，后两位是1，是 ‘代码’，’羽毛球‘
   

4. 查询，直接用集合中的元素。这次想获得含有代码的行。
   命令：select * from t1 where hobby='代码'
   
   显然这不是我们想要的数据。那么，使用数值会怎么样呢？

5. 使用数值查询含代码 的行，where hobby=1
   命令：select * from t1 where hobby=1;
   
   显然，这也不是我们想要的结果，那么没有办法了吗？
   当然不是，我们可以使用find_in_set函数,也可以使用位运算。

   binary 2代表代码 的二进制表示
   

6.3 find_in_set 函数

6.3.1 语法

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；
str_list 用逗号分隔的字符串。

6.3.2 举例

1. 查询含代码的行
   命令：select * from t1 where find_in_set('代码', hobby);
   

2. 查询含乒乓球的行
   命令：select *from t1 where find_in_set('乒乓球', hobby);