【MySQL】数据类型与表的约束

🌈 个人主页：Zfox_
🔥 系列专栏：MySQL

一：🔥 数据类型

🦋 1.1 数据类型分类

🦋 1.2 数值类型

1.2.1 tinyint类型

📚 数值越界测试：

mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tt1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt1 values(128); -- 越界插入，报错
ERROR 1264 (22003): Out of range value for column 'num' at row 1

mysql> select * from tt1;
+------+
| num  |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

📚 说明:

• 🍡 在 MySQL 中，整型可以指定是有符号的和无符号的，默认是有符号的。
• 🍡 可以通过 UNSIGNED 来说明某个字段是无符号的
• 🍡 无符号案例

mysql> create table tt2(num tinyint unsigned);
mysql> insert into tt2 values(-1); -- 无符号，范围是： 0 - 255
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tt2 values(255);
Query OK, 1 row affected (0.02 sec)

mysql> select * from tt2;
+------+
| num  |
+------+
| 255  |
+------+
1 row in set (0.00 sec)

• 其他类型就不一一讲解了可以自行推导验证

注意：尽量不使用 unsigned，对于 int 类型可能存放不下的数据，int unsigned 同样可能存放不下，与其如此，还不如设计时，将 int 类型提升为 bigint 类型。

1.2.2 bit类型

📚 基本语法：

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

📚 举例：

mysql> create table tt4 ( id int, a bit(8));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt4 values(10, 10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tt4; 	#发现很怪异的现象，a的数据10没有出现
+------+------+
| id   | a    |
+------+------+
| 10   |      |
+------+------+
1 row in set (0.00 sec)

📚 bit使用的注意事项：

• 🍡 bit 字段在显示时，是按照 ASCII码 对应的值显示。

mysql> insert into tt4 values(65, 65);
mysql> select * from tt4;
+------+------+
| id   | a    |
+------+------+
| 10   | 	  |
| 65   | A 	  |
+------+------+

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

mysql> create table tt5(gender bit(1));
mysql> insert into tt5 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt5 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt5 values(2); -- 当插入2时，已经越界了
ERROR 1406 (22001): Data too long for column 'gender' at row 1

1.2.3 小数类型

float

📚 语法：

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

📚 案例：
小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

mysql> create table tt6(id int, salary float(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt6 values(101, -99.991); #多的这一点被拿掉了
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt6;
+------+--------+
| id   | salary |
+------+--------+
| 100  | -99.99 |
| 101  | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

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 表示的精度不一样

mysql> create table tt8 ( id int, salary float(10,8), salary2
decimal(10,8));
mysql> insert into tt8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt8;
+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
| 100  | 23.12345695 | 23.12345612 | # decimal的精度更准确，因此我们如果希望某个数据表示高精度，选择decimal
+------+-------------+-------------+

说明：float表示的精度大约是7位。

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

建议：如果希望小数的精度高，推荐使用decimal。

🦋 1.3 字符串类型

1.3.1 char

📚 语法：

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

📚 案例（char）：

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt9 values(101, '中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt9;
+------+--------+
| id   | name   |
+------+--------+
| 100  | ab     |
| 101  | 中国    |
+------+--------+

📚 说明：
char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255

mysql> create table tt10(id int ,name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

1.3.2 varchar

📚 语法：

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

📚 案例：

mysql> create table tt10(id int ,name varchar(6)); --表示这里可以存放6个字符

mysql> insert into tt10 values(100, 'hello');

mysql> insert into tt10 values(100, '我爱你，中国');

mysql> select * from tt10;
+------+--------------------+
| id   | name 			    |
+------+--------------------+
| 100  | hello				|
| 100  | 我爱你，中国		    |
+------+--------------------+

📚 说明：
关于 varchar(len), len 到底是多大，这个 len 值，和表的编码密切相关：

• varchar 长度可以指定为 0 到 65535 之间的值，但是有 1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
• 当我们的表的编码是 utf8 时，varchar(n) 的参数 n 最大值是 65532 / 3 = 21844 [因为 utf8 中，一个字符占用 3 个字节]，如果编码是 gbk，varchar(n) 的参数 n 最大是 65532 / 2 = 32766（因为 gbk 中，一个字符占用 2 字节）。

mysql> create table tt11(name varchar(21845))charset=utf8; --验证了utf8确实是不能超过21844

ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

mysql> create table tt11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

1.3.3 char 和 varchar比较

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

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

🦋 1.4 日期和时间类型

常用的日期有如下三个：

• 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 | -- 更新数据，时间戳会更新成当前时间
+------------+---------------------+---------------------+

🦋 1.5 enum和set

📚 语法：

• enum：枚举，“单选”类型；
• enum (‘选项1’,‘选项2’,‘选项3’,…);

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

• set：集合，“多选”类型；
• set (‘选项值1’,‘选项值2’,‘选项值3’, …);

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

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读

📚 案例：
有一个调查表votes，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]

mysql> create table votes(
-> username varchar(30),
-> hobby set('登山','游泳','篮球','武术'), --注意：使用数字标识每个爱好的时候，想想Linux权限，采用比特位位置来个set中的爱好对应起来

-> gender enum('男','女')); --注意：使用数字标识的时候，就是正常的数组下标
Query OK, 0 rows affected (0.02 sec)

📚 插入数据：

insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2);
select * from votes where gender=2;
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| Juse     | 登山,武术      |女       |
+----------+---------------+--------+

有如下数据，想查找所有喜欢登山的人：

+-----------+---------------+--------+
| username  | hobby 	    | gender |
+-----------+---------------+--------+
| 雷锋       | 登山,武术      | 男 	 |
| Juse	    | 登山,武术 	    | 女	 |
| LiLei 	| 登山 		    | 男 	 |
| LiLei	    | 篮球 		    | 男 	 |
| HanMeiMei | 游泳  			| 女 	 |
+-----------+---------------+--------+

使用如下查询语句：

mysql> select * from votes where hobby='登山';
+----------+--------+--------+
| username | hobby  | gender |
+----------+--------+--------+
| LiLei    | 登山    | 男     |
+----------+--------+--------+

不能查询出所有，爱好为登山的人。

集合查询使用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 					    |
+---------------------------+

mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
| 0 					    |
+---------------------------+

查询爱好登山的人：

mysql> select * from votes where find_in_set('登山', hobby);
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| 雷锋      | 登山, 武术     | 男     |
| Juse     | 登山, 武术      | 女    |
| LiLei    | 登山           | 男     |
+----------+---------------+--------+

二：🔥 表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。

表的约束很多，这里主要介绍如下几个： null/not null , default, comment, zerofill，primary key，auto_increment，unique key。

🦋 2.1 空属性

• 两个值：null（默认的）和not null(不为空)
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 1+null;
+--------+
| 1+null |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

📚 案例：
创建一个班级表，包含班级名和班级所在的教室。
站在正常的业务逻辑中：

• 如果班级没有名字，你不知道你在哪个班级
• 如果教室名字可以为空，就不知道在哪上课
  所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“约束”。

mysql> create table myclass(
	-> class_name varchar(20) not null,
	-> class_room varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
//插入数据时,没有给教室数据插入失败：

mysql> insert into myclass(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

🦋 2.2 默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，
用户可以选择性的使用默认值。

mysql> create table tt10 (
	-> name varchar(20) not null,
	-> age tinyint unsigned default 0,
	-> sex char(2) default '男'
	-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc tt10;
+-------+---------------------+------+-----+---------+-------+
| Field | Type				  | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20) 		  | NO   | 	   | NULL    | 		 |
| age   | tinyint(3) unsigned | YES  |     | 0 	     | 		 |
| sex   | char(2) 			  | YES  | 	   | 男      | 		 |
+-------+---------------------+------+-----+---------+-------+

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值

mysql> insert into tt10(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt10;
+----------+------+------+
| name     | age  | sex  |
+----------+------+------+
| zhangsan | 0    | 男   |
+----------+------+------+

--注意：只有设置了default的列，才可以在插入值的时候，对列进行省略

🦋 2.3 列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

mysql> create table tt12 (
	-> name varchar(20) not null comment '姓名',
	-> age tinyint unsigned default 0 comment '年龄',
	-> sex char(2) default '男' comment '性别'
	-> );
	
--注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

通过desc查看不到注释信息：

mysql> desc tt12;
+-------+---------------------+------+-----+---------+-------+
| Field | Type 				  | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20) 		  | NO   |     | NULL	 | 		 |
| age   | tinyint(3) unsigned | YES  | 	   | 0 		 | 		 |
| sex   | char(2) 			  | YES  | 	   | 男 	 | 		 |
+-------+---------------------+------+-----+---------+-------+

通过show可以看到：

mysql> show create table tt12\G
*************************** 1. row ***************************
		Table: tt12
Create Table: CREATE TABLE `tt12` (
	`name` varchar(20) NOT NULL COMMENT '姓名',
	`age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
	`sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

🦋 2.4 zerofill

刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。通过show看看tt3表的建表语句：

mysql> show create table tt3\G
***************** 1. row *****************
		Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的。a和b列就是前面插入的数据，如下：

mysql> insert into tt3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
	+------+------+
	| a    | b    |
	+------+------+
	| 1    | 2    |
	+------+------+

但是对列添加了zerofill属性后，显示的结果就有所不同了。修改tt3表的属性：

mysql> alter table tt3 change a a int(5) unsigned zerofill;
mysql> show create table tt3\G
*************************** 1. row ***************************
		Table: tt3
Create Table: CREATE TABLE `tt3` (
	`a` int(5) unsigned zerofill DEFAULT NULL, --具有了zerofill
	`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

对a列添加了zerofill属性，再进行查找，返回如下结果：

mysql> select * from tt3;
+-------+------+
| a     | b    |
+-------+------+
| 00001 | 2    |
+-------+------+

这次可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。为什么是这样呢？我们可以用hex函数来证明。

mysql> select a, hex(a) from tt3;
+-------+--------+
| a     | hex(a) |
+-------+--------+
| 00001 | 1      |
+-------+--------+

可以看出数据库内部存储的还是1,00001只是设置了zerofill属性后的一种格式化输出而已。

🦋 2.5 主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个
主键；主键所在的列通常是整数类型。

📚 案例：

• 创建表的时候直接在字段上指定主键

mysql> create table tt13 (
-> id int unsigned primary key comment '学号不能为空',
-> name varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc tt13;
+-------+------------------+------+-----+---------+-------+
| Field | Type 			   | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL 	  | 	  | <= key 中 pri表示该字段是主键
| name  | varchar(20) 	   | NO   | 	| NULL    | 	  |
+-------+------------------+------+-----+---------+-------+

主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

mysql> insert into tt13 values(1, 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt13 values(1, 'aaa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

• 当表创建好以后但是没有主键的时候，可以再次追加主键

alter table 表名 add primary key(字段列表)

• 删除主键

alter table 表名 drop primary key;

mysql> alter table tt13 drop primary key;
mysql> desc tt13;
+-------+------------------+------+-----+---------+-------+
| Field | Type 			   | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | 	| NULL    |	 	  |
| name  | varchar(20)	   | NO   | 	| NULL 	  | 	  |
+-------+------------------+------+-----+---------+-------+

• 复合主键
  在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type 			   | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | 0 	  |       | <= 这两列合成主键
| course | char(10)     	   | NO   | PRI | 		  |       |
| score  | tinyint(3) unsigned | YES  |     | 60      |       |
+--------+---------------------+------+-----+---------+-------+

mysql> insert into tt14 (id,course)values(1, '123');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tt14 (id,course)values(1, '123');
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 主键冲突

🦋 2.6 自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

📚 案例：

mysql> create table tt21(
	-> id int unsigned primary key auto_increment,
	-> name varchar(10) not null default ''
	-> );

mysql> insert into tt21(name) values('a');

mysql> insert into tt21(name) values('b');

mysql> select * from tt21;
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
+----+------+

在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

mysql > select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1				   |
+------------------+

📚 索引：

• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
• 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

🦋 2.7 唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别：

我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。乍一听好像没啥区别，我们举一个例子

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)
比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一
个是员工工号，我们可以选择身份号码作为主键。

而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。

具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯
一键。

一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对
主键做过大的调整。

📚 案例：

mysql> create table student (
		-> id char(10) unique comment '学号，不能重复，但可以为空',
	-> name varchar(10)
	-> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(id, name) values('01', 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| 01   | aaa  |
| NULL | bbb  |
+------+------+

🦋 2.8 外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique
约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

📚 语法：

foreign key (字段名) references 主表(列)

📚 案例：

对上面的示意图进行设计：

• 先创建主键表

create table myclass (
		id int primary key,
		name varchar(30) not null comment'班级名'
	);

• 再创建从表

create table stu (
		id int primary key,
		name varchar(30) not null comment '学生名',
		class_id int,
		foreign key (class_id) references myclass(id)
	);

• 正常插入数据

mysql> insert into myclass values(10, 'C++大牛班'),(20, 'java大神班');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

• 插入一个班级号为30的学生，因为没有这个班级，所以插入不成功

mysql> insert into stu values(102, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (mytest.stu, CONSTRAINT stu_ibfk_1
FOREIGN KEY (class_id) REFERENCES myclass (id))

• 插入班级id为null，比如来了一个学生，目前还没有分配班级

mysql> insert into stu values(102, 'wangwu', null);

• 如何理解外键约束

首先我们承认，这个世界是数据很多都是相关性的。

理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。

此时，在实际使用的时候，可能会出现什么问题？有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？比如比特只开了比特100班，比特101班，但是在上课的学生里面竟然有比特102班的学生(这个班目前并不存在)，这很明显是有问题的。

因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉 mysql 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

🦋 2.9 综合案例 - 阅读

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

• 商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
• 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
• 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

📚 要求：

• 每个表的主外键
• 客户的姓名不能为空值
• 邮箱不能重复
• 客户的性别(男，女)

📚 SQL:

-- 创建数据库
create database if not exists bit32mall
default character set utf8 ;

-- 选择数据库
use bit32mall;

-- 创建数据库表
-- 商品
create table if not exists goods
(
	goods_id int primary key auto_increment comment '商品编号',
	goods_name varchar(32) not null comment '商品名称',
	unitprice int not null default 0 comment '单价，单位分',
	category varchar(12) comment '商品分类',
	provider varchar(64) not null comment '供应商名称'
);

-- 客户
create table if not exists customer
(
	customer_id int primary key auto_increment comment '客户编号',
	name varchar(32) not null comment '客户姓名',
	address varchar(256) comment '客户地址',
	email varchar(64) unique key comment '电子邮箱',
	sex enum('男','女') not null comment '性别',
	card_id char(18) unique key comment '身份证'
);

-- 购买
create table if not exists purchase
(
	order_id int primary key auto_increment comment '订单号',
	customer_id int comment '客户编号',
	goods_id int comment '商品编号',
	nums int default 0 comment '购买数量',
	foreign key (customer_id) references customer(customer_id),
	foreign key (goods_id) references goods(goods_id)
);

三：🔥 共勉

以上就是我对 【MySQL】数据类型与表的约束 的理解，觉得这篇博客对你有帮助的，可以点赞收藏关注支持一波~😉