用MySQL Procedure同时像级联表插入数据

一个月以来都没写博客，这篇博客的目的是提醒自己继续写下去，顺便记下笔记，看官若无兴趣请直接飘过。 级联表是关系数据库存储领域模型（Domain Model）中一对多关系的不二法门，比如“学生”和“班级”，实在是常用得很。创建表时建立外键关联，查询时使用

一个月以来都没写博客，这篇博客的目的是提醒自己继续写下去，顺便记下笔记，看官若无兴趣请直接飘过。

级联表是关系数据库存储领域模型（Domain Model）中一对多关系的不二法门，比如“学生”和“班级”，实在是常用得很。创建表时建立外键关联，查询时使用inner join或者多表联合查询非常便捷。不过插入数据则相对麻烦，因为关键关联的缘故，需要先插入主表，然后再插入从表，如果使用auto_increment主键，在插入从表之前必须获取刚刚插入主表时生成的ID。

举例来说，下面classes和students表通过外键class_id建立一对多关联：

DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS classes;
DROP VIEW IF EXISTS student_in_class;
CREATE TABLE classes(
  id serial,
  name CHAR(55) NOT NULL,
  UNIQUE KEY cls_name (name),
  PRIMARY KEY(id)
);
CREATE TABLE students(
  NUMBER CHAR(11) NOT NULL,
  name VARCHAR(55) NOT NULL,
  class_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(NUMBER),
  FOREIGN KEY (class_id) REFERENCES classes(id)
);

为了查询数据方便，创建一个视图，只是简单的执行级联查询：

CREATE VIEW student_in_class AS
SELECT NUMBER, students.name stu_name, classes.name cls_name 
FROM students
INNER JOIN classes
ON classes.id=students.class_id;

对于之前提出的两个表同时插入数据的问题，熟悉MySQL的朋友都知道，用下面的方法就行了，先插入主表classes，然后通过last_insert_id()获取刚刚插入的id，最后向从表students插入数据。

START TRANSACTION;
INSERT INTO classes(name) VALUE('Class 1');
INSERT INTO students(NUMBER, name, class_id) 
  VALUES('001', 'Jim', last_insert_id());
commit;

不过这样还是有个问题，如果待插入的数据和主表中已有的数据有重复怎么办呢？因此笔者对以上语句进行简单的封装，使用MySQL存储过程实现整个过程，首先对主表进行查询，如果不存在待插入的数据再插入：

DROP PROCEDURE IF EXISTS insert_stu;
CREATE PROCEDURE insert_stu(
  cls_name CHAR(55), 
  stu_num CHAR(11), 
  stu_name VARCHAR(55))
BEGIN 
  DECLARE cls_id BIGINT UNSIGNED;
  DECLARE cls_cnt INT;
  SELECT COUNT(*) INTO cls_cnt FROM classes WHERE name=cls_name;
  IF cls_cnt = 0 THEN
    INSERT INTO classes(name) VALUE(cls_name);
    SET cls_id = last_insert_id();
  ELSE
    SELECT id INTO cls_id FROM classes WHERE name=cls_name;
  END IF;
  INSERT INTO students(NUMBER, name, class_id) 
    VALUES(stu_num, stu_name, cls_id);
END;

调用和检验该存储过程的方法如下：

CALL insert_stu('Class 1', '001', 'Bob');
CALL insert_stu('Class 2', '002', 'Jim');
CALL insert_stu('Class 1', '003', 'Li Lei');
SELECT * FROM student_in_class;

最后一句是使用之前创建的视图查看输出结果：

number  stu_name  cls_name
001        Bob          Class 1
002        Jim           Class 2
003        Li Lei        Class 1

参考：

1. *: Is it possible to insert into two tables at the same time?
2. http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
3. MySQL transaction commit
4. MySQL last_insert_id() function
5. MySQL If statement

原文地址：用MySQL Procedure同时像级联表插入数据, 感谢原作者分享。