使用InnoDB或替代方法按组进行MySQL AUTO_INCREMENT [英] MySQL AUTO_INCREMENT by group using InnoDB or alternatives

问题描述

我正在使用MySQL数据库设计Web应用程序，而我却陷入了数据库设计的优点之一.

I am designing a web application using a MySQL database, and I am stuck on one of the fine points of my database design.

以下是我想使用InnoDB引擎存储的数据类型的示例:

Here's an example of the type of data I would like to store using the InnoDB engine:

user_id   account_id
1         1
1         2
1         3
2         1
2         2
3         1

从以上内容可以明显看出，我希望account_id为每个用户自动递增.这是我遇到的一些问题:

As is clear from the above, I would like the account_id to auto increment for each individual user. Here are some problems I have encountered:

1. 我确实知道可以自动使用MyISAM引擎，但是经过阅读后，我意识到当更新发生时，该引擎将锁定整个表，而不仅仅是一排.这对我不好.另外，MyISAM不强制执行参照完整性，我非常希望保留.

1. I do know that to do this automatically I could use the MyISAM engine, but after I have done some reading, I have come to the realization that when updates happen, this engine will lock the whole table instead of just one row. This is no good to me. Also, MyISAM does not enforce referential integrity, which I would highly like to keep.

我已经阅读了其他人针对此问题的解决方案，并且他们使用了触发器.我当然可以使用触发器，但是我并不喜欢触发器，因为它们比仅一张表更难维护.

I have read other people's solutions to this problem, and they have used a trigger. I could, of course, use a trigger, but I am not very fond of triggers, as they are more difficult to maintain than just one table.

我可能会使用一个序列，但是我认为为每个用户维护一个单独的序列是很丑陋的.

I could probably use a sequence, but I think that would be ugly to maintain a separate sequence for every user.

我可以简单地使用唯一的AUTO_INCREMENT而无需考虑user_id，这会很好地工作.我对此不满意有两件事:1.这样，我的smallint可能会增长为int或bigint，这将需要更多的存储空间.我知道存储空间很便宜，但我仍想将其最小化. 2.按组自动递增会更清洁.

I could simply use a unique AUTO_INCREMENT without any regard to the user_id, which would work very well. There are two things about this I do not like: 1. This way, my smallint would potentially grow to int or bigint, which would require a lot MORE storage space. I know that storage space is cheap, but I would still like to minimize it. 2. It would be much cleaner to auto increment by group.

我想对已经遇到并成功解决此问题的那些用户提供一些反馈/意见.也许，还有我没有想到的另一种解决方案.也许，还有另一种我从未想到过的方式来组织我的桌子.

I would like to get some feedback/opinions of those users that have encountered and successfully solved this problem. Perhaps, there is another solution that I haven't thought of. Perhaps, there is another way to organize my table that I haven't thought of.

推荐答案

使用简单的AUTO_INCREMENT并忽略存储:

Use a simple AUTO_INCREMENT and ignore the storage:

• 如果您将规模扩大到一百万个用户，每个用户拥有1000个帐户，您将 需要大约3MB的额外存储空间.
• 单字段主索引(主要在实际存储中排序)的性能优势将比那一点存储价值更多.

• If you scale to a million users, each having 1000 accounts, you will need approx. 3MB of additional storage.
• The performance benefit of a single-field primary index (mostly sorted in real storage!) will be worth much more, than that bit of storage

这篇关于使用InnoDB或替代方法按组进行MySQL AUTO_INCREMENT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！