修改数据，然后插入，如果它已经存在，使用mysql - Modify data and then insert, if it is exist already using mysql

Modify data and then insert, if it is exist already using mysql

我正在使用mysql和PHP。

我有一个像

这样的表

name    age     department
----    ---     ----------
aaa     10      1
bbb     25      1
ccc     25      2

当我插入下一行name=aaa时，我需要将其存储为aaa(1)。

当我再次插入name=aaa的行时，我需要将其存储为aaa(2)。

结果应该像

name    age     department
----    ---     ----------
aaa     10      1
bbb     25      1
ccc     25      2
aaa(1)  36      2
aaa(2)  27      3

是否可以通过mysql实现?

如果需要，我们还可以创建更多的列

drop table if exists t1;
create table t1 (name text,   age int,      department int);
insert into t1 values
("aaa", 10, 1),
("aaa(1)", 10, 1),
("bbb", 25, 1),
("ccc", 25, 2);
insert into t1 values(
  (select if(c, concat(name, "(", c, ')'), name) from 
    (select n.name, count(*) c from 
      (select @n:="aaa" name) n 
      join 
      (select name from t1) t 
      on t.name=n.name or t.name like concat(n.name, '(%')) ttt), 
  1, 1);
select * from t1

sqlfiddle演示

在MySQL中可以是这样的:

INSERT INTO `staff` SELECT concat('aaa',count(*)) as name, '36' as age,'2' as department FROM `staff` WHERE name='aaa'