主页 / dba / 问题 / 176683
Accepted
rasenkantenstein
Asked: 2017-06-20 11:14:58 +0800 CST

MySQL 更新/插入触发器自动递增分组

  • 772

MySQL 更新/插入触发器自动递增分组

我有一个表单，用户可以在其中单击一个标志（如果是，则为 1，如果不是，则为 0）来创建收据编号（receiptno）。我希望这通过数据库实现自动化——因此我想到了触发器。

我想在一个组上创建一个自动增量。

架构：

 CREATE TABLE booking
    (BuchungId int, `category` int, `year` int, `flag` int, receiptno int)
;

INSERT INTO booking
    (BuchungId,`category`, `year`, `flag`, receiptno)
VALUES
    (1, 1, 2016, 1, NULL),
    (2, 1, 2017, 1, NULL), 
    (3, 1, 2017, 0, NULL), 
    (4, 2, 2017, 1, NULL), 
    (5, 2, 2017, 1, NULL),
    (6, 1, 2017, 1, NULL),
    (7, 1, 2017, 0, NULL),
    (8, 1, 2017, 1, NULL)
;

我创建了触发器的主体：

    CREATE TRIGGER trigger1
        BEFORE UPDATE
        ON booking
        FOR EACH ROW
    BEGIN
      IF (new.Flag = 1) THEN

      SET New.receiptno = COALESCE(old.receiptno,0)+1;


      end IF;

end

现在我想按类别和年份更新（或插入）一组收据。

因此最终产生如下表：

(1, 1, 2016, 1, 1),
(2, 1, 2017, 1, 1), 
(3, 1, 2017, 0, NULL), 
(4, 2, 2017, 1, 1), 
(5, 2, 2017, 1, 2),
(6, 1, 2017, 1, 2),
(7, 1, 2017, 0, NULL),
(8, 1, 2017, 1, 3)

http://sqlfiddle.com/#!9/05d7b

mysql trigger

1 个回答

  1. Best Answer

    一种选择是每年每个类别都没有一组独特的收据编号。相反，只需使用BuchungID作为收据编号（我假设该值在您的表中是唯一的，并且很可能是实际数据库中的实际自动增量列）。这应该保证一个唯一的 ID 已经可用，无论用户是否需要收据号。

    但是，我假设您有一些业务规则阻止这个简单的解决方案工作，并且您确实需要在每个类别/年份配对中生成不同的收据编号。

    仅查看最大电流receiptno并添加 1 并不能保证唯一性 - 两个用户可能同时请求收据编号，两者都在receiptno更新之前检查类别和年份的当前最大值，因此获得相同的收据编号。

    我的下一个想法是使用序列，但这样做有两个问题。首先，看起来 MySQL 没有序列对象。其次，由于您必须为每个类别和年份设置一个序列，您要么必须使用动态 SQL 来设置要使用的序列（触发器中不允许），要么必须对所有序列进行硬编码将名称添加到您的查询中（使查询显着复杂化，并在添加新类别或年份时强制您更新它）。

    但是，我们可以通过使用序列表来解决这个问题。这是一个表，其中包含您想要的每个“序列”的行，列标识序列（category和year，在您的情况下），以及定义序列如何工作的其他列（起始值，结束值，增量， ETC。）。

    我在网上找到了一篇文章，其中提供了设置这一切的代码，包括一些我没有想到的选项。

    为了使事情最适合您的情况，我将修改他们的代码如下：

    • 不用命名序列，而是用category和来识别它们year。
    • 使用该nextval功能时，如果没有您传入的the category，让它自动创建一个新序列。year

    （注：以下代码未经测试，只是我对文章中代码的调整）

    CREATE TABLE `sequence_data` (
    `sequence_category` int NOT NULL,
    `sequence_year` int NOT NULL,
    `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    `sequence_cur_value` bigint(20) unsigned DEFAULT 1,
    `sequence_cycle` boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`seq_category`, `seq_year`)
)


DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    SELECT
        sequence_cur_value INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    IF cur_val IS NOT NULL THEN
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sequence_data
                       WHERE sequence_cateogry = seq_category
                       AND   sequence_year = seq_year
                      )
        BEGIN
            INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
            VALUES (seq_category, seq_year, 2);
            SET cur_val = 1;
        END
    END
    END IF;

    RETURN cur_val;
END$$

    在你的触发器中，你会使用这个：

    SET New.receiptno = nextval(`category`, `year`);

    重要的是要注意，正如所写，sequence_cur_val实际上是序列将分配的下一个值，而不是它分配的最后一个值。因此，当您查询sequence_data并看到当前值为 71 时，该类别和年份的最高分配值为 70。此外，如果当前值超过最大值，则将其设置为 NULL（或者，如果您更改, 的默认设置sequence_cycle为序列的最小值-在您的情况下，您绝对不想这样做）。因此，您可能应该添加一个检查以查看函数是否返回 NULL，并适当地出错；否则，将不设置收据编号。

    请阅读这篇文章（以及下面的评论），了解更多关于这一切是如何工作的细节。

    另请注意，该函数必须在事务中运行，以确保您不会获得重复的序列号。如果它只在触发器内运行，那么您可能不需要在函数本身内显式启动事务。

    实际上，我倾向于nextval稍微重新排列代码，以确保在尝试设置值之前我们已经完成了更新或插入（这应该将值锁定到我们的会话）。我相信这个版本会是这样的：

    DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    IF NOT EXISTS (SELECT 1 FROM sequence_data
                   WHERE sequence_cateogry = seq_category
                   AND   sequence_year = seq_year
                  )
        INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
        VALUES (seq_category, seq_year, 2);
    ELSE
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    END IF;

    SELECT
        IF(sequence_cur_val IS NULL, NULL, sequence_cur_value - sequence_increment) INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    RETURN cur_val;
END$$

    （再次，未经测试的代码）。

    希望这能提供帮助，或者至少是一个需要考虑的方向。

    • 1

相关问题