具有 LAST_INSERT_ID() 的 MySQL 函数给出意外结果

Question

我在这上面花了 24 小时，为了速度的缘故，我需要利用现有知识。

我有这个 mysql 函数:

DELIMITER $$
USE `db`$$

DROP FUNCTION IF EXISTS `insertindb`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `insertindb`(itemtype  VARCHAR(10), dname VARCHAR (50), id INTEGER, fname VARCHAR(50), lname VARCHAR(50)) RETURNS INT(11)
DETERMINISTIC
BEGIN 
    DECLARE ret INT;
    DECLARE ret2 INT;
    IF itemtype = 'state' THEN 
        INSERT IGNORE INTO state (`name`) VALUES (dname);
        SELECT LAST_INSERT_ID() INTO ret;       
        IF ret = 0 THEN
           SELECT `id` INTO ret2 FROM state WHERE `name` LIKE dname;
           SET ret = ret2;
        END IF;              
    END IF;
  RETURN ret;
END$$

DELIMITER ;

目标？当 state 表中已经存在一个状态时，它应该返回 id 列(这是自增键)。当它不存在时，它应该插入它并返回新(自动)分配的 id。

该功能正在运行，但做了两件我不想做的事情:

1) 当记录已经存在时，它返回零而不是现有记录的id。我的代码旨在解决这个问题，但它不起作用 请注意，我在表上定义了一个索引，以确保 state.name 在表中是唯一的，以便 INSERT IGNORE 起作用

2) LAST_INSERT_ID() 保持递增，即使记录存在因此没有插入任何内容。因此，例如，如果状态表只有一个记录开始(比如 id==>1，名称 ==> 'State A'，)如果我调用该函数 2 次以尝试重新插入 'State A'(哪个不会像上面第 1 点中提到的那样插入)，然后再次调用它来插入“State B”，该表现在将有第二条记录 id==>4，name ==>“State B”跳过 id 值 2 和 3 . 有没有办法让 LAST_INSERT_ID 以不同的方式工作？

我现在的代码

BEGIN 
DECLARE ret,ret2 INT;

IF itemtype = 'state' THEN 
    SELECT `id` INTO ret FROM state WHERE `name` LIKE dname;
    #insert into debug (dump1,dump2) values ('ret will be',ret);
    IF ret IS NULL OR ret = '' THEN

        INSERT IGNORE INTO state (`name`) VALUES (dname);
        SELECT LAST_INSERT_ID() INTO ret2;  
        #SELECT `id` INTO ret FROM state WHERE `name` LIKE dname;
        RETURN ret2;
    ELSE
        RETURN ret;
    END IF; 
END IF;

结束$$

Answer 1

洛伦兹已经给了你解决方案:

 SELECT `id` INTO ret FROM state WHERE `name` LIKE dname;
 IF ret IS NULL THEN
     INSERT IGNORE INTO state (`name`) VALUES (dname);
     SELECT `id` INTO ret FROM state WHERE `name` LIKE dname;
 END IF;         

如果您同时多次调用同一个函数，您仍然有可能在 id 之间出现差距，那么您需要不同的方法:

1) 移除你的 id 上的 auto_increment2) 在 id 上创建唯一索引3) 使用这种查询来插入新值:

INSERT INTO state (name, id)
SELECT dname, MAX(id) + 1 FROM state
WHERE NOT EXISTS (SELECT * FROM state WHERE name=dname);
SELECT id INTO ret FROM state WHERE name=dname;

当 MAX(id) 返回 NULL 时，它可能不适用于第一个状态。