MySQL外键

https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html

外键基本特性

外键通常关联到另一个表的主键列，这样它就能确保引用的数据是一致的。

与SQL标准对比

https://dev.mysql.com/doc/refman/8.4/en/ansi-diff-foreign-keys.html

MySQL外键问题

https://www.bilibili.com/video/BV1M1Koz6EW1

参考MySQL官方Bug: https://bugs.mysql.com/bug.php?id=94148, 该Bug基于版本5.7.24，这里我用MySQL8演示

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.08 sec)

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.08 sec)

mysql> SET autocommit = 0;

现有parent, child这两张表，建表DDL如下：

drop table if exists child;
drop table if exists parent;

create table parent (
  parent_id int unsigned not null,
  primary key (parent_id)
);

create table child (
  child_id int unsigned not null,
  parent_id int unsigned not null,
  bs_column int unsigned not null,
  primary key (child_id),
  key (parent_id,bs_column),
  constraint fk_parent_id foreign key (parent_id) 
    references parent (parent_id) on update cascade on delete cascade
);

insert into parent set parent_id=1;
insert into child set child_id=1,parent_id=1,bs_column=0;
insert into child set child_id=2,parent_id=1,bs_column=0;

现更新child表与外键无关的字段 bs_column：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update child set bs_column = bs_column + 1 where child_id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时查看锁状态，发现 parent 表被锁了

mysql> SELECT ENGINE_TRANSACTION_ID as Trx_Id,
    OBJECT_NAME as `Table`,
    INDEX_NAME as `Index`,
    LOCK_DATA as Data,
    LOCK_MODE as Mode,
    LOCK_STATUS as Status,
    LOCK_TYPE as Type
FROM performance_schema.data_locks;
+----------+--------+---------+------+---------------+---------+--------+
| Trx_Id   | Table  | Index   | Data | Mode          | Status  | Type   |
+----------+--------+---------+------+---------------+---------+--------+
| 12720929 | parent | NULL    | NULL | IS            | GRANTED | TABLE  |
| 12720929 | child  | NULL    | NULL | IX            | GRANTED | TABLE  |
| 12720929 | child  | PRIMARY | 1    | X,REC_NOT_GAP | GRANTED | RECORD |
| 12720929 | parent | PRIMARY | 1    | S,REC_NOT_GAP | GRANTED | RECORD |
+----------+--------+---------+------+---------------+---------+--------+
4 rows in set (0.11 sec)

还有一个有趣的现象：

在 child 表的 parent_id 上建一个索引

mysql> DROP TABLE if exists child ;
mysql> create table child (
    child_id int unsigned not null,
    parent_id int unsigned not null,
    bs_column int unsigned not null,
    primary key (child_id),
    key (parent_id),
    key (parent_id,bs_column),
    constraint fk_parent_id foreign key (parent_id) 
        references parent (parent_id) on update cascade on delete cascade
);
mysql> insert into child set child_id=1,parent_id=1,bs_column=0;
mysql> insert into child set child_id=2,parent_id=1,bs_column=0;

重新执行：update child set bs_column = bs_column + 1 where child_id = 1; 此时没有锁住parent表

mysql> SELECT ENGINE_TRANSACTION_ID as Trx_Id,
    OBJECT_NAME as `Table`,
    INDEX_NAME as `Index`,
    LOCK_DATA as Data,
    LOCK_MODE as Mode,
    LOCK_STATUS as Status,
    LOCK_TYPE as Type
FROM performance_schema.data_locks;
+----------+-------+---------+------+---------------+---------+--------+
| Trx_Id   | Table | Index   | Data | Mode          | Status  | Type   |
+----------+-------+---------+------+---------------+---------+--------+
| 12720955 | child | NULL    | NULL | IX            | GRANTED | TABLE  |
| 12720955 | child | PRIMARY | 1    | X,REC_NOT_GAP | GRANTED | RECORD |
+----------+-------+---------+------+---------------+---------+--------+
2 rows in set (0.04 sec)

参考资料：

1. https://dba.stackexchange.com/questions/191510/why-adding-foreign-key-constraint-does-not-lock-the-table
2. https://www.reddit.com/r/mysql/comments/vqnrps/why_does_deleting_a_record_from_a_foreign_keyed/