如果发现重复项，MYSQL 查询是否更新字段？

Question

这是我的问题。我得到了一张表含义

ID - Meaning1 - red car2 - cat man3 - red car4 - ontime5 - red car....

I want to make the colum Meaning become Unique. So i want to build a query to found all the duplicates & for each of duplicate, the system should append [number] to make the cell become unique.

So after running that query, the result should be:

ID - Meaning1 - red car2 - cat man3 - red car [2]4 - ontime5 - red car [3]....

The table is pretty long about 100K rows. The query could be similar to this query

Update Table Meaning set meaning=concat(meaning,"1") 
where meaning in (select meaning from Meaning group by meaning having count(meaning>1)

那么解决问题的查询是什么？

似乎我们必须使用 set variable 来检查每一行？

Answer 1

第一步:创建临时表

CREATE TABLE TMP (id int, meaning varchar (2));

第二步:准备查询并插入临时表

insert into tmp 
SELECT id, 
CASE WHEN cnt =0 theN meaning ELSE concat(meaning,'[',cnt+1,']') END AS meaning 

FROM
(
SELECT t1.id, t1.meaning, (

SELECT COUNT( t.id ) 
FROM test t
where  t.meaning=t1.meaning 
and t.id<t1.id
) as cnt
FROM test t1
)TMP

第三步

truncate table test

第四步:迁移到原始

insert into test select * from tmp