【数据库初阶】MySQL数据类型

亲爱的小伙伴们，大家好！在这篇文章中，我们将深入浅出地为大家讲解 MySQL数据类型 帮助您轻松入门，快速掌握核心概念。

如果文章对您有所启发或帮助，请别忘了 点赞 👍、收藏 🌟、留言 📝 支持！您的每一份鼓励，都是我持续创作的源动力。让我们携手前行，共同进步！

文章目录

• • @[toc]
• 1. 数值类型
• • 🎧1.1 数值类型纵览🎧
  • 🎧1.2 smallint 类型🎧
  • 🎧1.3 类比 C/C++ 截断操作🎧
  • 🎧1.4 修改表数据类型🎧
  • 🎧1.5 无符号类型🎧
• 2. bit 类型
• • 🎧2.1 bit 类型介绍🎧
  • 🎧2.2 bit 类型详解🎧
• 3. 小数类型
• • 🎧3.1 float类型🎧
  • 🎧3.2 decimal 类型🎧
• 4. 字符串类型
• • 🎧4.1 char类型🎧
  • 🎧4.2 varchar类型🎧
  • 🎧4.3 char 和 varchar 比较🎧
• 5. 日期类型
• 6. enum 和 set
• • 🎧6.1 enum类型🎧
  • 🎧6.2 set 类型🎧
  • 🎧6.3 查找🎧

上一篇文章中，博主介绍了 :

• MySQL表的相关操作

建议将上一篇文章看完之后再来看这篇文章，链接如下：

【数据库初阶】Linux中表的基础操作

那么接下来正文开始：

1. 数值类型

🎧1.1 数值类型纵览🎧

• MySQL中数据类型为数值类型的如下：

数据类型	字节数	带符号最小值	带符号最大值	无符号最小值	无符号最大值
TINYINT	1	-128 (-2^7)	127 (2^7 - 1)	0	255 (2^8 - 1)
SMALLINT	2	-32,768 (-2^15)	32,767 (2^15 - 1)	0	65,535 (2^16 - 1)
MEDIUMINT	3	-8,388,608 (-2^23)	8,388,607 (2^23 - 1)	0	16,777,215 (2^24 - 1)
INT	4	-2,147,483,648 (-2^31)	2,147,483,647 (2^31 - 1)	0	4,294,967,295 (2^32 - 1)
BIGINT	8	-9,223,372,036,854,775,808 (-2^63)	9,223,372,036,854,775,807 (2^63 - 1)	0	18,446,744,073,709,551,615 (2^64 - 1)

• 接下来，博主将以SMALLINT类型为例进行讲解：
  
  

🎧1.2 smallint 类型🎧

1. 首先创建数据库 test1，之后进入此数据库：
   • 输入如下代码：
   • create database if not exists test1;
   • use test1;
2. 创建表 t1，在其中插入一个类型为smallint的id
   • create table if not exists t1(id smallint);
3. 插入一条数据：

• insert into t1 (id) values (100);

4. 插入一个越界数 32768 或者 -32769；

• mysql> insert into t1 (id) values (32768);
• mysql> insert into t1 (id) values (-32769);
• 很明显会报错。

🎧1.3 类比 C/C++ 截断操作🎧

• 如果我们在 C/C++ 中输入下面代码，会发生什么呢：

char ch = 12345;
std::cout << ch << std::endl;

• 首先，因为char类型是 $1Byte$，范围是 -128 ~ 127；
• 所以很明显，$12345$ 超出范围了；
• 但是一般情况下编译器不会直接报错，而是会在编译阶段发生截断操作；
• 这就意味着编译器只会读取 $12345$ 转化成 二进制的低八位：
  • $12345$的二进制： $0011$ $0000$ $0011$ $1001$
  • 截断：$0011$ $1001$ (BIN) -> $57$ (DEC)
• 所以结果就是 $57$ 对应的 ASCII 码值。
• 所以在MySQL中在我们输入的时候就给了限制，这其实是对 程序员的一种约束。

🎧1.4 修改表数据类型🎧

• 如果我们将定义时比较大范围的类型改成比较小的类型会发生什么呢？例如将smallint 改变成 tinyint。

• 这里会直接报错，所以系统会 约束 程序员不要将大范围的类型转换成小范围的类型。

🎧1.5 无符号类型🎧

• 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的；
• 可以通过UNSIGNED来说明某个字段是无符号的；
• 输入下面代码给刚才的 t1 表添加一列：
  • mysql> alter table t1 add column age tinyint unsigned;
  • 注意这里 unsigned 要放在 tinyint 后面。

• 由于无符号的tinyint的范围是 0 ~ 255 ，尝试插入 256，输入以下代码：
  • insert into t1 (id, age) values (1,256);

• 很明显，这也被约束了。

---

2. bit 类型

🎧2.1 bit 类型介绍🎧

• bit(M) : 位字段类型。M表示每个值的位数，范围从1 ~64。如果M被忽略，默认为1。
• 创建表 t2，字段为 id int 和 gender bit，代码如下：

mysql> create table if not exists t2(-> 		id int,-> 		gender bit-> );

• 注意细节，默认bit就是1位
  

🎧2.2 bit 类型详解🎧

• 在表中插入两行：(10, 0)和(20, 1)：

  • 输入代码：
  • mysql> insert into t2 (id, gender) values (10, 0);
  • mysql> insert into t2 (id, gender) values (20, 1);

• 进行查询：

  • 输入语句：mysql> select * from t2;

• 说明：博主这个MySQL是8.0版本之后，如果是在此版本之前，bit类型会显示ASCII码对应的值，但是我这个版本就显示十六进制了。

完整操作代码：

mysql> insert into t2 (id, gender) values (10, 0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 (id, gender) values (20, 1);
Query OK, 1 row affected (0.01 sec)mysql> select * from t2;
+------+----------------+
| id   | gender         |
+------+----------------+
|   10 | 0x00           |
|   20 | 0x01           |
+------+----------------+
2 rows in set (0.00 sec)

---

3. 小数类型

🎧3.1 float类型🎧

• 语法：float[(m, d)] [unsigned]

  • M 指定显示长度；
  • d 指定小数位数，占用空间4个字节。

• 如果是 float(4,2)，那么它的范围就是 -99.99 ~ 99.99，例如：

  • 插入99.99 是可以的：insert into t3 values(100, 99.99);
  • 插入 100.00 是不行的：insert into t3 values(100, 100.00);
  • 但是插入 99.994 也是可以的，但是会被优化掉（四舍五入）：insert into t3 values(100, 99.994);
  • 类似的，插入-99.994是可以的，但是插入-99.995就不行了：
    • insert into t3 values(100, -99.994);
    • insert into t3 values(100, -99.995);

• 完整代码：

mysql> create table t3(-> id int,-> salary float(4,2)-> );
Query OK, 0 rows affected, 1 warning (0.10 sec)mysql> insert into t3 values(100, 99.99);
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 values(100, 100);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t3 values(100, 100.00);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t3 values(100, 99.994);
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 values(100, -99.994);
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values(100, -99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select * from t3;
+------+--------+
| id   | salary |
+------+--------+
|  100 |  99.99 |
|  100 |  99.99 |
|  100 | -99.99 |
+------+--------+
3 rows in set (0.00 sec)

• 默认情况下，浮点数都是带符号的，如果不要带符号的浮点数，例如：float(4,2) unsigned 范围就是 0 ~ 99.99，这里就不尝试了。

🎧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很像，但是有区别！

• 他们表示的精度不一样！

• 例如，创建下面的表：

mysql> create table t4(-> 		id int,-> 		salary1 float(10,8),-> 		salary2 decimal(10,8)-> );

• 插入元素 23.12345612
  • mysql> insert into t4 values(100, 23.12345612, 23.12345612);
• 查看结果：select * from t4;

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

完整代码：

mysql> create table t4(-> id int,-> salary1 float(10,8),-> salary2 decimal(10,8)-> );
Query OK, 0 rows affected, 1 warning (0.05 sec)mysql>  insert into t4 values(100, 23.12345612, 23.12345612);
Query OK, 1 row affected (0.01 sec)mysql> select * from t4;
+------+-------------+-------------+
| id   | salary1     | salary2     |
+------+-------------+-------------+
|  100 | 23.12345695 | 23.12345612 |
+------+-------------+-------------+
1 row in set (0.00 sec)

---

4. 字符串类型

🎧4.1 char类型🎧

• 语法：char(L)

  • 固定长度字符串，L是可以存储的长度；
  • 单位为 字符；
    • 注意这里是字符，不是字节，字符就是你实际存了多少个。
  • 最大长度值可以为255。

• 示例：

mysql> create table t5(-> 		id int,-> 		name char(3)-> );

• 插入元素，下面都正确：

  • insert into t5 values(1,'张三三');
  • insert into t5 values(2,'abc');
  • insert into t5 values(3,'李四');

• 因为存放的是字符，不管是字母还是汉字都是正确的，但是char不能超过255个字符。

🎧4.2 varchar类型🎧

• 语法：varchar(L)
  • 可变长度字符串，
  • L表示字符长度，最大长度65535个字节；
    • 注意这里单位就是字节了！

关于L最大值的讲解：

• 在utf8mb4中，每个字符可以是1~4个字节长度，具体可以看下图：

• 那么L的最大值应该是什么呢？还需要了解下面一点：
• 对于 VARCHAR，存储内容需要额外加上 1~2 字节的长度前缀，用于表示数据的实际字节长度：
  • 如果字段定义的最大长度 ≤ 255，使用 1 字节表示长度；
  • 如果字段定义的最大长度 > 255，使用 2 字节表示长度。
• 因此，MySQL考虑的是最坏的情况：
  • 取出两个字节的长度，假设每个字符都是四个字节，就可以得到下面的结果：
  • $(65535-2)/4=16383$。

• 示例：

mysql> insert into t7 values(100, '我爱你，中国');
Query OK, 1 row affected (0.00 sec)mysql> select * from t7;
+------+--------------------+
| id   | name               |
+------+--------------------+
|  100 | 我爱你，中国         |
+------+--------------------+
1 row in set (0.00 sec)

🎧4.3 char 和 varchar 比较🎧

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

---

5. 日期类型

常用的日期有如下三个：

• date :日期 'yyyy-mm-dd' ，占用三字节；

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

• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节。

• 示例：

//创建表
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 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 | 2017-11-12 18:28:55 | --添加数据时，时间戳自动补上当前时间
+------------+---------------------+---------------------+
//更新数据：
mysql> update birthday set t1='2000-1-1';
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 | 2017-11-12 18:32:09 | -- 更新数据，时间戳会更新成当前时间
+------------+---------------------+---------------------+

---

6. enum 和 set

🎧6.1 enum类型🎧

• enum是枚举类型，学过C的都很熟悉；
• 语法：
• enum('选项1','选项2','选项3',...);

示例：

1. 创建表
2. 插入数据
   • 这里插入数据可以选一个插入；
   • 当然也可以用下标来插入 ，开始的下标是1！

mysql> create table t8 (-> 		gender enum('男','女')-> );
mysql> insert into t8 values('男');
Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values('女');
Query OK, 1 row affected (0.01 sec)// 或者下标：mysql> insert into t8 values(1);
Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values(2);
Query OK, 1 row affected (0.01 sec)

🎧6.2 set 类型🎧

• 语法：set('选项值1','选项值2','选项值3', ...);
• 这是一个集合类型，可以从中选择多个！
• 创建一个表，其中包含爱好的set类型

• 向其中插入元素：

• set中就不可以用下标了，这里是 位图的形式
  • 例如一共有五个爱好，如果想象成5个二进制位，如果对应的位置是你的爱好，此为就是1；
  • 例如这五个全都是我的爱好，那么 $00001$ $1111$ 就是 $31$：

• 如果只喜欢爬山和游泳，那么就是 $0000$ $0011$，转化成十进制就是 $3$：

• 那如果是0会发生什么呢？
  • 这里是空不是没有数据！

🎧6.3 查找🎧

• 现在先看一下表：

mysql> select * from t9;
+--------+---------------------------------------+
| name   | hobby                                 |
+--------+---------------------------------------+
| Alice  | 爬山                                  |
| Alan   | 足球,代码                             |
| Mikael | 爬山,游泳,乒乓球,足球,代码            |
| Peter  | 爬山,游泳                             |
| kk     |                                       |
+--------+---------------------------------------+
5 rows in set (0.00 sec)

• 假如我要查找所有喜欢爬山的人怎么找呢？用到 find_in_set() 函数
• 这个函数表示查找一个元素是否在集合中，返回0为假，非0为真：

mysql> select find_in_set('a','a,b');
+------------------------+
| find_in_set('a','a,b') |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

• 所以就有了下面的用法：
  • where 有点类似于 if 语句

mysql> select * from t9 where find_in_set('爬山',hobby);
+--------+---------------------------------------+
| name   | hobby                                 |
+--------+---------------------------------------+
| Alice  | 爬山                                  |
| Mikael | 爬山,游泳,乒乓球,足球,代码            |
| Peter  | 爬山,游泳                             |
+--------+---------------------------------------+
3 rows in set (0.00 sec)

• 当然，如果想要查找喜欢爬山和游泳的人，可以用and连接两个find_in_set()函数，例如：

mysql> select * from t9 where find_in_set('爬山',hobby) and find_in_set('游泳',hobby);
+--------+---------------------------------------+
| name   | hobby                                 |
+--------+---------------------------------------+
| Mikael | 爬山,游泳,乒乓球,足球,代码            |
| Peter  | 爬山,游泳                             |
+--------+---------------------------------------+
2 rows in set (0.00 sec)