主页 / dba / 问题 / 339907
Accepted
FloT
Asked: 2024-05-30 21:34:59 +0800 CST

为什么MySQL在DDL中为复合主键生成“key”语句？

  • 772

我使用的是 MySQL 版本 8.3.0。

我可以使用以下 SQL 语句在人员和团队表之间创建“多对多”关系：

create table persons (
  id bigint not null,
  last_name varchar(255) not null,
  first_name varchar(255),
  primary key (id)
);

create table teams (
  id bigint not null,
  team_name varchar(255) not null,
  primary key (id)
);

create table person_team (
  person_id bigint not null,
  team_id bigint not null,
  primary key (person_id, team_id),
  constraint fk_person_team_person foreign key (person_id) references persons(id),
  constraint fk_person_team_team foreign key (team_id) references teams(id)
);

因此该person_team表有一个复合主键，并且它的每一列都是它所连接的表的外键。

person_team如果我检索with 的DDL 语句show create table person_team;，它会给出以下 SQL：

CREATE TABLE `person_team` (
  `person_id` bigint NOT NULL,
  `team_id` bigint NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`),
  KEY `fk_person_team_team` (`team_id`),
  CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
  CONSTRAINT `fk_person_team_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

因此它会自动向表KEY中添加一条名为外键的指令（第 5 行）teams。

为什么会有这种行为？这个额外的KEY说明对我来说听起来不需要。为什么它显示的是 forfk_person_team_team而不是 for 的密钥fk_person_team_person？最初的陈述是否错误或不完整？自动生成的DDL是否正确？当我使用 转储数据库时mysqldump，我获得了与自动生成的 DDL 相同的代码。修改这段代码来恢复数据库是否更安全？

mysql

2 个回答

  1. Best Answer

    有时会出现这种情况，答案可以在文档中找到：

    MySQL 需要外键和引用键上的索引，以便外键检查可以快速且不需要表扫描。在引用表中，必须有一个索引，其中外键列按相同顺序列为第一列。如果引用表不存在，则会自动在引用表上创建此类索引。

    （强调我的）。

    person_team您在:person_id和team_id;中有两个引用列前者被表的主键覆盖，而后者则没有，因为它不在PK中的第一个位置，所以为其创建一个额外的索引。

    • 7

  2. 原因是这样的：外键引用fk_person_team_person已经看到一个可以满足外键关系的索引。在您的情况下，它是主键，因为前导列是person_id。

    至于另一个外键参考，fk_person_team_team，不存在能够满足外键关系的索引。

    我们用下面的代码来证明这一点

    DROP DATABASE IF EXISTS FKTEST;
CREATE DATABASE FKTEST;
USE FKTEST
create table persons (
  id bigint not null,
  last_name varchar(255) not null,
  first_name varchar(255),
  primary key (id)
);

create table teams (
  id bigint not null,
  team_name varchar(255) not null,
  primary key (id)
);

CREATE TABLE `person_team` (
  `person_id` bigint NOT NULL,
  `team_id` bigint NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`)
) ENGINE=InnoDB;


ALTER TABLE `person_team`
ADD FOREIGN KEY `fk_person_team_person` (`person_id`) REFERENCES `persons` (`id`);
SHOW CREATE TABLE person_team\G
ALTER TABLE `person_team`
ADD FOREIGN KEY `fk_person_team_team`   (`team_id`) REFERENCES `teams` (`id`);
SHOW CREATE TABLE person_team\G

    这将首先创建没有 FK 的 person_team 表。然后我会一次添加一个 FK

    这是结果

    MariaDB [FKTEST]> DROP DATABASE IF EXISTS FKTEST;
ATE DATABASE FKTEST;
USE FKTEST
Query OK, 3 rows affected (0.035 sec)

MariaDB [(none)]> CREATE DATABASE FKTEST;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE FKTEST
Database changed
MariaDB [FKTEST]> create table persons (
    ->   id bigint not null,
last_na    ->   last_name varchar(255) not null,
    ->   first_name varchar(255),
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.009 sec)

MariaDB [FKTEST]> create table teams (
    ->   id bigint not null,
    ->   team_name varchar(255) not null,
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.014 sec)

MariaDB [FKTEST]> CREATE TABLE `person_team` (
    ->   `person_id` bigint NOT NULL,
    ->   `team_id` bigint NOT NULL,
    ->   PRIMARY KEY (`person_id`,`team_id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.011 sec)

MariaDB [FKTEST]> ALTER TABLE `person_team` ADD FOREIGN KEY `fk_person_team_person` (`person_id`) REFERENCES `persons` (`id`);
Query OK, 0 rows affected (0.034 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [FKTEST]> SHOW CREATE TABLE person_team\G
*************************** 1. row ***************************
       Table: person_team
Create Table: CREATE TABLE `person_team` (
  `person_id` bigint(20) NOT NULL,
  `team_id` bigint(20) NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`),
  CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.002 sec)

MariaDB [FKTEST]> ALTER TABLE `person_team` ADD FOREIGN KEY `fk_person_team_team`   (`team_id`) REFERENCES `teams` (`id`);
Query OK, 0 rows affected (0.036 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [FKTEST]> SHOW CREATE TABLE person_team\G
*************************** 1. row ***************************
       Table: person_team
Create Table: CREATE TABLE `person_team` (
  `person_id` bigint(20) NOT NULL,
  `team_id` bigint(20) NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`),
  KEY `fk_person_team_team` (`team_id`),
  CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
  CONSTRAINT `fk_person_team_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.013 sec)

MariaDB [FKTEST]>

    请注意以下事项

    • 添加了第一个 FK 约束但没有创建额外的索引
    • 添加了第二个 FK 约束并创建了一个附加索引
    • 3

相关问题