如何插入一列以使其根据另一列自动增量

Question

有表：

ID	类型	serial_no
1	苹果	1
2	香蕉	1
3	香蕉	2
4	苹果	2
5	水	1

我想要serial_no可以为每个类型自动增量

我尝试了

INSERT INTO MY_TBALE
(type, serial_no)
VALUES
(
apple, 
(SELECT (COALESCE(MAX('type'), 0)+1 FROM MY_TABLE T1 WHERE type=apple)
)

，但似乎在多线程系统上不起作用。 我在相同的type上获得了一些重复serial_no。