MySQL中获取未占用的最小可用小数，确保未完成状态number列唯一性

这问题我之前也碰到过，核心就是要给状态为「not paid」的记录自动找到向下最近的未被占用的唯一decimal值，下面几个方案都能帮你搞定：

方案一：用存储过程封装自动插入逻辑 #

最省心的方式是把判断和插入逻辑封装成存储过程，应用层直接调用就行，不用管内部细节。假设你的表名为payment_records，number是decimal(10,2)类型，存储过程可以这么写：

DELIMITER //
CREATE PROCEDURE InsertPaymentRecord(
    IN p_number DECIMAL(10,2),
    IN p_status VARCHAR(20),
    IN p_paid_how VARCHAR(20)
)
BEGIN
    DECLARE v_available_number DECIMAL(10,2);
    SET v_available_number = p_number;

    -- 如果是completed状态，直接插入，不做唯一性检查
    IF p_status = 'completed' THEN
        INSERT INTO payment_records(number, status, paid_how) VALUES(v_available_number, p_status, p_paid_how);
    ELSE
        -- 循环查找第一个未被占用的number值（向下递减）
        WHILE EXISTS(SELECT 1 FROM payment_records WHERE status = 'not paid' AND number = v_available_number) DO
            SET v_available_number = v_available_number - 0.01;
            -- 加个最小值限制，防止无限循环，比如不能小于0
            IF v_available_number < 0 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No available number found below 0';
            END IF;
        END WHILE;
        -- 插入找到的可用值
        INSERT INTO payment_records(number, status, paid_how) VALUES(v_available_number, p_status, p_paid_how);
    END IF;
END //
DELIMITER ;

调用示例 #

直接执行存储过程即可，它会自动处理数值调整：

-- 尝试插入number=1.54、status=not paid的记录，会自动调整为1.52
CALL InsertPaymentRecord(1.54, 'not paid', 'paypal');

这个存储过程逻辑很直白：先判断状态，如果是completed直接插入；如果是not paid，就从输入的number开始往下减0.01，直到找到未被占用的值再插入，还加了最小值限制避免死循环。

方案二：用递归CTE预计算可用数值 #

如果你不想用存储过程，也可以用递归CTE生成候选数值，直接定位到第一个可用值：

-- 先设置你要插入的初始number
SET @input_number = 1.54;

WITH RECURSIVE possible_numbers AS (
    SELECT @input_number AS num
    UNION ALL
    SELECT num - 0.01 FROM possible_numbers
    WHERE num - 0.01 >= 0 -- 限制最小值，防止死循环
)
SELECT MIN(num) AS available_number
FROM possible_numbers
WHERE num NOT IN (SELECT number FROM payment_records WHERE status = 'not paid');

这段SQL会生成从1.54开始递减的序列（1.54、1.53、1.52...），然后筛选出第一个不在「not paid」记录里的数值（也就是示例中的1.52），拿到这个值后你就可以在应用层执行插入操作了。

额外优化：添加条件唯一索引 #

为了从数据库层面保证「not paid」记录的number唯一性，你可以添加一个条件唯一索引：

CREATE UNIQUE INDEX idx_not_paid_unique_number ON payment_records(number) WHERE status = 'not paid';

这样如果不小心插入了重复值，数据库会直接报错，你可以在代码里捕获这个错误再触发调整逻辑；或者结合上面的方案，提前计算好可用值再插入，从根源避免冲突。

内容的提问来源于stack exchange，提问作者user1499261