可以在MySQL的BEFORE TRIGGER中安全使用AUTO_INCREMENT [英] Can AUTO_INCREMENT be safely used in a BEFORE TRIGGER in MySQL
本文介绍了可以在MySQL的BEFORE TRIGGER中安全使用AUTO_INCREMENT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Instagram的实现分片的自定义Id的Postgres方法很棒，但是我需要在MySQL中实现.
Instagram's Postgres method of implementing custom Ids for Sharding is great, but I need the implementation in MySQL.
因此，我转换了此博客底部的方法: http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
So, I converted the method found at the bottom of this blog, here: http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
MySQL版本:
CREATE TRIGGER shard_insert BEFORE INSERT ON tablename
FOR EACH ROW BEGIN
DECLARE seq_id BIGINT;
DECLARE now_millis BIGINT;
DECLARE our_epoch BIGINT DEFAULT 1314220021721;
DECLARE shard_id INT DEFAULT 1;
SET now_millis = (SELECT UNIX_TIMESTAMP(NOW(3)) * 1000);
SET seq_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "dbname" AND TABLE_NAME = "tablename");
SET NEW.id = (SELECT ((now_millis - our_epoch) << 23) | (shard_id << 10) | (SELECT MOD(seq_id, 1024)));
END
该表大致如下所示:
CREATE TABLE tablename (
id BIGINT AUTO_INCREMENT,
...
)
问题:
- 这里存在并发问题.当产生100个线程并运行插入时，我得到重复的序列值，这意味着两个触发器都获得相同的auto_increment值.我该如何解决?
我尝试创建一个新表，例如"tablename_seq"(一行)有一个计数器，用于存储我自己的auto_increment值，然后在TRIGGER中对该表进行更新，但是问题是我无法在存储过程(触发)中锁定该表，所以我有了完全一样的问题，我不能保证触发器之间的计数器是唯一的:(.
I tried creating a new table, e.g. "tablename_seq", with one row, a counter to store my own auto_increment values, then doing updates to that table inside the TRIGGER, but the problem is I can't LOCK the table in a Stored Procedure (trigger), so I have the exact same problem, I can't guarantee a counter to be unique between triggers :(.
我很困惑，真的很感谢任何提示！
I'm stumped and really would appreciate any tips!
可能的解决方案:
- MySQL 5.6具有UUID_SHORT()，该UUID_SHORT()生成唯一的递增值，这些值保证是唯一的.实际上，在调用此函数时，每个调用都会增加值+1.通过使用:SET seq_id =(SELECT UUID_SHORT());它似乎消除了并发问题.这样做的副作用是，在整个系统中，每毫秒现在(大约)最多可以进行1024次插入.如果执行更多操作，则可能出现DUPLICATE PRIMARY KEY错误.好消息是，在我的计算机上进行基准测试时，无论是否包含包含UUID_SHORT()的触发器，我都能获得约3,000次插入/秒，因此似乎丝毫没有降低它的速度.
推荐答案
以下 SQL Fiddle 生成如下所示的输出:
The following SQL Fiddle generates an output as shown below:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.35-1
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select `id` from `tablename`;
+-------------------+
| id |
+-------------------+
| 11829806563853313 |
| 11829806563853314 |
| 11829806563853315 |
| 11829806563853316 |
| 11829806563853317 |
| 11829806563853318 |
| 11829806563853319 |
| 11829806563853320 |
| 11829806563853321 |
| 11829806563853322 |
| 11829806563853323 |
| 11829806563853324 |
| 11829806563853325 |
| 11829806563853326 |
| 11829806563853327 |
| 11829806563853328 |
| 11829806563853329 |
| 11829806563853330 |
| 11829806563853331 |
| 11829806563853332 |
| 11829806563853333 |
| 11829806563853334 |
| 11829806563853335 |
| 11829806563853336 |
| 11829806563853337 |
| 11829806563853338 |
| 11829806563853339 |
| 11829806563853340 |
| 11829806563853341 |
| 11829806563853342 |
| 11829806563853343 |
| 11829806563853344 |
| 11829806563853345 |
| 11829806563853346 |
| 11829806563853347 |
| 11829806563853348 |
| 11829806563853349 |
| 11829806563853350 |
| 11829806563853351 |
| 11829806563853352 |
+-------------------+
40 rows in set (0.01 sec)
如果答案确实可以解决您的需求，请接受答案.
Accept the answer if it really solves your need.
更新
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.5.35-1
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DELIMITER //
mysql> DROP FUNCTION IF EXISTS `nextval`//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP TRIGGER IF EXISTS `shard_insert`//
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS `tablename_seq`, `tablename`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `tablename_seq` (
-> `seq` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
-> )//
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `tablename` (
-> `id` BIGINT UNSIGNED PRIMARY KEY
-> )//
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION `nextval`()
-> RETURNS BIGINT UNSIGNED
-> DETERMINISTIC
-> BEGIN
-> DECLARE `_last_insert_id` BIGINT UNSIGNED;
-> INSERT INTO `tablename_seq` VALUES (NULL);
-> SET `_last_insert_id` := LAST_INSERT_ID();
-> DELETE FROM `tablename_seq`
-> WHERE `seq` = `_last_insert_id`;
-> RETURN `_last_insert_id`;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER `shard_insert` BEFORE INSERT ON `tablename`
-> FOR EACH ROW
-> BEGIN
-> DECLARE `seq_id`, `now_millis` BIGINT UNSIGNED;
-> DECLARE `our_epoch` BIGINT UNSIGNED DEFAULT 1314220021721;
-> DECLARE `shard_id` INT UNSIGNED DEFAULT 1;
-> SET `now_millis` := `our_epoch` + UNIX_TIMESTAMP();
-> SET `seq_id` := `nextval`();
-> SET NEW.`id` := (SELECT (`now_millis` - `our_epoch`) << 23 |
-> `shard_id` << 10 |
-> MOD(`seq_id`, 1024)
-> );
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `tablename`
-> VALUES
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0),
-> (0), (0), (0), (0), (0)//
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> DELIMITER ;
mysql> SELECT `id` FROM `tablename`;
+-------------------+
| id |
+-------------------+
| 12581084357198849 |
| 12581084357198850 |
| 12581084357198851 |
| 12581084357198852 |
| 12581084357198853 |
| 12581084357198854 |
| 12581084357198855 |
| 12581084357198856 |
| 12581084357198857 |
| 12581084357198858 |
| 12581084357198859 |
| 12581084357198860 |
| 12581084357198861 |
| 12581084357198862 |
| 12581084357198863 |
| 12581084357198864 |
| 12581084357198865 |
| 12581084357198866 |
| 12581084357198867 |
| 12581084357198868 |
| 12581084357198869 |
| 12581084357198870 |
| 12581084357198871 |
| 12581084357198872 |
| 12581084357198873 |
| 12581084357198874 |
| 12581084357198875 |
| 12581084357198876 |
| 12581084357198877 |
| 12581084357198878 |
| 12581084357198879 |
| 12581084357198880 |
| 12581084357198881 |
| 12581084357198882 |
| 12581084357198883 |
| 12581084357198884 |
| 12581084357198885 |
| 12581084357198886 |
| 12581084357198887 |
| 12581084357198888 |
+-------------------+
40 rows in set (0.00 sec)
请参见 db-fiddle .
这篇关于可以在MySQL的BEFORE TRIGGER中安全使用AUTO_INCREMENT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
