mysql - MySQL 触发器中的持久/静态变量

Question

我在黑洞表上有以下触发器，它拦截插入并将它们传递给其他表。
为了加快速度，我想计算一个递增的值并将其传递到我的插入中。

DELIMITER $$

CREATE TRIGGER ai_blackhole_each AFTER INSERT ON `test.blackhole` FOR EACH ROW
BEGIN
  DECLARE calculated_id INTEGER;

  SET calculated_id = calc_id_for_previous_insert + 1;
  INSERT INTO example VALUES(new.field1, new.field2, calculated_id, ..);
END$$

DELIMITER ;

我可以在触发器中使用一个静态变量来在两次触发之间保持它的值吗？
或者是否有有效实现类似目标的技巧？

Answer 1

在 MEMORY 表中放置一个从 0 开始的间歇值

use test
DROP TABLE IF EXISTS test.blackholecounter;
DROP TABLE IF EXISTS test.blackhole;
DROP TABLE IF EXISTS test.example;
CREATE TABLE test.blackholecounter (calc_id INT NOT NULL DEFAULT 0) ENGINE=MEMORY;
INSERT INTO test.blackholecounter VALUES (0);
CREATE TABLE test.blackhole
(
    field1 VARCHAR(20),
    field2 VARCHAR(20)
) ENGINE=BLACKHOLE;
CREATE TABLE test.example (field1 VARCHAR(20),
field2 VARCHAR(20),
calc_id INT);
DELIMITER $$
CREATE TRIGGER ai_blackhole_each AFTER INSERT ON test.blackhole
FOR EACH ROW
BEGIN
    DECLARE calculated_id INTEGER;
    SELECT calc_id INTO calculated_id FROM test.blackholecounter;
    UPDATE test.blackholecounter SET calc_id=calc_id+1;
    INSERT INTO test.example VALUES(new.field1, new.field2, calculated_id);
END
$$
DELIMITER ;
SELECT * FROM test.blackholecounter;
SELECT * FROM test.example;
INSERT INTO test.blackhole (field1,field2) VALUES ('rolando','edwards'),('pamela','edwards');
SELECT * FROM test.blackholecounter;
SELECT * FROM test.example;

这是我将其粘贴到 MySQL 时得到的结果

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test.blackholecounter;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS test.blackhole;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS test.example;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE test.blackholecounter (calc_id INT NOT NULL DEFAULT 0) ENGINE=MEMORY;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test.blackholecounter VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test.blackhole(field1 VARCHAR(20),field2 VARCHAR(20)) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE test.example (field1 VARCHAR(20),field2 VARCHAR(20),calc_id INT);
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER ai_blackhole_each AFTER INSERT ON test.blackhole
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE calculated_id INTEGER;
    ->     SELECT calc_id INTO calculated_id FROM test.blackholecounter;
    ->     UPDATE test.blackholecounter SET calc_id=calc_id+1;
    ->     INSERT INTO test.example VALUES(new.field1, new.field2, calculated_id);
    -> END
    -> $$
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
mysql> SELECT * FROM test.blackholecounter;
+---------+
| calc_id |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.example;
Empty set (0.02 sec)

mysql> INSERT INTO test.blackhole (field1,field2) VALUES ('rolando','edwards'),('pamela','edwards');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test.blackholecounter;
+---------+
| calc_id |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.example;
+---------+---------+---------+
| field1  | field2  | calc_id |
+---------+---------+---------+
| rolando | edwards |       0 |
| pamela  | edwards |       1 |
+---------+---------+---------+
2 rows in set (0.00 sec)

您可以用其他数字开始 test.blackholecounter 表中的初始值，或者在触发器中发生增量时更改顺序。

试一试!!!