mysql - 如何检索外键和 INSERT INTO 表 (MySQL/InnoDB)

Question

我正在从命令行和/或 mysql> 提示符执行以下所有操作。

我是数据库新手，注意到有很多关于结构(教程)和定义(手册)的网站，但没有关于实际示例的网站。如果我有两个像这样的表

CREATE TABLE IF NOT EXISTS owner
(
  ID INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL UNIQUE,
  PRIMARY KEY(ID)
)ENGINE=InnoDB


CREATE TABLE IF NOT EXISTS dog
(
  ID INT NOT NULL AUTO_INCREMENT,
  owner INT NOT NULL,
  name VARCHAR(32) NOT NULL UNIQUE,
  PRIMARY KEY(ID),
  FOREIGN KEY (owner) REFERENCES owner(ID)
)ENGINE=InnoDB

如果我想添加到 table 狗

INSERT INTO dog (owner, name) VALUES(get_owner_ID("Peter Griffin"), "Brian Griffin");

如何从名称(“Peter Griffin”)获取神秘的所有者 ID

Answer 1

您可以在插入 dog 表时运行子选择，或者创建 user defined function你自己。用户定义的函数比存储过程具有优势，因为它可以在插入期间调用。因此:

CREATE TABLE IF NOT EXISTS owner
(
  ID INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL UNIQUE,
  PRIMARY KEY(ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dog
(
  ID INT NOT NULL AUTO_INCREMENT,
  owner INT NOT NULL,
  name VARCHAR(32) NOT NULL UNIQUE,
  PRIMARY KEY(ID),
  FOREIGN KEY (owner) REFERENCES owner(ID)
)ENGINE=InnoDB;

现在创建函数:

DELIMITER //

DROP FUNCTION IF EXISTS get_owner_id//

CREATE FUNCTION get_owner_id(i_owner_name varchar(32)) returns integer 
READS SQL DATA
BEGIN
 declare v_owner_id int;
 select id into v_owner_id from owner where name = i_owner_name; 
 return v_owner_id;
END//

DELIMITER ;

insert into owner(name) values ('Peter Griffin');
insert into dog (owner,name) values (get_owner_id('Peter Griffin'),'Brian Griffin');

当然，您必须想出一种方法来处理所有者不存在于 owner 表中的情况。也许所有者表中有一个默认的“未知所有者”，如果找不到所有者，则从函数返回该值？有点取决于你...