【MySQL学习】MySQL数据类型

迷南。 2023-09-27 18:04 37阅读 0赞

### 文章目录 ###

*  前言
 *  一、数据类型分类
 *  二、数值类型
 *   *  2.1 TINYINT类型
     *  2.2 BIT类型
     *  2.3 小数类型
     *   *  2.3.1 float类型
         *  2.3.2 decimal类型
 *  三、字符串类型
 *   *  3.1 CHAR类型
     *  3.2 VARCHAR类型
     *  3.3 CHAR和VARCHAR比较
 *  四、日期和时间类型
 *  五、ENUM和SET类型

--------------------

# 前言 #

MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

# 一、数据类型分类 #

![146ca1ceac5f4cbcbce5c06a1d5ec734.png][]

【注意】  
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，只有插入该范围的数值才是合法的。  
![cefd39745b7d4a05974137bb0a0f9194.png][]  
当试图插入128，则提示越界：  
![49a2b4765e7542979d0246e0fa390078.png][]  
【注意】

*  在MySQL中，整型可以指定有符号和无符号的，并且默认是有符号的。
 *  可以通过UNSIGNED来指明某个字段是无符号的。

**无符号的TINYINT案例：**

create table if not exists tt2(
    	num tinyint unsigned
    );

无符号的TINYINT的数值范围是0 ~ 255，只能插入次范围内的数值，否则就会出错。  
![6601050a0b6b4637b185e29eb00246b1.png][]

*  其他整型类型与TINYINT类型类似，只是其大小有所不同而已。
 *  另外，尽量不使用UNSIGNED，对于INT类型可能存放不下的数据，UNSIGNED同样可能存放不下，与其如此，还不如设计时，将INT类型提升为BIGINT类型。

## 2.2 BIT类型 ##

**BIT类型的使用：**

create table if not exists t3(
    	id int unsigned, 
    	a bit(8)
    );

![b4cd40c97eb34d2f9d3cf772e702b791.png][]

当我们向表 t3里面插入数据 (10, 10) 时，发现 a 对应的时没有显示：  
![ad5c98e30254476c8d75bdeef3d46c18.png][]  
**原因是 BIT 字段在显示的时候，是按照 ASCII 码对应的值显示的，例如：**  
![28d1ced11a924577a136b79b1042600c.png][]  
**如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间：**  
![ffcc11b5a995496b95fdfe2a45e5c296.png][]

## 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)
    );

![f417e424da00429c931b7bdc222a578c.png][]  
明显可以看到MySQL进行了四舍五入操作，如果插入的数值长度超过4，则会报错：  
![6ad9c431e9da449a8302908b9b1bc67d.png][]

**案例：**

> 如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99

create table if not exists t6(
    	id int, 
    	salary float(4,2) unsigned
    );

![dcb05d727f724e5bbdde034b796f5e2d.png][]

### 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)
    );

![4a1e97130a4442daa6590da3446cd8e3.png][]  
通过对比，发现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)
    );

![ef727b1e763d4443b88552f2f6f12e48.png][]  
【说明】

*  char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个

## 3.2 VARCHAR类型 ##

**语法：**

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

**案例：**

> varchar(6) 表示这里可以存放6个字符

create table if not exists t9(
    	id int,
    	name varchar(6)
    );

![eafc452c1c8944538b223103b7f6bf75.png][]  
【说明】  
关于 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  
    ![55aab6018b164c14b72f836cdf65ed18.png][]

## 3.3 CHAR和VARCHAR比较 ##

**采用UTF-8编码：**  
![fb5e4345e2ef4fff861ddba0ef5a6bf3.png][]  
**如何选择定长或变长字符串？**

*  如果数据确定长度都一样，就使用定长（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('男','女')
    );

> 插入数据

insert into votes values('张三', '登山,武术', '男');
    
    insert into votes values('李四', '游泳,篮球,武术', '男');
    
    insert into votes values('王五','武术','女');

> 查询

查找喜欢武术的人：

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>

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

[146ca1ceac5f4cbcbce5c06a1d5ec734.png]: https://img-blog.csdnimg.cn/146ca1ceac5f4cbcbce5c06a1d5ec734.png
[cefd39745b7d4a05974137bb0a0f9194.png]: https://img-blog.csdnimg.cn/cefd39745b7d4a05974137bb0a0f9194.png
[49a2b4765e7542979d0246e0fa390078.png]: https://img-blog.csdnimg.cn/49a2b4765e7542979d0246e0fa390078.png
[6601050a0b6b4637b185e29eb00246b1.png]: https://img-blog.csdnimg.cn/6601050a0b6b4637b185e29eb00246b1.png
[b4cd40c97eb34d2f9d3cf772e702b791.png]: https://img-blog.csdnimg.cn/b4cd40c97eb34d2f9d3cf772e702b791.png
[ad5c98e30254476c8d75bdeef3d46c18.png]: https://img-blog.csdnimg.cn/ad5c98e30254476c8d75bdeef3d46c18.png
[28d1ced11a924577a136b79b1042600c.png]: https://img-blog.csdnimg.cn/28d1ced11a924577a136b79b1042600c.png
[ffcc11b5a995496b95fdfe2a45e5c296.png]: https://img-blog.csdnimg.cn/ffcc11b5a995496b95fdfe2a45e5c296.png
[f417e424da00429c931b7bdc222a578c.png]: https://img-blog.csdnimg.cn/f417e424da00429c931b7bdc222a578c.png
[6ad9c431e9da449a8302908b9b1bc67d.png]: https://img-blog.csdnimg.cn/6ad9c431e9da449a8302908b9b1bc67d.png
[dcb05d727f724e5bbdde034b796f5e2d.png]: https://img-blog.csdnimg.cn/dcb05d727f724e5bbdde034b796f5e2d.png
[4a1e97130a4442daa6590da3446cd8e3.png]: https://img-blog.csdnimg.cn/4a1e97130a4442daa6590da3446cd8e3.png
[ef727b1e763d4443b88552f2f6f12e48.png]: https://img-blog.csdnimg.cn/ef727b1e763d4443b88552f2f6f12e48.png
[eafc452c1c8944538b223103b7f6bf75.png]: https://img-blog.csdnimg.cn/eafc452c1c8944538b223103b7f6bf75.png
[55aab6018b164c14b72f836cdf65ed18.png]: https://img-blog.csdnimg.cn/55aab6018b164c14b72f836cdf65ed18.png
[fb5e4345e2ef4fff861ddba0ef5a6bf3.png]: https://img-blog.csdnimg.cn/fb5e4345e2ef4fff861ddba0ef5a6bf3.png