mysql - 有问题的 LAST_INSERT_ID

Question

CREATE FUNCTION `getSequenceNumber` (
company_id INTEGER, sequence_name varchar(255)) RETURNS INT(10)
BEGIN
INSERT INTO sequences (`company_id`, `name`,       `value`)
     VALUES            (company_id, sequence_name, LAST_INSERT_ID(1))
     ON DUPLICATE KEY UPDATE
        `value` = LAST_INSERT_ID(value + 1); 
RETURN LAST_INSERT_ID(); END


CREATE TABLE `sequences` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sequences_name_company_id_unique` (`name`,`company_id`),
  KEY `sequences_company_id_index` (`company_id`),
  KEY `sequences_value_index` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


//sample output
MariaDB [testdb]> select version();
+----------------+
| version()      |
+----------------+
| 10.2.6-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select `getSequenceNumber`(1,'sequence_021');
+---------------------------------------+
| `getSequenceNumber`(1,'sequence_021') |
+---------------------------------------+
|                                     2 |
+---------------------------------------+
1 row in set (0.03 sec)

MariaDB [testdb]> select `getSequenceNumber`(1,'sequence_0212');
+----------------------------------------+
| `getSequenceNumber`(1,'sequence_0212') |
+----------------------------------------+
|                                      5 |
+----------------------------------------+
1 row in set (0.03 sec)

MariaDB [testdb]> select `getSequenceNumber`(1,'new_sequence123');
+------------------------------------------+
| `getSequenceNumber`(1,'new_sequence123') |
+------------------------------------------+
|                                        6 |
+------------------------------------------+
1 row in set (0.03 sec)

我的 MariaDB 有这个函数，它可以工作，但问题是插入时，它插入的新 ID 是 <last_id> + <last value>有没有办法清除/刷新 LAST_INSERT_ID在插入记录之前？

编辑:添加了创建sql语句和示例输出

Answer 1

我无法重现该问题:

MariaDB [_]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 10.2.6-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [_]> DROP TABLE IF EXISTS `sequences`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaMariaDB [_]> CREATE TABLE IF NOT EXISTS `sequences` (
    ->   `name` VARCHAR(255) PRIMARY KEY,
    ->   `value` BIGINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [_]> DROP FUNCTION IF EXISTS `getSequenceNumber`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [_]> DELIMITER //

MariaDB [_]> CREATE OR REPLACE FUNCTION `getSequenceNumber` (
    ->   `sequence_name` VARCHAR(255)
    -> )
    -> RETURNS BIGINT UNSIGNED
    -> BEGIN
    ->   INSERT INTO `sequences` (`name`, `value`)
    ->   VALUES (`sequence_name`, LAST_INSERT_ID(1))
    ->     ON DUPLICATE KEY UPDATE `value` = LAST_INSERT_ID(`value` + 1); 
    ->   RETURN LAST_INSERT_ID();
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER ;

MariaDB [_]> SELECT `getSequenceNumber`('sequence_0'); -- 1
+-----------------------------------+
| `getSequenceNumber`('sequence_0') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`('sequence_0'); -- 2
+-----------------------------------+
| `getSequenceNumber`('sequence_0') |
+-----------------------------------+
|                                 2 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`('sequence_1'); -- 1
+-----------------------------------+
| `getSequenceNumber`('sequence_1') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.01 sec)

MariaDB [_]> SELECT `getSequenceNumber`('sequence_2'); -- 1
+-----------------------------------+
| `getSequenceNumber`('sequence_2') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`('sequence_0'); -- 3
+-----------------------------------+
| `getSequenceNumber`('sequence_0') |
+-----------------------------------+
|                                 3 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`('sequence_2'); -- 2
+-----------------------------------+
| `getSequenceNumber`('sequence_2') |
+-----------------------------------+
|                                 2 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`('sequence_1'); -- 2
+-----------------------------------+
| `getSequenceNumber`('sequence_1') |
+-----------------------------------+
|                                 2 |
+-----------------------------------+
1 row in set (0.00 sec)

更新

MariaDB [_]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 10.2.6-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [_]> DROP FUNCTION IF EXISTS `getSequenceNumber`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DROP TABLE IF EXISTS `sequences`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `sequences` (
    ->   `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    ->   `company_id` BIGINT UNSIGNED unsigned NOT NULL,
    ->   `value` BIGINT UNSIGNED NOT NULL,
    ->   PRIMARY KEY `sequences_name_company_id_unique` (`name`, `company_id`),
    ->   KEY `sequences_company_id_index` (`company_id`),
    ->   KEY `sequences_value_index` (`value`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER //

MariaDB [_]> CREATE OR REPLACE FUNCTION `getSequenceNumber` (
    ->   `_company_id` BIGINT UNSIGNED,
    ->   `sequence_name` VARCHAR(255)
    -> ) RETURNS BIGINT UNSIGNED
    -> BEGIN
    ->   INSERT INTO `sequences` (`name`, `company_id`, `value`)
    ->   VALUES (`sequence_name`, `_company_id`, LAST_INSERT_ID(1))
    ->     ON DUPLICATE KEY UPDATE `value` = LAST_INSERT_ID(`value` + 1);
    ->   RETURN LAST_INSERT_ID();
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DELIMITER ;

MariaDB [_]> SELECT `getSequenceNumber`(1, 'sequence_021'); -- 1
+----------------------------------------+
| `getSequenceNumber`(1, 'sequence_021') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`(2, 'sequence_021'); -- 1
+----------------------------------------+
| `getSequenceNumber`(2, 'sequence_021') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.01 sec)

MariaDB [_]> SELECT `getSequenceNumber`(1, 'sequence_021'); -- 2
+----------------------------------------+
| `getSequenceNumber`(1, 'sequence_021') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+
1 row in set (0.02 sec)

MariaDB [_]> SELECT `getSequenceNumber`(2, 'sequence_021'); -- 2
+----------------------------------------+
| `getSequenceNumber`(2, 'sequence_021') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`(1, 'sequence_0212'); -- 1
+-----------------------------------------+
| `getSequenceNumber`(1, 'sequence_0212') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`(1, 'sequence_021'); -- 3
+----------------------------------------+
| `getSequenceNumber`(1, 'sequence_021') |
+----------------------------------------+
|                                      3 |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`(1, 'new_sequence123'); -- 1
+-------------------------------------------+
| `getSequenceNumber`(1, 'new_sequence123') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `getSequenceNumber`(1, 'new_sequence123'); -- 2
+-------------------------------------------+
| `getSequenceNumber`(1, 'new_sequence123') |
+-------------------------------------------+
|                                         2 |
+-------------------------------------------+
1 row in set (0.01 sec)