MySQL - second sequence in table (e.g. by category)

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 us开发者_开发知识库er 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

you can try

    insert into mytable
    select "categoryselected",coalesce(max(category_sequence),0)+1 from mytable where category = "categoryselected"

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

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).

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.