当前位置 : 首页 » 文章分类 :  开发  »  MySQL-外键

MySQL-外键

MySQL外键笔记


mysql外键

在MySQL 3.23版本后,InnoDB引擎类型的表支持了外键约束,外键主要用于引用和参照完整性的约束检查。外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作。但是外键的使用是有一些必要条件的:

  1. 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持);

  2. 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显式建立;

  3. 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以;

在InnoDB存储引擎中,对于一个外键列,如果没有显示地对这个列加索引,InnoDB存储引擎会自动对其加一个索引,因为这样可以避免表锁。这比Oracle数据库做得好,Oracle数据库不会自动添加索引,用户必须自己手动添加,这也导致了Oracle数据库中可能产生死锁。

外键的定义语法:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在CREATE TABLE和ALTER TABLE时使用,如果不指定CONSTRAINT symbol,MySQL会自动生成一个名字。对于ON DELETE、ON UPDATE表示事件触发限制。

一般来说,称被引用的表为父表,引用的表为子表。外键定义时ON DELETE和ON UPDATE表示在对父表进行DELETE或UPDATE操作时,对子表所做的操作,可定义的子表操作有:

CASCADE(跟随外键改动):表示当父表发送DELETE或UPDATE操作时,对相应的子表中的数据也进行DELETE和UPDATE操作。
SET NULL(设空值):表示当父表发送DELETE或UPDATE操作时,对相应的子表中的数据更新为NULL值,但子表对应的列必须允许为NULL。
SET DEFAULT(设默认值):表示当父表发送DELETE或UPDATE操作时,对相应的子表中的数据更新为默认值。
NO ACTION(无动作,默认的):表示当父表发送DELETE或UPDATE操作时,抛出错误,不允许这类操作发生。
RESTRICT(限制外表中的外键改动):表示当父表发送DELETE或UPDATE操作时,抛出错误,不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE,RESTRICT就是默认的外键设置。

在其他数据库中,如Oracle数据库中,有一种称为延时检查(deferred check)的外键约束,即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查,因此从上面的定义可以看出,在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。

对于参照完整性约束,外键能起到一个非常好的作用。但是对于数据的导入操作时,外键往往导致在外键约束的检查上花费大量时间。因此MySQL数据库的外键是即时检查的,所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查,如:

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA ....

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

对于外键值的插入或更新,首先需要检查父表中的记录,既SELECT父表。但是对于父表的SELECT操作,不是使用一致性非锁定读的方式,因为这会发生数据不一致的问题,因此这时使用的是SELECT…LOCK IN SHARE MODE方式,即主动对父表加一个S锁。如果这时父表上已经这样加X锁,子表上的操作会被阻塞,如下:

先创建测试环境,如下:

# 创建parent表;
create table parent(
  tag_id int primary key auto_increment not null,
  tag_name varchar(20)
);

# 创建child表;
create table child(
  article_id int primary key auto_increment not null,
  article_tag int(11),
  CONSTRAINT  tag_at FOREIGN KEY (article_tag) REFERENCES parent(tag_id)
);

# 插入数据;
insert into parent(tag_name) values('mysql');
insert into parent(tag_name) values('oracle');
insert into parent(tag_name) values('mariadb');

开始测试

# Session A
mysql> begin
mysql> delete from parent where tag_id = 3;

# Session B
mysql> begin
mysql> insert into child(article_id, article_tag) values(1,3);   #阻塞

Session B 执行insert语句时被阻塞。当Session A手动执行commit提交事务后,Session B才能继续。

在上述的例子中,两个会话中的事务都没有进行COMMIT或ROLLBACK操作,而会话B的操作会被阻塞。这是因为tag_id为3的父表在会话中已经加了一个X锁,而此时在会话B中用户又需要对父表中tag_id为3的行加一个S锁,这时INSERT的操作会被阻塞。设想如果访问父表时,使用的是一致性的非锁定读,这时Session B会读到父表有tag_id=3的记录,可以进行插入操作。但是如果会话A对事务提交了,则父表中就不存在tag_id为3的记录。数据在父、子表就会存在不一致的情况。

MySQL InnoDB外键约束详解 - 运维那点儿事 质量很高的文章
http://www.ywnds.com/?p=9135


外键与锁

在mysql的多个存储引擎中,innodb支持外键,但是由于外键,也会对innodb表增加锁定机制;

所有的外键相关的操作都在数据更改时,比如检查数据完整性、增加锁定等;

假设一个表为 parent ,一个表为child,child表有外键pid REFERENCES parent(id);在一个session中set autocommit=0,执行对parent或child的操作,在另一个
session执行对child或parent的操作,从而得出以下结论:

一、对父表的操作
1、insert to parent ,新插入行的id值为XXX
child会锁外键值为XXX的行,不会锁其他行

2、update parent ,原id为XXX,现id为YYY
child会锁外键值为XXX,YYY的行,不会锁其他行

3、delete from parent ,删除行的id为XXX
child会锁外键值为XXX,不会锁其他行

二、对子表的操作
1、insert to child,插入行的外键值为XXX
parent 会锁值XXX的行,不会锁其他行

2、update child,更新行的外键值原为XXX,现为YYY
parent 会锁XXX,YYY行,但要注意:存在间隔锁,也会锁其他行(XXX,YYY之间的位置)

3、delete from child,删除行的外键值为XXX
parent 会锁XXX的行,但要注意:存在间隔锁,也会锁其他行(XXX-1的位置)

mysql 外键锁机制
https://blog.csdn.net/lan12334321234/article/details/70049370


InnoDB会自动为外键创建索引

下面是 mysql(mariadb) 中的一个建表语句,原始sql如下:

-- 用户地址表, 常用地址和收货地址
DROP TABLE IF EXISTS user_address;
CREATE TABLE user_address (
  id               BIGINT                        NOT NULL AUTO_INCREMENT,
  app_id           INT                           NOT NULL,
  user_id          BIGINT                        NOT NULL,
  status           ENUM ('enable', 'disable')    NOT NULL DEFAULT 'enable'
  COMMENT '状态',
  province_id      BIGINT
  COMMENT '省份id',
  province         VARCHAR(64)
  COMMENT '省份',
  city_id          BIGINT
  COMMENT '城市id',
  city             VARCHAR(64)
  COMMENT '城市',
  region_id        BIGINT
  COMMENT '区县id',
  region           VARCHAR(64)
  COMMENT '区县',
  detailed_address VARCHAR(512)                  NOT NULL
  COMMENT '详细地址',
  type             ENUM ('usual', 'deliverable') NOT NULL
  COMMENT 'usual:常用联系地址,deliverable:收货地址',
  priority         INT                           NOT NULL DEFAULT 1
  COMMENT '序号,用于排序',
  is_default       BOOLEAN                       NOT NULL DEFAULT FALSE
  COMMENT '是否默认地址',
  ext_data         VARCHAR(1024)                 NOT NULL
  COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `create_time`    DATETIME                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`    TIMESTAMP                     NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user (id),
  FOREIGN KEY (province_id) REFERENCES china_region (id),
  FOREIGN KEY (city_id) REFERENCES china_region (id),
  FOREIGN KEY (region_id) REFERENCES china_region (id)
)
  ENGINE = InnoDB,
  DEFAULT CHARSET = utf8mb4;

创建完后在 DataGrip 中查看建表语句的话,发现多了几个外键的创建索引语句,这是mysql自动给加外键的索引

create table user_address
(
    id bigint auto_increment primary key,
    user_id bigint not null,
    province_id bigint null comment '省份id',
    province varchar(64) null comment '省份',
    city_id bigint null comment '城市id',
    city varchar(64) null comment '城市',
    region_id bigint null comment '区县id',
    region varchar(64) null comment '区县',
    detailed_address varchar(512) not null comment '详细地址',
    type enum('usual', 'deliverable') not null comment 'usual:常用联系地址,deliverable:收货地址',
    priority int default '1' not null comment '序号,用于排序',
    is_default tinyint(1) default '0' not null,
    ext_data varchar(1024) null comment '姓名、手机号地区码、手机号、别名,json字符串',
    app_id int not null,
    status enum('enable', 'disable') default 'enable' not null comment '状态',
    create_time datetime default CURRENT_TIMESTAMP not null,
    update_time timestamp default CURRENT_TIMESTAMP not null,
    constraint user_address_ibfk_1
        foreign key (user_id) references uds.user (id),
    constraint user_address_ibfk_2
        foreign key (province_id) references uds.china_region (id),
    constraint user_address_ibfk_3
        foreign key (city_id) references uds.china_region (id),
    constraint user_address_ibfk_4
        foreign key (region_id) references uds.china_region (id)
);

-- 下面4个创建索引的语句是mysql自动加的
create index user_id on user_address (user_id);
create index city_id on user_address (city_id);
create index province_id on user_address (province_id);
create index region_id on user_address (region_id);

直接在命令行用 show create table user_address 看建表sql也会看到多了几个建索引的 KEY 语句:

CREATE TABLE `user_address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `province_id` bigint(20) DEFAULT NULL COMMENT '省份id',
  `province` varchar(64) DEFAULT NULL COMMENT '省份',
  `city_id` bigint(20) DEFAULT NULL COMMENT '城市id',
  `city` varchar(64) DEFAULT NULL COMMENT '城市',
  `region_id` bigint(20) DEFAULT NULL COMMENT '区县id',
  `region` varchar(64) DEFAULT NULL COMMENT '区县',
  `detailed_address` varchar(512) NOT NULL COMMENT '详细地址',
  `type` enum('usual','deliverable') NOT NULL COMMENT 'usual:常用联系地址,deliverable:收货地址',
  `priority` int(11) NOT NULL DEFAULT '1' COMMENT '序号,用于排序',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `ext_data` varchar(1024) DEFAULT NULL COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `app_id` int(11) NOT NULL,
  `status` enum('enable','disable') NOT NULL DEFAULT 'enable' COMMENT '状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `province_id` (`province_id`),
  KEY `city_id` (`city_id`),
  KEY `region_id` (`region_id`),
  CONSTRAINT `user_address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_address_ibfk_2` FOREIGN KEY (`province_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_3` FOREIGN KEY (`city_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_4` FOREIGN KEY (`region_id`) REFERENCES `china_region` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10070 DEFAULT CHARSET=utf8mb4

show index 查看索引如下:

MariaDB [uds]> show index from user_address;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_address |          0 | PRIMARY     |            1 | id          | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | user_id     |            1 | user_id     | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | province_id |            1 | province_id | A         |          56 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | city_id     |            1 | city_id     | A         |         140 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | region_id   |            1 | region_id   | A         |         255 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.016 sec)

上一篇 OLTP和OLAP

下一篇 TiDB

阅读
2,523
阅读预计11分钟
创建日期 2018-11-28
修改日期 2018-11-30
类别
百度推荐