每个键值的循环自动增量 [英] cyclic autoincrement for each key value
问题描述
我有一些记录的mysql表，例如:
CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
value varchar NOT NULL
)
现在，我需要的是在php脚本中生成1、2，...，N的唯一序列并将其存储到另一个表中. ?
我想知道是否可以使用一些附加的mysql表，但是我不知道如何创建诸如为每个列值分别自动递增"之类的东西.
test:
1 ... apples
2 ... oranges
3 ... lemons
一些php脚本(当时多个用户并行访问):
save_next_fruit($_GET['fruit']);
将在另一个表中创建具有以下值的记录:
saved_fruit:
ID | FRUIT(FK) | FRUIT_NO
1 1 1
2 1 2
3 2 1
4 3 1
5 3 2
6 1 3
7 3 3
8 2 2
9 1 4
10 2 3
11 1 5
12 2 4
13 1 6
14 3 4
15 3 5
换句话说，我需要这样做(例如，对于水果3(柠檬)):
insert into saved_fruit (fruit, fruit_no) values (3, select MAX(fruit_no)+1 from saved_fruit where fruit = 3);
但是以线程安全的方式进行(我了解上述命令在MyISAM MySQL数据库中不是线程安全的)
你能帮忙吗?
谢谢
MyISAM确实支持此行为.创建一个两列的主键，并使 second 列自动递增.它将从第一列中的每个不同值重新开始.
CREATE TABLE t (i INT, j INT AUTO_INCREMENT, PRIMARY KEY (i,j)) ENGINE=MyISAM;
INSERT INTO t (i) VALUES (1), (1), (2), (2), (1), (3);
SELECT * FROM t;
+---+---+
| i | j |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
+---+---+
但是，考虑一下，这仅在对INSERT语句执行表级锁定的存储引擎中是线程安全的.因为INSERT必须搜索表中的其他行以找到每个相同的i值的最大j值.如果其他人同时执行INSERT，则会创建竞争条件.
因此，它依赖MyISAM，后者对INSERT进行表级锁定.
请参见手册中的以下参考: http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html 在 MyISAM Notes 部分下.
有很多很好的理由不使用MyISAM.对我而言，决定性因素是MyISAM损坏数据的趋势.
发表您的评论
InnoDB不支持上述的每组增量行为.您可以制作一个多列主键，但是出现的错误是因为InnoDB要求auto-increment列是表键的第一列(严格来说，它不必是主键)
无论自动递增列在多列键中的位置如何，仅当与InnoDB一起使用时，它才会递增；它不会在另一列中为每个不同的值对条目编号.
要对InnoDB表执行此操作，必须在INSERT期间显式锁定表，以避免出现竞争情况.您将对要插入的组中的最大值执行自己的SELECT查询.然后插入该值+1.
基本上，您必须绕过自动递增功能并指定值，而不是让它们自动生成.
I have mysql table of some records, e.g.:
CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
value varchar NOT NULL
)
now, what I need is to generate unique sequence of 1, 2, ..., N in php script and store to another table... How to achieve this to be thread-safe and not creating doubles or skipping something?
I was wondering if some additional mysql table could be helpful, but I don't know how to create something like "separate autoincrements for each column value" or anything else...
test:
1 ... apples
2 ... oranges
3 ... lemons
some php script (accessed parallely by multiple users at the time):
save_next_fruit($_GET['fruit']);
will create some record in another tables with values like this:
saved_fruit:
ID | FRUIT(FK) | FRUIT_NO
1 1 1
2 1 2
3 2 1
4 3 1
5 3 2
6 1 3
7 3 3
8 2 2
9 1 4
10 2 3
11 1 5
12 2 4
13 1 6
14 3 4
15 3 5
other words, I need to do this (e.g. for fruit 3 (lemons)):
insert into saved_fruit (fruit, fruit_no) values (3, select MAX(fruit_no)+1 from saved_fruit where fruit = 3);
but in thread safe way (I understand that above command is not thread safe in MyISAM MySQL database)
Can you help?
Thanks
MyISAM does support this behavior. Create a two-column primary key, and make the second column auto-increment. It'll start over for each distinct value in the first column.
CREATE TABLE t (i INT, j INT AUTO_INCREMENT, PRIMARY KEY (i,j)) ENGINE=MyISAM;
INSERT INTO t (i) VALUES (1), (1), (2), (2), (1), (3);
SELECT * FROM t;
+---+---+
| i | j |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
+---+---+
But if you think about it, this is only thread-safe in a storage engine that does table-level locking for INSERT statements. Because the INSERT has to search other rows in the table to find the max j value per the same i value. If other people are doing INSERTs concurrently, it creates a race condition.
Thus, the dependency on MyISAM, which does table-level locking on INSERT.
See this reference in the manual: http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html under the section, MyISAM Notes.
There are a whole lot of good reasons not to use MyISAM. The deciding factor for me is MyISAM's tendency to corrupt data.
Re your comment:
InnoDB does not support the increment-per-group behavior described above. You can make a multi-column primary key, but the error you got is because InnoDB requires that the auto-increment column be the first column in a key of the table (it doesn't strictly have to be the primary key)
Regardless of the position of the auto-increment column in the multi-column key, it only increments when you use it with InnoDB; it does not number entries per distinct value in another column.
To do this with an InnoDB table, you'd have to lock the table explicitly for the duration of the INSERT, to avoid race conditions. You'd do your own SELECT query for the max value in the group you're inserting to. Then insert that value + 1.
Basically, you have to bypass the auto-increment feature and specify values instead of having them automatically generated.
这篇关于每个键值的循环自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
