前言
MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
一、数据类型分类
【注意】
MySQL本身是不支持BOOL类型的，当把一个数据设置成BOOL类型时，数据库会自动将其转换成TINYINT(1)的数据类型，其实这个就是变相的BOOL类型，因为TINYINT(1)只有1和0两种取值，可以分别对应BOOL类型的TRUE和FALSE。
二、数值类型
2.1 TINYINT类型
TINYINT类型数值越界测试：
创建一个含有 tinyint 类型的表结构
create table tt1( num tinyint );
由于tinyint类型占用一个字节，因此其数值范围为-128 ~ 127，只有插入该范围的数值才是合法的。
当试图插入128，则提示越界：
【注意】
- 在MySQL中，整型可以指定有符号和无符号的，并且默认是有符号的。
- 可以通过UNSIGNED来指明某个字段是无符号的。
无符号的TINYINT案例：
create table if not exists tt2( num tinyint unsigned );
无符号的TINYINT的数值范围是0 ~ 255，只能插入次范围内的数值，否则就会出错。
- 其他整型类型与TINYINT类型类似，只是其大小有所不同而已。
- 另外，尽量不使用UNSIGNED，对于INT类型可能存放不下的数据，UNSIGNED同样可能存放不下，与其如此，还不如设计时，将INT类型提升为BIGINT类型。
2.2 BIT类型
BIT类型的使用：
create table if not exists t3( id int unsigned, a bit(8) );
当我们向表 t3里面插入数据 (10, 10) 时，发现 a 对应的时没有显示：
原因是 BIT 字段在显示的时候，是按照 ASCII 码对应的值显示的，例如：
如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间：
2.3 小数类型
2.3.1 float类型
语法：
float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节
案例：
小数：float(4, 2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。
create table if not exists t5( id int, salary float(4,2) );
明显可以看到MySQL进行了四舍五入操作，如果插入的数值长度超过4，则会报错：
案例：
如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99
create table if not exists t6( id int, salary float(4,2) unsigned );
2.3.2 decimal类型
语法：
decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数
案例：
- decimal(5,2) 表示的范围是 -999.99 ~ 999.99
- decimal(5,2) unsigned 表示的范围 0 ~ 999.99
- decimal和float很像，但是有区别: float和decimal表示的精度不一样
create table t7 ( id int, salary float(10,8), salary2 decimal(10,8) );
通过对比，发现decimal的精度更准确，因此如果我们希望某个数据表示高精度，选择decimal类型。
【说明】
- float表示的精度大约是7位
- decimal整数最大位数m为65，支持小数最大位数d是30。如果d被省略，默认为0，如果m被省略，默认是10。
- 如果希望小数的精度高，推荐使用decimal。
三、字符串类型
3.1 CHAR类型
语法：
char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255
案例：
create table if not exists t8( id int, name char(2) );
【说明】
- char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个
3.2 VARCHAR类型
语法：
varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节
案例：
varchar(6) 表示这里可以存放6个字符
create table if not exists t9( id int, name varchar(6) );
【说明】
关于 varchar(len) 中的 len 到底是多大，这个 len 值和表的编码密切相关：
- varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532
- 当我们的表的编码是 UTF-8 时，varchar(n) 的参数 n 最大值是 65532 / 3 = 21844，因为 UTF-8 中，一个字符占用3个字节
- 如果编码是GBK，varchar(n) 的参数 n 最大值是 65532 / 2 = 32766，因为GBK中，一个字符占用2字节
例如：
- 验证 UTF-8 编码时，n不能超过21844
3.3 CHAR和VARCHAR比较
采用UTF-8编码：
如何选择定长或变长字符串？
- 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
- 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是要保证最长的能存的进去。
- 定长的磁盘空间比较浪费，但是效率高。
- 变长的磁盘空间比较节省，但是效率低。
- 定长的意义是，直接开辟好对应的空间
- 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。
四、日期和时间类型
常用的日期有如下三个：
- date：日期 yyyy-mm-dd ，占用三字节
- datetime：时间日期格式 yyyy-mm-dd HH:ii:ss 表示范围从 1000 到 9999 ，占用八字节
- timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节
案例：
创建表
create table if not exists birthday ( t1 date, t2 datetime, t3 timestamp );
插入数据
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); Query OK, 1 row affected (0.00 sec) mysql> select * from birthday; +------------+---------------------+---------------------+ | t1 | t2 | t3 | +------------+---------------------+---------------------+ | 1997-07-01 | 2008-08-08 12:01:01 | 2023-05-02 19:20:06 | +------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql>
添加数据时，时间戳自动补上当前时间。
更新数据
mysql> update birthday set t1='2000-01-01'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from birthday; +------------+---------------------+---------------------+ | t1 | t2 | t3 | +------------+---------------------+---------------------+ | 2000-01-01 | 2008-08-08 12:01:01 | 2023-05-02 19:22:08 | +------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql>
更新数据时，时间戳会更新成当前时间。
五、ENUM和SET类型
ENUM类型：
- enum：枚举，“单选”类型；
- 语法：enum(‘选项1’, ‘选项2’, ‘选项3’, …);
【说明】
- ENUM类型只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；
- 而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,… 最多65535个；
- 当我们添加枚举值时，也可以添加对应的数字编号，但是不建议这样做，因为不利于阅读。
SET类型：
- set：集合，“多选”类型；
- 语法：set(‘选项值1’, ‘选项值2’, ‘选项值3’, …);
【说明】
- SET类型提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；
- 出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，… 最多64个。
案例：
有一个调查表votes，需要调查人的喜好， 比如：爱好在（登山，游泳，篮球，武术）中去选择，可以多选，性别在（男，女）选择，只能是单选。
创建votes表
create table votes( username varchar(30), hobby set('登山','游泳','篮球','武术'), gender enum('男','女') );
插入数据
mysql> select * from votes where hobby='武术'; +----------+--------+--------+ | username | hobby | gender | +----------+--------+--------+ | 王五 | 武术 | 女 | +----------+--------+--------+ 1 row in set (0.00 sec) mysql>
查询
查找喜欢武术的人：
mysql> select * from votes where hobby='武术'; +----------+--------+--------+ | username | hobby | gender | +----------+--------+--------+ | 王五 | 武术 | 女 | +----------+--------+--------+ 1 row in set (0.00 sec) mysql>
使用上面的查询语句，只能查出一个唯一爱好是武术的人。
集合查询使用find_ in_ set函数：
find_in_set(sub, str_list)：如果sub在str_list中，则返回下标；如果不在，返回0；str_list是用逗号分隔的字符串。
例如：
mysql> select find_in_set('a', 'a,b,c'); +---------------------------+ | find_in_set('a', 'a,b,c') | +---------------------------+ | 1 | +---------------------------+ 1 row in set (0.00 sec) mysql> select find_in_set('d', 'a,b,c'); +---------------------------+ | find_in_set('d', 'a,b,c') | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec) mysql>
查询爱好武术的人：
mysql> select * from votes where find_in_set('武术', hobby); +----------+----------------------+--------+ | username | hobby | gender | +----------+----------------------+--------+ | 张三 | 登山,武术 | 男 | | 李四 | 游泳,篮球,武术 | 男 | | 王五 | 武术 | 女 | +----------+----------------------+--------+ 3 rows in set (0.00 sec) mysql>
此时就能查询出全部结果。
