MySql数据库中数据类型

        本篇将介绍在 MySql 中的所有数据类型，其中主要分为四类：数值类型、文本和二进制类型、时间日期、String 类型。如下（图片来源：MySQL数据库）：

        目录如下：

目录

数值类型        

1. 整数类型

2. bit 类型

3. 浮点数类型

字符串类型

1. char 类型

2. varchar

3. 如何选择 char 和 varchar

日期和时间类型

enum和set

数值类型        

        数值类型的数据又主要分为两类，整数类型和浮点数类型还有 bit 类型，对于整数类型来说，占多少位就可以表示多少位数的大小，若不在定义变量的后面带上 unsigned 关键字修饰，则为有符号整数，其表示范围为：-2 ^ (n-1) ~ 2 ^ (n - 1) - 1，若带上 unsigned 关键字修饰，则表示的范围为 ：2 ^ n - 1，其中 n 代表的是数据类型的位数。

1. 整数类型

        对于整数类型，tinyint 数据类型占一个字节、smallint 占用两个字节、mediumint 占三个字节、int 占四个字节、bigint 占八个字节。

        我们以 tinyint 类型为例讲解，其余所有的整数类型的操作均和 tinyint 的一致，只是能表示的数据范围不同而已，（tinyint 数据类型为 8 位bit位，一个字节）如下：

        当我们向一个类型为 tinyint 类型的变量插入元素的时候，超过对应的范围就会导致我们插入失败，当我们定义一共 tinyint unsigned 元素的变量的时候，当范围超255的时候也会导致报错，如下：

        对于如上的操作我们就可得出结论：

        1. mysql 中插入数据不合法的数据，会被 mysql 直接给拦截掉，只要我们插入的数据，就一定是合法的数据。

        2. mysql 的中数据类型本身就是一种约束，这种约束会倒逼我们去遵守数据类型的使用，保证数据可预期的，完整的。

2. bit 类型

        对于 bit 类型来说，定义的时候为 bit(m)，其中 m 表示该 bit 类型的数据有多少位，默认值为 1，最大为64，所以 bit 类型的数据的位数范围为：1 ~ 64 位，如下：

        如上所示，当我们插入对应的元素的时候，超过 bit 位的位数的时候，就会导致插入失败，同时查看的时候，显示方法和整型类型的数据有所区别（若是其他版本的mysql，可能显示不出对应的 bit 类型的数据，因为可能是按照asc码值显示的时候，整个使用我们就可可以将数据转化位十六进制来显示，如下）。

3. 浮点数类型

        对于浮点数类型而言，主要被我们分 float、double 和 decimal 两类，先以 float 为例：语法为：float(m, d) [unsigned]：其中 m 为总显示长度，d 表示小数位数。占用的空间为 4 个字节，如 float(4，2) 表示的范围为 -99.99 ~ 99.99（对于小数的位数已经在定义的时候就已经定下来了，即使我们插入的小数位数没有那么多，都会自动填充0，小数位前的整数就是使用总位数减去小数位数得出，若在 float 后面加上 unsigned 修饰，则只能表示 0 ~ 99.99 的数据），如下：

        当我们插入不合法数据的时候，依然会导致插入失败。但是在浮点数中的小数部分遵守五舍六入（小于等于5舍掉，大于等于六进一），如下：

        但即使是这样的进位规则，若进入的时候导致数据溢出，还是会导致插入失败。但是对于 float 类型而言，在表示数据较大，或者浮点数较多的时候，也会导致精度缺失，如下：

        如上是我们采用默认的 float 类型的数据，当我们使用 double 类型数据的时候，其实也会有精度损失，只能说 double类型能表示的数据范围更大。

        当面对精度损失的时候，我们可以使用 decimal 类型来定义变量，如下：

        如上所示，当我们使用 float 类型的时候，就会出现一定的数据误差，所以平时我们使用浮点数最好使用 decimal 变量类型精度更高。

        float 表示的精度大约是 7 位；

        decimal 整数最大位数 m 为65，支持小数最小位数 d 为30，若 d 被省略，默认为 0，若 m 被省略，默认是10。

字符串类型

        在 mysql 中的字符串输入的时候，使用单引号和双引号都可以，但常用的为单引号。

1. char 类型

        对于 char 类型的语法为：char(L)，固定长度的字符串，L 是可以存储的长度，单位为字符，最大长度为 255，如下：

        如上所示，当我们将 name 变量定义为 char(2) 类型的数据，这个时候我们插入对应的字符，超过两个的时候，就会报错，在 mysql 中的 char 类型中存储的数据是 字符，而不是字节，特别是在 utf8 编码下的字符，一个字符占3个字节，所以可以存下对应数量的汉字。

2. varchar

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

        关于 varchar（L）中的 L 到底是多大，这个 L 的值和编码密切相关：

        varchar 长度可以指定为0 到 65535 之家的值，但有 1 ~ 3 个字节用于记录数据大小，所以有效字节数是 65532；

        但是需要注意的是，这个 varchar 类型的单位是字节，所以在 utf8 编码下的最多能存放 65532/3=21844 个字符，我们在定义的时候也只能最多定义 21844 个字符，若编码是 gbk 编码，则最多定义 65532/2=32766 个字符。如下：

        当我们在 gbk 编码下的数据库中创建对应的表的时候，最多定义 32766 个长度。

3. 如何选择 char 和 varchar

        char 和 varchar 的对比，在 utf8 编码下：

        1. 对于 char 类型，定义长度为 Ｌ，字节数为 3＊L；

        2. 对于 varchar 类型，定义长度为 L，实际插入字符长度为 n，占字节数为 3 * n + 1；

        那么我们如何选择 char 和 varchar 类型呢？

1. 若数据确定长度都一样长，我们就使用 char，如身份证号码、手机号
2. 若数据长度有变化，我们就使用 varchar，比名字，地址（但是最起码要保证存入的长度我们能存进去）
3. 定长的 char 类型比较浪费磁盘空间，但效率较高
4. 边长的 varchar 类型比较节省空间，但效率较低
5. 定长的意义为，直接开辟好空间
6. 边长的意义为，在补偿过自定义范围的情况下，用多少开辟多少

日期和时间类型

        常用的日期有一下三个：

        date 日期：' yyyy-mm-dd '，占用三个字节

        datetime 时间日期格式：' yyyy-mm-dd HH:ii:ss ' 表示范围从1000 到 9999，占用8字节

        timestamp 时间戳，从1970 年开始，格式和 datetime 一样，但不同的是 timestamp 的值更新之后会是当前的最新值，若不更新就是创建时候的值，如下，创建一个 birthday 的表：

mysql> create table birthday (-> t1 date,-> t2 datetime,-> t3 timestamp-> );
Query OK, 0 rows affected (0.02 sec)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 | 2024-10-23 21:46:49 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)mysql> update birthday set t1='2022-1-1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2022-01-01 | 2008-08-08 12:01:01 | 2024-10-23 21:48:42 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

        如上所示，假若我们创建出的 timestamp 类型的数据，在插入时会自动更新成当前时间，但是之后就不会改变了，只有当我们更新表中值的时候，timestamp 类型数据的值就会自动更新

enum和set

        枚举类型的语法：enum('选项1', '选项2', '选项3',...)   ---> 单选类型

        对于枚举的设定，只是提供了若干选项的值，最终在一个单元格中，实际只存储了其中一个值，而且处于效率来说，这些值实际存储的是数字，因为这些选项的每个选项值依次对应如下数字：1，2，3，4，……最多到65535个，所以当我们添加枚举值的时候，也可以添加这些对应的数字编号。

        集合类型的语法：set('选项值1', '选项值2', '选项值3',...)  ---> 多选类型

        该设定只是提供了若干选项的值，最终一个单元格中，设计可存储了其中任意多个值，而且出于效率考虑，这些值实际存储的是数字，因为这些选项的每个 选项值依次对应如下数字：1 2 4 8 16 32 64…… 最多64个

        注：在添加枚举值、集合值的时候不建议使用数字表示，因为不利于阅读。

如下：

mysql> CREATE TABLE votes (->     username VARCHAR(32),->     hobby SET('登山', '游泳', '篮球', '武术'),->     gender ENUM('男', '女')-> );
Query OK, 0 rows affected (0.02 sec)mysql> mysql> insert votes ('张山', '登山,武术', '男');
Query OK, 1 row affected (0.00 sec)mysql> insert votes values ('小红', '登山,游泳', '2');
Query OK, 1 row affected (0.00 sec)mysql> select * from votes where gender=2;
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| 小红     | 登山,游泳     | 女     |
+----------+---------------+--------+
1 row in set (0.00 sec)

        当我们在表中查找有关集合元素的时候，不能完全找到对应的元素，如下：

mysql> select * from votes;
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| 张山     | 登山,武术     | 男      |
| 小红     | 登山,游泳     | 女      |
| 王明     | 登山,篮球     | 男      |
| 小刘     | 登山          | 男      |
+----------+---------------+--------+
4 rows in set (0.00 sec)mysql> select * from votes where hobby='登山';
+----------+--------+--------+
| username | hobby  | gender |
+----------+--------+--------+
| 小刘     | 登山   | 男     |
+----------+--------+--------+
1 row in set (0.00 sec)

        如上所示，当我们使用查找语句查找 hobby 为登山的时候，只能找到刚好为登山的，不能找到其中有登山的，这个时候我们可以使用函数 find_in_set(sub,str_list)，使用方法如下：

mysql> select * from votes where find_in_set('登山', hobby);
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| 张山     | 登山,武术     | 男     |
| 小红     | 登山,游泳     | 女     |
| 王明     | 登山,篮球     | 男     |
| 小刘     | 登山          | 男     |
+----------+---------------+--------+
4 rows in set (0.00 sec)