一个MySQL储存过程（自动插入外键）

最新推荐文章于 2022-10-25 22:48:37 发布

kagami318

最新推荐文章于 2022-10-25 22:48:37 发布

阅读量380

点赞数

分类专栏：数据库（MySQL）文章标签： mysql path insert

本文链接：https://blog.csdn.net/kagami318/article/details/6939214

版权

数据库（MySQL）专栏收录该内容

1 篇文章 0 订阅

订阅专栏

先把语句贴出来：

CREATE DEFINER=`root`@`localhost` PROCEDURE `tracks_in`(in in_title varchar(100),in in_artist 

varchar(100),in in_album varchar(100),in in_track int,in in_year int,in in_length int,in in_size varchar

(30),in in_last_modified varchar(30),in in_path varchar(500),in in_filename varchar(250))
BEGIN
 DECLARE id_artist int default 0;
    declare id_album int default 0;
    declare id_path int default 0;
    
    select a_id into id_artist from artist where a_artist=in_artist;
    if(id_artist=0) then
    insert into artist(a_artist) values(in_artist); 
    SELECT LAST_INSERT_ID() into id_artist;
    end if;
    
    select a_id into id_album from album where a_album=in_album;
    if(id_album=0) then
    insert into album(a_album,a_year) values(in_album,in_year); 
    SELECT LAST_INSERT_ID() into id_album;
    end if;
    
    select p_id into id_path from path where p_path=in_path;
    if(id_path=0) then
    insert into path(p_path) values(in_path); 
    SELECT LAST_INSERT_ID() into id_path;
    end if;
    
    insert into tracks(t_title,t_artist,t_album,t_track,t_length,t_size,t_last_modified,t_path,t_filename)
                values
                (in_title,id_artist,id_album,in_track,in_length,in_size,in_last_modified,id_path,in_filename);
END

这是三张表的关系：

接下来解释下，输入参数很多。。。其中有3个需要判断，拿path举例子吧。先定义个id_path初始为0，然后在path表里搜索下有没和in_path一样的记录，有的话返回id给id_path,没有的话id_path当然还是0.是0时往path表里插入in_path,然后用LAST_INSERT_ID()获得刚插入的id，关于LAST_INSERT_ID()可去看MySQL手册我在那找的。