mysql - 创建自引用行时如何避免使用 max(id)

Question

我有一个 MySQL 表

id | ref_id

其中 ref_id 应等于可用 id 值之一(包括同一行的值)。 id 也是自动递增的。

在存储过程中，我想在此表中添加一行，其中 ref_id = id。我可以通过

DECLARE prev_id INT;
SELECT MAX(id) INTO prev_id FROM `table`;
INSERT INTO table (ref_id) VALUES (prev_id + 1)

然而却是not recommended为此目的使用 MAX(id) 。我应该使用 LAST_INSERT_ID() 代替。但是如果我没有插入任何其他东西，我该怎么做呢？

Answer 1

架构

-- drop table selfie;
create table selfie
(   id int auto_increment primary key,
    thing varchar(40) not null,
    ref_id int not null,
    descr varchar(40) not null
);

存储过程

drop procedure if exists spInsertOneSelfRef;

DELIMITER $$
create procedure spInsertOneSelfRef
(   pThing varchar(40),
    pRef int, -- 0 if to self, >0 if not 
    pDescr varchar(40)
)
begin
  insert selfie(thing,ref_id,descr) values (pThing,pRef,pDescr);
    -- Moment A
  IF (pRef=0) then
    -- self-reference request, ditch the zero that was just inserted
    set @theId:= last_insert_id();
    update selfie set ref_id=@theId where id=@theId;
    -- MOMENT B
  END IF;
end
$$
DELIMITER ;

测试一下

-- truncate table selfie;
call spInsertOneSelfRef('frog',0,'a selfie'); -- 
call spInsertOneSelfRef('cat',1,''); -- 
call spInsertOneSelfRef('mouse',2,''); 
call spInsertOneSelfRef('dog',3,''); -- 
call spInsertOneSelfRef('abcd',0,'a selfie'); -- 

查看结果

select * from  selfie;
+----+-------+--------+----------+
| id | thing | ref_id | descr    |
+----+-------+--------+----------+
|  1 | frog  |      1 | a selfie |
|  2 | cat   |      1 |          |
|  3 | mouse |      2 |          |
|  4 | dog   |      3 |          |
|  5 | abcd  |      5 | a selfie |
+----+-------+--------+----------+

只需进行一些调整，就可以将 null 插入到 ref_id 中。并处理存储过程中列出的时刻A和时刻B之间的瞬间并发控制。我把这些留给读者，除非你恳求我这样做。

<小时/>

选项 B(ref_id 架构更改)

drop table selfie;
create table selfie
(   id int auto_increment primary key,
    thing varchar(40) not null,
    ref_id int, -- NULL means I have no parent
    descr varchar(40) not null,
    constraint fk_blahblah foreign key (ref_id) references selfie(id)
);

测试

call spInsertOneSelfRef('abcd',null,'no parent'); -- good
call spInsertOneSelfRef('pqrs',1,''); --  good
call spInsertOneSelfRef('zzz',44,''); -- bad (as expected, FK failure)

查看

+----+-------+--------+-----------+
| id | thing | ref_id | descr     |
+----+-------+--------+-----------+
|  1 | abcd  |   NULL | no parent |
|  2 | pqrs  |      1 |           |
+----+-------+--------+-----------+

哦，我现在有 parent 了，没问题，更新 ref_id 列