Mysql : Query to update email with unique random value

Question

I need to anonymize emails in DB, so I try to set a query, but I cannot find a way to generate unique random string for each. What I have so far is :

update candidate set email = (select CONCAT(SUBSTRING(MD5(RAND()) FROM 1 FOR 15) , '@test.fr'));

But obviously the value is not unique, is that possible with a simple query?

I tried solution here : https://harrybailey.com/2015/08/mysql-roughly-random-string-generation-for-updating-rows/ but same result, I got a

Error Code: 1062. Duplicate entry '0417da5fb3d071b9bd10' for key 'email'

Answer 1

You can use UUID

 UPDATE `candidate` SET email = CONCAT(MD5(UUID()),'@test.fr');

and if you want exactly 15 characters

 UPDATE candidate SET email=CONCAT(SUBSTRING(MD5(UUID()),1,15) , '@test.fr');