【MySQL】数据类型

墨蓝 2024-04-27 07:47 4阅读 0赞

#### 文章目录 ####

*  数据类型分类
 *   *  数值类型
     *  小数类型
     *  字符串类型
     *  日期类型和时间类型
     *  enum 和 set

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

## 数据类型分类 ##

![在这里插入图片描述][19449a2d49404bd78ee0853127b60f5a.png]

*  `数值类型`：用于存储数值，包括整型和浮点型，如 int、double、float等。
 *  `字符串类型`：用于存储字符串，包括固定长度的char和可变长度的varchar，以及blob和text类型等。
 *  `日期和时间类型`：用于存储日期和时间，包括date、time、datetime、timestamp等。
 *  `枚举类型`：用于存储枚举值，例如 enum型。
 *  `集合类型`：用于存储一组值，例如set类型。
 *  **`注意`**：枚举类型和集合类型都属于字符串，因此它们被归为了String类型。

在MySQL中，不同的数据类型有不同的存储需求和和存储限制，选择合适的数据类型可以提高数据存储和查询的效率，并保证数据的准确性和完善性。

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

### 数值类型 ###

![在这里插入图片描述][8f7309f324c44539af81182bf6806e1b.png]

💕 **`tinyint类型`**

create table t1(num tinyint);

![在这里插入图片描述][36147f8c1ea84f59a76bae5a7ca1c05a.png]

**数值越界测试：**

mysql> insert into t1 values (127);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into t1 values (128);
    1264 - Out of range value for column 'num' at row 1
    mysql> insert into t1 values (-128);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into t1 values (0);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into t1 values (100);
    Query OK, 1 row affected (0.00 sec)

![在这里插入图片描述][b8348537b4794055b5ccfddf790795f4.png]

这里我们可以看到，如果插入的数据超过数值类型所能表示的范围，MySQL将不允许该数据插入。因为无法得知该数据是截断后的数据还是未经截断的数据。这里体现出了MySQL保证了数据库中数据的可靠性。

同时，超过数据类型范围的数据无法插入到表中也是一种 **`约束`**。约束是指在创建表时规定的对表中的数据进行限制的条件。它可以对表中某列或某几列添加一些限制条件，以保证表中的数据符合要求。

**注意：**

*  在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
 *  可以通过 unsigned 来说明某个字段是无符号的。
 *  尽量不适用unsigned，对于int类型可能存放不下的数据，int unsigned 同样可能存放不下，与其如此，还不如设计时将int类型提升为 bigint类型。
 *  如果索要存储的数据不会出现负数，可以使用unsigned。

**unsigned tinyint 测试**

create table t2 (num tinyint unsigned);

![在这里插入图片描述][5505df2a23e140e1b6fd3fabe688445a.png]

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

💕 **`bit类型`**

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

create table t3(id int, ch bit(8));

![在这里插入图片描述][f72167ff2d1d41e7a2d7af37f2629b73.png]

这里我们发现插入10后，没有显现出来。其实这是因为bit字段在显示时，是按照ASCII码对应的字符显示，而有一些ASCII码对应的字符是不可显的。

![在这里插入图片描述][bc8ad6bac67347758d706cf0fc0075e5.png]

这里我们可以看到，超过bit的数值范围时是无法插入的。同时我们也可以看到插入的97和65分别以ASCII码对应的字符的形式显示出来了。

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

create table t4(name varchar(32), gender bit(1));

![在这里插入图片描述][7a003294ff24450fa6b11f9c76c302cd.png]

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

### 小数类型 ###

💕 **`小数类型`**

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

**注意：** `float(4，2)` 表示的范围是-99.99~99.99，MySQL在保存值时会进行四舍五入。

create table t5(id int, salary float(4,2));

这里的 `4` 表示数字的总个数，`2` 表示小数点后数字的个数。

![在这里插入图片描述][06efb66e05c64ab0bd0b6e2edc1798a7.png]

如果我们定义的是 `float(4，2) unsigned`，因为把它指定为无符号数字时， 取值范围会变成`0~99.99`。它的取值范围相当于少了负数那一部分。

![在这里插入图片描述][da6c35d9465c41aa9e681ea0aeae0d76.png]

下面我们来验证一下：

![在这里插入图片描述][e17de5d667fd4571ad7a9b7b5eb633b4.png]

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

💕 **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 t6(id int, salary float(10,8), salary2 decimal(10,8));

![在这里插入图片描述][019fcdbf1cca411e947d1270848e7410.png]

**注意：**

*  decimal的精度更准确，因此如果我们希望某个数据表示高精度，可以选择decimal。
 *  float表示的精度最大约是7位。
 *  decimal整数最大位数m为65，支持小数最大位数d是30，如果d被省略，默认为0。如果m被省略，默认是10。

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

### 字符串类型 ###

💕 **char**

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

create table t7(id int, name char(2));

![在这里插入图片描述][bd345dce634a476a9058869acd49611c.png]

这里我们可以发现，这里 `char(2)` 中的 `2` 表示的其实是字符的最大数量，并不是字节数，而MySQL中的字符指定就是单纯的字符，字符可以是一个字母，也可以是一个汉字。而在C语言中，用的是两个字节来表示一个汉字字符。

这里我们需要注意的是：char 最多只能是255。

![在这里插入图片描述][1b4b74545d08402e9e81d99dae9d1e50.png]

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

💕 **varchar**

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

create table t8(id int, name varchar(6));

![在这里插入图片描述][6564796fcd4d4cbaa765c95e05bdc6b3.png]

这里我们可以看到，varchar和char类型一样，超过长度上限就会插入失败。

**注意：**

*  变长字符串是指长度可变的字符串，即字符串长度不固定，可以根据实际需要存储不同长度的字符数据。varchar 类型的字段会根据存储的实际数据长度来占用相应的存储空间，因此它比 char 类型更加灵活，能够在存储短字符串时占用更少的存储空间。
 *  varchar(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 字节）。

![在这里插入图片描述][fa62bf7bef98434ab157d9e53af07030.png]

**`char`和`varchar`比较：**

![在这里插入图片描述][413c1bf188604658b9e92281ee2aced7.png]

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

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

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

### 日期类型和时间类型 ###

常用的日期有如下三个：

*  `date`：用于存储日期（年、月、日）。其格式为 ‘YYYY-MM-DD’，其中 YYYY 表示四位年份，MM 表示月份（01-12），DD 表示日期（01-31）。date 数据类型占用 3 个字节。
 *  `datetime`：用于存储日期和时间（年、月、日、时、分、秒）。其格式为 ‘YYYY-MM-DD HH:MM:SS’，其中 YYYY 表示四位年份，MM 表示月份（01-12），DD 表示日期（01-31），HH 表示小时（00-23），MM 表示分钟（00-59），SS 表示秒（00-59）。datetime 数据类型占用 8 个字节。
 *  `timestamp`：用于存储日期和时间（年、月、日、时、分、秒），且其值自动随系统时间的更改而更新。其格式与 datetime 相同，但是其取值范围比 datetime 更小。timestamp 数据类型占用 4 个字节。
 *  `时间戳（timestamp）` 是一种特殊的数据类型，用于表示某个时间点。在 MySQL 中，时间戳通常指 Unix 时间戳，它表示自 1970 年 1 月 1 日 0 时 0 分 0 秒（UTC 时区）起到某个时间点的秒数。
 *  在 MySQL 中，timestamp 类型可以自动更新为当前时间，比如在插入新记录时，可以将 timestamp 列设置为默认值 CURRENT\_TIMESTAMP，这样每当插入一条新记录时，该列的值都会自动更新为当前时间。

create table t10(t1 date, t2 datetime, t3 timestamp);

![在这里插入图片描述][959d792ec0fe4d2299567abf73c6ae64.png]

注意： 插入和更新数据时，时间戳会自动地获取当前时间点的时间戳并更新。

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

### enum 和 set ###

*  `enum` 是一种多选一的数据类型，它可以存储一个固定的选项列表中的一个值，支持最多 65535 个元素，每个元素的长度不超过 65535 个字节。在创建表时，可以使用 enum(column\_name,‘option1’,‘option2’,…) 指定列名和选项列表，其中列名是必须的，选项列表中至少要有一个选项。出于效率考虑，这些值实际存储的是数字。因为这些选项的每个选项值依次对应如下数字：1，2，3，… 最多 65535 个；当我们添加枚举值时，也可以添加对应的数字编号。
 *  `set` 是一种多选多的数据类型，它可以存储一个固定的选项列表中的多个值，支持最多 64 个元素，每个元素的长度不超过 64 个字节。在创建表时，可以使用 set(column\_name,‘option1’,‘option2’,…) 指定列名和选项列表，其中列名是必须的，选项列表中至少要有一个选项。出于效率考虑，这些值实际存储的是数字，因为这些选项的每个选项值依次对应如下数字：1，2，4，8，16，32，… 最多 64 个。
 *  不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。\\n示例：\\n有一个调查表 votes，需要调查人的喜好， 比如在集合（唱，跳，rap，打篮球）中去选择（可以多选），同时填表人还需要填写自己的性别男或女（单选）。

**下面我们举一个例子：**

创建一个调查表votes，需要调查人的喜好，比如在集合（唱、跳、rap、打篮球）中去选择（可以多选），同时填表人还需要填写自己的性别男或女（单选）。

create table Votes(name varchar(32), hobby set('唱', '跳','rap','打篮球'), gender enum('男','女'));

![在这里插入图片描述][793f8f54ffef43c6ae03c4c7b0913c1d.png]

下面我们往表中插入一些数据观察一下：

![在这里插入图片描述][bb67566b81e44691a5fd74e8981037f6.png]  
![在这里插入图片描述][0c91e430e7d74e00b84d3e478518555c.png]  
![在这里插入图片描述][57bf128f97a643999e8697d4b4f74e02.png]

**查找数据：**

# 针对类型不是集合的查找
    select * from table_name where column_name=xxx;
    # 针对类型是集合的查找
    select * from table_name where find_in_set(xxx, column_name);

**集合查询使用 `find_in_set` 函数**

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

![在这里插入图片描述][665ed7b647f64d7994ae19f9457ef886.png]

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

[19449a2d49404bd78ee0853127b60f5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/574a641496334c7a8e446c3dcb8fe46f.png
[8f7309f324c44539af81182bf6806e1b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/6be3607e6fa04fa682d399f88f39a46e.png
[36147f8c1ea84f59a76bae5a7ca1c05a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/06e54dc49dec47aa9de7e10ae919d36e.png
[b8348537b4794055b5ccfddf790795f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/ccfa2a89a57f4664b16e112e5a81d8e3.png
[5505df2a23e140e1b6fd3fabe688445a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/67224fee3659408dad8c9998e9aea3e5.png
[f72167ff2d1d41e7a2d7af37f2629b73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/0d2f67e7065242bebd8aac35dd78daf0.png
[bc8ad6bac67347758d706cf0fc0075e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/014605f5a9a2414893443a5e9882426f.png
[7a003294ff24450fa6b11f9c76c302cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/c00385522905479f94a19f5c91a48230.png
[06efb66e05c64ab0bd0b6e2edc1798a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/13f282650b69450fa084e8d2d0bc3334.png
[da6c35d9465c41aa9e681ea0aeae0d76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/c980af39d2404338901901ecbb0eea66.png
[e17de5d667fd4571ad7a9b7b5eb633b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/e6a7a5fe626047c9bc982925a2d40081.png
[019fcdbf1cca411e947d1270848e7410.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/eb0a2fcafe0f4c9780bdf3a6840c6e72.png
[bd345dce634a476a9058869acd49611c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/f69ec118c98742d78e128d61eb853df6.png
[1b4b74545d08402e9e81d99dae9d1e50.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/668a8c1dca0948c3b4a86f3b199b0ca3.png
[6564796fcd4d4cbaa765c95e05bdc6b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/2d1031a7ef42466285ab56bd0fc08f08.png
[fa62bf7bef98434ab157d9e53af07030.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/648f08e7125241d8a7826005f767584f.png
[413c1bf188604658b9e92281ee2aced7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/ab17a3b4a3e64e0990c838c6d3553b08.png
[959d792ec0fe4d2299567abf73c6ae64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/dada63ac9a214cf7b85508ac3e885c25.png
[793f8f54ffef43c6ae03c4c7b0913c1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/94bee2f2e9d64bb2b6437a425b58a9d6.png
[bb67566b81e44691a5fd74e8981037f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/c472bcb590644fba8fb41bfdb4c06680.png
[0c91e430e7d74e00b84d3e478518555c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/efcda05c933141d398a66f93f8674b94.png
[57bf128f97a643999e8697d4b4f74e02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/6334b61c116c4ab98d06627e7cad248f.png
[665ed7b647f64d7994ae19f9457ef886.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/4fd8585b52cd4b97b85ae526acc60275.png