MySQL - 表中的第二个序列（例如按类别）
我正在努力寻找最有效的方法来做某事，非常感谢您的建议！
我有一个包含以下列的表：
id
category
category_sequence
other_columns
id 是一个自动增量列和主键。类别由用户在 UI 上选择。
我想做的是生成一个category_sequence，它是该类别现有的最高category_sequence + 1。
例如，插入几行：
- 1, 1, 1
- 2, 1, 2
- 3, 2, 1
- 4、1、3
等等。
显然，我可以运行一个查询来提取最高的category_sequence，向其中添加一个，然后运行我的插入语句。但是有没有一种更有效的方法（一些插入将处于相当大的循环中，因此任何可以加快速度并防止交叉的方法都会很棒）。
提前致谢， 凯夫
I'm trying to find the most efficient way to do something and would appreciate your advice!
I have a table with the following columns:
id
category
category_sequence
other_columns
id is an auto-increment column and the primary key. The category is selected by the user on the UI.
What I'd like to do is generate a category_sequence, which is the highest existing category_sequence for that category + 1.
For example, inserting a few rows would like like:
- 1, 1, 1
- 2, 1, 2
- 3, 2, 1
- 4, 1, 3
and so on.
Obviously I could run a query to extract the highest category_sequence, add one to it, and then run my insert statement. But is there a much more efficient way (some of the inserts will be in fairly big loops, so anything to speed it up, and prevent crossover, would be great).
Thanks in advance,
Kev
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以尝试，
如果它是该类别的第一条记录，您可能还必须将其包装在 case 语句中。我没有 mysql 来测试它
you can try
You might have to wrap it in a case statement as well in the case that it is the first record for that category. I don't have mysql to test it on
假设您有一个类别引用的“类别”表，则那里可能有一个 next_sequence 字段。然后在表上有一个插入触发器，用于提取下一个序列号，将其放入category_sequence 列中，并递增next_sequence 字段（必须在单个事务中完成所有操作）。
Assuming you have a "Category" table to which category refers to, you could have a next_sequence field on there. Then have an insert trigger on your table that pulls the next sequence number, puts it into the category_sequence column, and increments the next_sequence field (would have to all be done in a single transaction).
您可以让数据库通过将 auto_increment 字段更改为category_sequence 来完成此操作，然后将主键设置为category_sequence。然后您可以删除 id 字段，因为它不会执行您想要的操作。
然后，MySql 将按照您描述的方式精确地增加category_sequence 字段。
You can have the database do it by changing your auto_increment field to be category_sequence, then make your primary key category+category_sequence. You can then drop the id field since it's not going to do what you wanted it to.
MySql will then increment the category_sequence field exactly the way you describe.