mysql 添加自动增量_如何根据MySQL中的组添加自动增量ID

Here is the format of the table:

indexer group name id

1 abc a

2 abc b

3 xyz c

4 abc e

5 xyz d

Now i want it to be like,

indexer group name id

1 abc a 1

2 abc b 2

3 xyz c 1

4 abc e 3

5 xyz d 2

"id" should auto increment according to "group"

解决方案

Try this:

update yourtable t1

join (

select

tt.indexer, @rowno := if(@grp = `group`, @rowno + 1, 1) as id, @grp := `group`

from (select * from yourtable order by `group`, indexer) tt

cross join (select @rowno := 0, @grp := null) t

) t2

on t1.indexer = t2.indexer

set t1.id = t2.id

Edited:

If you want to insert a new row, you have to do it like this:

insert into yourtable

select '$indexer', '$group', '$name', coalesce(max(id), 0) + 1

from yourtable

where name = '$name'