每个主键值的某种“不同的自动增量索引” [英] Some sort of “different auto-increment indexes” per a primary key values

问题描述

我有一个表格，其中包含 ID （主键带自动递增）， uid （例如键指向用户ID）

我想让 id 对每个 uid使用不同的自动递增键

因此，我将添加一个包含 uid 10和 id 字段的条目由于 uid 中之前没有值 10 的条目，此条目将有 1 。我将添加一个新的 uid 4，其 ID 将是 3 ，因为我已经有两个 uid 4 。

...很明显的解释，但我试图作为解释清楚，我可以证明这个想法...很清楚。 p>

1. 什么SQL引擎可以提供这样的功能？ （非基于Microsoft / Oracle）


2. 如果没有，我如何最好地复制它？


3. 如果你知道一个非SQL数据库引擎提供这样的功能，名称




感谢。

解决方案

MySQL的MyISAM引擎可以做到这一点。请参见使用AUTO_INCREMENT 部分中的手册：




对于MyISAM表，您可以在多列索引中的辅助列上指定AUTO_INCREMENT。在这种情况下，AUTO_INCREMENT列的生成值计算为MAX（auto_increment_column）+ 1 WHERE prefix = given-prefix。




这段文字在段落之后，显示一个例子。





MySQL中的InnoDB引擎不支持此功能，这是不幸的，因为在几乎所有情况下都更好使用InnoDB。





您不能使用触发器（或限制为事务范围的任何SQL语句）在INSERT上不使用锁定表来模拟此行为。考虑这个动作顺序：



1. 马里奥开始交易并为使用者4插入新列。


2. Bill开始交易并为用户4插入新行。


3. 马里奥的会话触发一个触发器，为用户4计算MAX（id）+1。


4. Bill的会话触发一个触发器来计算MAX（id）。


5. 马里奥的会话尝试完成其INSERT，但是行（userid = 4） ，id = 3）现在存在，因此马里奥得到主键冲突。



一般来说，执行这些步骤而不进行某种同步。





这些解决方案是：







• 获取独占表锁。在尝试INSERT之前，锁定表。这是必要的，以防止并发INSERT创建竞态条件，如上例所示。有必要锁定整个表，因为你试图限制INSERT没有特定的行锁（如果你试图通过UPDATE来管理给定行的访问，你可以锁定特定的行）。但是锁定表格会导致对表格的访问变成连续的，这限制了您的处理量。




• id号码以不会被两个并发事务隐藏的方式。顺便说一句，这是AUTO_INCREMENT的作用。两个并发会话将获得唯一的id值，无论它们的执行顺序或提交顺序如何。但是跟踪每个用户ID的最后生成的ID需要访问数据库或重复的数据存储。例如，每个用户标识 memcached 键，可以原子性递增。







相对容易确保插入获得唯一值。但很难确保他们会获得连续的序数值。还请考虑：






• 如果在事务中插入INSERT但后退回，会发生什么？您已在该交易中分配了ID值3，然后我分配了值4，因此如果您回退，我提交，现在有一个缺口。


• 如果INSERT失败会发生什么的其他约束在表上（例如另一列是NOT NULL）？


• 如果您删除了某行，是否需要为同一个用户ID重新编号所有以下行？如果您使用该解决方案，对您的memcached条目有什么作用？





I have got a table which has an id (primary key with auto increment), uid (key refering to users id for example) and something else which for my question won’t matter.

I want to make, lets call it, different auto-increment keys on id for each uid entry.

So, I will add an entry with uid 10, and the id field for this entry will have a 1 because there were no previous entries with a value of 10 in uid. I will add a new one with uid 4 and its id will be 3 because I there were already two entried with uid 4.

...Very obvious explanation, but I am trying to be as explainative an clear as I can to demonstrate the idea... clearly.

1. What SQL engine can provide such a functionality natively? (non Microsoft/Oracle based)
2. If there is none, how could I best replicate it? Triggers perhaps?
3. Does this functionality have a more suitable name?
4. In case you know about a non SQL database engine providing such a functioality, name it anyway, I am curious.

Thanks.

解决方案

MySQL's MyISAM engine can do this. See their manual, in section Using AUTO_INCREMENT:

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

The docs go on after that paragraph, showing an example.

The InnoDB engine in MySQL does not support this feature, which is unfortunate because it's better to use InnoDB in almost all cases.

You can't emulate this behavior using triggers (or any SQL statements limited to transaction scope) without locking tables on INSERT. Consider this sequence of actions:

1. Mario starts transaction and inserts a new row for user 4.
2. Bill starts transaction and inserts a new row for user 4.
3. Mario's session fires a trigger to computes MAX(id)+1 for user 4. You get 3.
4. Bill's session fires a trigger to compute MAX(id). I get 3.
5. Bill's session finishes his INSERT and commits.
6. Mario's session tries to finish his INSERT, but the row with (userid=4, id=3) now exists, so Mario gets a primary key conflict.

In general, you can't control the order of execution of these steps without some kind of synchronization.

The solutions to this are either:

• Get an exclusive table lock. Before trying an INSERT, lock the table. This is necessary to prevent concurrent INSERTs from creating a race condition like in the example above. It's necessary to lock the whole table, since you're trying to restrict INSERT there's no specific row to lock (if you were trying to govern access to a given row with UPDATE, you could lock just the specific row). But locking the table causes access to the table to become serial, which limits your throughput.

• Do it outside transaction scope. Generate the id number in a way that won't be hidden from two concurrent transactions. By the way, this is what AUTO_INCREMENT does. Two concurrent sessions will each get a unique id value, regardless of their order of execution or order of commit. But tracking the last generated id per userid requires access to the database, or a duplicate data store. For example, a memcached key per userid, which can be incremented atomically.

It's relatively easy to ensure that inserts get unique values. But it's hard to ensure they will get consecutive ordinal values. Also consider:

• What happens if you INSERT in a transaction but then roll back? You've allocated id value 3 in that transaction, and then I allocated value 4, so if you roll back and I commit, now there's a gap.
• What happens if an INSERT fails because of other constraints on the table (e.g. another column is NOT NULL)? You could get gaps this way too.
• If you ever DELETE a row, do you need to renumber all the following rows for the same userid? What does that do to your memcached entries if you use that solution?

这篇关于每个主键值的某种“不同的自动增量索引”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！