第7章：MySQL之测试数据
第7章：MySQL之测试数据
目录
修订日期：2021-08-13
一. Employees数据库安装
1. Employees数据库介绍
Employees数据库是一个用于学习和测试的数据库，大约160MB，4百万条记录
2. Employees的安装
2.1. 下载
根据官方文档的连接，我们可以找到下载该数据库的两种方式
-
employees_db-full-1.0.6.tar.bz2 使用
wget下载 -
github-test_db 使用
git clone进行仓库拉取
2.2. 解压和拉取
[root@midou01 /soft]# git clone https://github.com/datacharmer/test_db.git
正克隆到 'test_db'...
remote: Counting objects: 98, done.
remote: Compressing objects: 100% (4/4), done.
remote: Total 98 (delta 0), reused 0 (delta 0), pack-reused 94
Unpacking objects: 100% (98/98), done.
[root@midou01 /soft]# ls -lh test_db
total 165M
-rw-r--r-- 1 root root 964 12月 6 16:48 Changelog
-rw-r--r-- 1 root root 7.8K 12月 6 16:48 employees_partitioned_5.1.sql
-rw-r--r-- 1 root root 6.2K 12月 6 16:48 employees_partitioned.sql
-rw-r--r-- 1 root root 4.1K 12月 6 16:48 employees.sql
drwxr-xr-x 2 root root 4.0K 12月 6 16:48 images
-rw-r--r-- 1 root root 250 12月 6 16:48 load_departments.dump
-rw-r--r-- 1 root root 14M 12月 6 16:48 load_dept_emp.dump
-rw-r--r-- 1 root root 1.1K 12月 6 16:48 load_dept_manager.dump
-rw-r--r-- 1 root root 17M 12月 6 16:48 load_employees.dump
-rw-r--r-- 1 root root 38M 12月 6 16:48 load_salaries1.dump
-rw-r--r-- 1 root root 38M 12月 6 16:48 load_salaries2.dump
-rw-r--r-- 1 root root 38M 12月 6 16:48 load_salaries3.dump
-rw-r--r-- 1 root root 21M 12月 6 16:48 load_titles.dump
-rw-r--r-- 1 root root 4.5K 12月 6 16:48 objects.sql
-rw-r--r-- 1 root root 4.0K 12月 6 16:48 README.md
drwxr-xr-x 2 root root 4.0K 12月 6 16:48 sakila
-rw-r--r-- 1 root root 272 12月 6 16:48 show_elapsed.sql
-rwxr-xr-x 1 root root 1.8K 12月 6 16:48 sql_test.sh
-rw-r--r-- 1 root root 4.8K 12月 6 16:48 test_employees_md5.sql
-rw-r--r-- 1 root root 4.8K 12月 6 16:48 test_employees_sha.sql
2.3. 导入employees库
[root@midou01 /soft]# cd test_db/
[root@midou01 /soft/test_db]# mysql -uroot -p < /soft/test_db/employees.sql
Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:49
2.4. 验证
[root@midou01 /soft/test_db]# time mysql -uroot -p -t < /soft/test_db/test_employees_sha.sql
Enter password:
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+------------------------------------------+
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+------------------------------------------+
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| employees | OK | ok |
| departments | OK | ok |
| dept_manager | OK | ok |
| dept_emp | OK | ok |
| titles | OK | ok |
| salaries | OK | ok |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:09 |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC | OK |
| count | OK |
+---------+--------+
real 0m11.340s
user 0m0.007s
sys 0m0.003s
[root@midou01 /soft/test_db]# time mysql -uroot -p -t < /soft/test_db/test_employees_md5.sql
Enter password:
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+----------------------------------+
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+----------------------------------+
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| employees | OK | ok |
| departments | OK | ok |
| dept_manager | OK | ok |
| dept_emp | OK | ok |
| titles | OK | ok |
| salaries | OK | ok |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:08 |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC | OK |
| count | OK |
+---------+--------+
real 0m10.811s
user 0m0.006s
sys 0m0.003s
至此，Employees测试数据库就安装完成了
二. 表(TABLE)
1. 表的介绍
- 表是关系数据库的核心
- 表 = 关系
- 表是记录的集合
- 二维表格模型易于人的理解
- MySQL默认存储引擎都是基于行(记录)存储
- 每行记录都是基于列进行组织的
1.1 MySQL中数据库与表之间的关系
graph TD
A[MySQL实例] --> B[Schema库]
B --> C[Table表1]
B --> D[Table表2]
B --> E[Table表3]
2. 表是数据的集合
select * from table_name limit 1;
集合是无序的，上面的SQL语句的意思是 从表(集合)中随机选出一条数据，结果是不确定的, 不能简单的认为是取出第一条数据。
select * from table_name order by col_name limit 1;
只有通过order by排序之后取出的数据，才是确定的。
3. 创建表
3.1. 临时表
-
临时表的创建
-
临时表只能
单独会话中存在
--
-- 终端A
--
(root@localhost) 17:39:38 [employees]> create database mytest;
Query OK, 1 row affected (0.00 sec)
(root@localhost) 17:39:42 [employees]> use mytest;
Database changed
(root@localhost) 17:43:53 [mytest]> create temporary table t1 (a int);
Query OK, 0 rows affected (0.00 sec)
(root@localhost) 17:44:27 [mytest]> insert into t1 select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
(root@localhost) 17:44:30 [mytest]> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
(root@localhost) 17:44:49 [mytest]> show create table t1;
+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------+
| t1 | CREATE TEMPORARY TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--
-- 终端B
--
(gcdb@localhost) 17:52:23 [(none)]> show full processlist;
+-------+------+-----------+--------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+--------+---------+------+----------+-----------------------+
| 9253 | root | localhost | mytest | Sleep | 3 | | NULL |
| 33348 | gcdb | localhost | NULL | Query | 0 | starting | show full processlist |
+-------+------+-----------+--------+---------+------+----------+-----------------------+
2 rows in set (0.01 sec)
(gcdb@localhost) 17:52:37 [(none)]> use mytest;
Database changed
(gcdb@localhost) 17:53:04 [mytest]> show tables;
Empty set (0.00 sec)
(gcdb@localhost) 17:53:09 [mytest]>
Empty set (0.00 sec) -- 从其他终端登录的用户(session)无法看到临时表t1
临时表和普通表同名问题
mysql> create table t_1 (a int); -- 创建一张普通的表叫做 t_1
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t_1 values(3);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t_1 values(4);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t_1;
+------+
| a |
+------+
| 3 | -- 可以看到插入的数据
| 4 |
+------+
2 rows in set (0.00 sec)
mysql> create temporary table t_1 (a int); -- 创建一种和t_1 同名的临时表
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_1 values(1000); -- 插入一个 不一样的值
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_1;
+------+
| a |
+------+
| 1000 | -- 只能搜索到临时表中的数据
+------+
1 row in set (0.00 sec)
mysql> create temporary table if not exists table_name (a int); -- 使用if not exists进行判断
1：临时表是
SESSION级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的
2：当临时表和普通表同名时，当前用户只能看到同名的临时表
3：创建表时带上if not exists进行表的存在性检查；同时建议在临时表的表名前面加上统一的prefix
-
临时表的作用
- 临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。
- 不要和SQL优化器在排序过程中内部帮你创建的临时表相混淆。
-
临时表的存储引擎
(root@localhost) 18:01:11 [mytest]> show variables like "default%tmp%";
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB | -- 临时表默认存储引擎就是InnoDB
+----------------------------+--------+
1 row in set (0.00 sec)
- 临时表存储位置
(root@localhost) 18:06:30 [mytest]> system ls /r2/mysqldata/ |grep '#'
#sql29ca9_2425_0.frm --临时表保存的表结构
(root@localhost) 18:09:34 [mytest]> system ls -lh /r2/mysqldata/ |grep 'tmp'
-rw-r----- 1 mysql mysql 12M 12月 6 18:09 ibtmp1 -- 这个是我们的表结构对应的数据
(root@localhost) 18:09:42 [mytest]> show variables like "innodb_temp%";
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
MySQL5.7.18 把临时
表结构放在tmpdir，而数据表数据放在datadir
4. 查看表结构
(root@localhost) 18:13:27 [mytest]> show create table t_1\G; --显示表结构
*************************** 1. row ***************************
Table: t_1
Create Table: CREATE TABLE `t_1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
ERROR:
No query specified
(root@localhost) 18:14:18 [mytest]> desc t_1\G; ---- 表的描述,描述二维表信息
*************************** 1. row ***************************
Field: a
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
1 row in set (0.00 sec)
ERROR:
No query specified
(root@localhost) 18:14:29 [mytest]> show table status like 't_1'\G; -- 看表结构的元数据信息
*************************** 1. row ***************************
Name: t_1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-12-06 18:12:55
Update_time: 2017-12-06 18:13:17
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
5. ALTER TABLE
(root@localhost) 09:01:26 [mytest]> select * from t_1;
+------+
| a |
+------+
| 3 |
| 4 |
+------+
2 rows in set (0.00 sec)
(root@localhost) 09:01:37 [mytest]> alter table t_1 add column b char(10); --添加列
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) 09:02:08 [mytest]> select * from t_1;
+------+------+
| a | b |
+------+------+
| 3 | NULL |
| 4 | NULL |
+------+------+
2 rows in set (0.00 sec)
(root@localhost) 09:02:45 [mytest]> update t_1 set b=23 where a =3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost) 09:02:51 [mytest]> update t_1 set b=24 where a =4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost) 09:02:58 [mytest]> select * from t_1;
+------+------+
| a | b |
+------+------+
| 3 | 23 |
| 4 | 24 |
+------+------+
2 rows in set (0.00 sec)
(root@localhost) 09:03:10 [mytest]> alter table t_1 drop column b; --删除列
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) 09:03:36 [mytest]> select * from t_1;
+------+
| a |
+------+
| 3 |
| 4 |
+------+
2 rows in set (0.00 sec)
(root@localhost) 09:03:38 [mytest]>
注意:
当表记录很大的时候，alter table会很耗时，影响性能
(root@localhost) 10:07:22 [mytest]> use employees;
Database changed
(root@localhost) 10:07:46 [employees]> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
(root@localhost) 10:07:50 [employees]> select count(*) from salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.51 sec)
(root@localhost) 10:11:02 [employees]> create table newsal like salaries;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) 10:13:25 [employees]> insert into newsal select * from salaries;
Query OK, 2844047 rows affected (17.20 sec)
Records: 2844047 Duplicates: 0 Warnings: 0
(root@localhost) 10:16:03 [employees]> show create table newsal\G;
*************************** 1. row ***************************
Table: newsal
Create Table: CREATE TABLE `newsal` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
(root@localhost) 10:19:29 [employees]> alter table newsal add column (update_data date);
Query OK, 0 rows affected (29.05 sec) --2844047行数据做ONLINE DDL花费的时间，并且会锁表，线上操作要谨慎。
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) 10:20:27 [employees]> show create table newsal \G;
*************************** 1. row ***************************
Table: newsal
Create Table: CREATE TABLE `newsal` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`update_data` date DEFAULT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
(root@localhost) 10:20:45 [employees]> alter table newsal drop column update_data;
Query OK, 0 rows affected (26.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
- ONLINE DDL
- 5.6以后对在线DDL操作进行了优化，以提高性能。官方文档
三. 外键约束
1. 外键的介绍
--
-- 摘自 MySQL官方文档
--
CREATE TABLE product ( -- 商品表
category INT NOT NULL, -- 商品种类
id INT NOT NULL, -- 商品id
price DECIMAL,
PRIMARY KEY(category, id) -- 主键是 (category, id)
) ENGINE=INNODB;
CREATE TABLE customer ( -- 客户表
id INT NOT NULL, -- 客户id
PRIMARY KEY (id) -- 主键是 id
) ENGINE=INNODB;
CREATE TABLE product_order ( -- 订单表
no INT NOT NULL AUTO_INCREMENT, -- number，自增长
product_category INT NOT NULL, -- 商品种类
product_id INT NOT NULL, -- 商品id
customer_id INT NOT NULL, -- 客户id
PRIMARY KEY(no), -- 主键是 no
INDEX (product_category, product_id), -- 对 (product_category, product_id) 做索引
INDEX (customer_id), -- 对 customer_id 做索引
FOREIGN KEY (product_category, product_id) -- 两个外键约束
REFERENCES product(category, id) -- 字段 product_category 引用自 product表的category
-- 字段 product_id 引用自 product表的id
ON UPDATE CASCADE ON DELETE RESTRICT, -- 级联跟新 和 严格模式删除
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;
2. 外键操作
--
-- 表结构 摘自 MySQL 官方文档
--
mysql> create table parent (
-> id int not null,
-> primary key (id)
-> ) engine=innodb;
Query OK, 0 rows affected (0.14 sec)
mysql> create table child (
-> id int,
-> parent_id INT,
-> index par_ind (parent_id),
-> foreign key (parent_id)
-> references parent(id)
-> on delete cascade on update cascade -- update cascade级联更新
-> ) engine=innodb;
Query ok, 0 rows affected (0.15 sec)
mysql> insert into child values(1,1); -- 我们插入一条数据，id=1，parent_id=1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)
-- 直接报错了，因为此时parent表中没有任何记录
mysql> insert into parent values(1); -- 现在parent中插入记录
Query OK, 1 row affected (0.03 sec)
mysql> insert into child values(1,1); -- 然后在child中插入记录，且parent_id是在parent中存在的
Query OK, 1 row affected (0.02 sec)
mysql> insert into child values(1,2); -- 插入parent_id=2的记录，报错。因为此时parent_id=2的记录不存在
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 | -- parent_id = 1
+------+-----------+
1 row in set (0.00 sec)
mysql> select * from parent;
+----+
| id |
+----+
| 1 | -- 根据表结构的定义（Foreign_key），这个值就是 child表中的id
+----+
1 row in set (0.00 sec)
mysql> update parent set id=100 where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from parent;
+-----+
| id |
+-----+
| 100 | -- 已经设置成了100
+-----+
1 row in set (0.00 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 100 | -- 自动变化，这是on update cascade的作用，联级更新，parent更新，child也跟着更新
+------+-----------+
1 row in set (0.00 sec)
mysql> delete from parent where id=100; -- 删除这条记录
Query OK, 1 row affected (0.03 sec)
mysql> select * from parent; -- id=100的记录已经被删除了
Empty set (0.00 sec)
mysql> select * from child; -- id=1，parent_id=100的记录跟着被删除了。on delete cascade的作用
Empty set (0.00 sec)
mysql> alter table child drop foreign key child_ibfk_1; -- 删除 之前的外键
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table child add foreign key(parent_id)
-> references parent(id) on update cascade on delete restrict; -- 使用严格模式
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into parent values(50);
Query OK, 1 row affected (0.03 sec)
mysql> insert into child values(3,50);
Query OK, 1 row affected (0.03 sec)
mysql> insert into child values(3,51); -- 和之前一样会提示错误
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)
mysql> delete from parent where id=50; -- 删除失败了，因为是restrict模式
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)
-- 注意，delete 后面说明都不写表示 no action == restrict
-
cascade方式
- 在父表上update/delete记录时，
同步update/delete掉子表的匹配记录
- 在父表上update/delete记录时，
-
set null方式
- 在父表上update/delete记录时，将子表上匹配记录的
列设为null - 要注意子表的
外键列不能为not null
- 在父表上update/delete记录时，将子表上匹配记录的
-
No action方式
- 如果子表中有匹配的记录,则
不允许对父表对应候选键进行update/delete操作
- 如果子表中有匹配的记录,则
-
Restrict方式
- 同no action, 都是立即检查外键约束
-
Set default方式
- 父表有变更时,子表将外键列设置成一个
默认的值但Innodb不能识别
- 父表有变更时,子表将外键列设置成一个
外键约束，可以让数据进行一致性更新，但是会有一定的
性能损耗，线上业务使用不多
通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。
