【MySQL学习】MySQL数据类型

前言

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> 

此时就能查询出全部结果。