【MySQL】数据类型

---

数据类型分类

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

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

---

数值类型

💕 tinyint类型

create table t1(num tinyint);

数值越界测试：

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)

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

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

注意：

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

unsigned tinyint 测试

create table t2 (num tinyint unsigned);

---

💕 bit类型

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

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

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

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

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

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

---

小数类型

💕 小数类型

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 表示小数点后数字的个数。

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

下面我们来验证一下：

---

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

注意：

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

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

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

---

💕 varchar

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

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

这里我们可以看到，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 字节）。

char和varchar比较：

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

• 如果数据确定长度都一样，就使用定长（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);

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

---

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('男','女'));

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

查找数据：

# 针对类型不是集合的查找
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 用逗号分隔的字符串。

---