当前位置:Gxlcms > mysql > 用MySQLProcedure同时像级联表插入数据

用MySQLProcedure同时像级联表插入数据

时间:2021-07-01 10:21:17 帮助过:36人阅读

一个月以来都没写博客,这篇博客的目的是提醒自己继续写下去,顺便记下笔记,看官若无兴趣请直接飘过。 级联表是关系数据库存储领域模型(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. Stackoverflow: 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

人气教程排行