I have table users where I have column email. Some of email addresses are NULL and I would like to update these rows with some fake email address but this address must be unique (e.g. testX@example.com where X will be iterated number). Could you help me how can I prepare stored procedure which will update these email addresses?
Thanks.
我有表用户，我有列电子邮件。一些电子邮件地址是NULL，我想用一些假电子邮件地址更新这些行，但这个地址必须是唯一的（例如testX@example.com，其中X将是迭代编号）。你能帮我解决一下如何准备更新这些电子邮件地址的存储过程吗？谢谢。
2 个解决方案
#1
1
try this:
尝试这个：
declare v_i int default 1;
declare v_rowcount int;
select count(1)
into v_rowcount
from users
where email is null;
while (v_i <= v_rowcount)
do
update users
set email = concat('test', v_i, '@example.com')
where email is null
limit 1;
set v_i = v_i + 1;
end while;
#2
0
You can achieve this inside (or outside) a stored procedure as well, simply by using an all-encompassing query:
您也可以通过使用无所不包的查询来实现存储过程内部（或外部）：
Table used for examples:
用于示例的表：
CREATE TABLE emailAddrs(email VARCHAR(255));
...
+------------+
| email |
+------------+
| NULL |
| NULL |
| NULL |
| some@email |
| NULL |
| NULL |
+------------+
Example: Using a session variable:
示例：使用会话变量：
SET @i := 0;
UPDATE emailAddrs SET email = CONCAT(@i := @i + 1, '@example.com') WHERE email IS NULL;
Gives:
得到：
+----------------+
| email |
+----------------+
| 7@example.com |
| 8@example.com |
| 9@example.com |
| some@email |
| 10@example.com |
| 11@example.com |
+----------------+
Example: Using random and universally-unique data generation:
示例：使用随机和通用唯一数据生成：
UPDATE emailAddrs SET email = CONCAT(SHA(UUID()), '@example.com') WHERE email IS NULL;
Gives:
得到：
+------------------------------------------------------+
| email |
+------------------------------------------------------+
| aac9e54bbc2548058460041da06271f6c5698ab2@example.com |
| 333053729d4879fd61c86c44f7a6bb78441b8436@example.com |
| e93540b16d908840c194890ef6bf4537286d0c8d@example.com |
| some@email |
| af104449c8f45bd30d23085cc6f63607fa3bab0b@example.com |
| d9df7bd9a8fb579b7ee9053d9f2520e58b46b808@example.com |
+------------------------------------------------------+
#1
1
try this:
尝试这个：
declare v_i int default 1;
declare v_rowcount int;
select count(1)
into v_rowcount
from users
where email is null;
while (v_i <= v_rowcount)
do
update users
set email = concat('test', v_i, '@example.com')
where email is null
limit 1;
set v_i = v_i + 1;
end while;
#2
0
You can achieve this inside (or outside) a stored procedure as well, simply by using an all-encompassing query:
您也可以通过使用无所不包的查询来实现存储过程内部（或外部）：
Table used for examples:
用于示例的表：
CREATE TABLE emailAddrs(email VARCHAR(255));
...
+------------+
| email |
+------------+
| NULL |
| NULL |
| NULL |
| some@email |
| NULL |
| NULL |
+------------+
Example: Using a session variable:
示例：使用会话变量：
SET @i := 0;
UPDATE emailAddrs SET email = CONCAT(@i := @i + 1, '@example.com') WHERE email IS NULL;
Gives:
得到：
+----------------+
| email |
+----------------+
| 7@example.com |
| 8@example.com |
| 9@example.com |
| some@email |
| 10@example.com |
| 11@example.com |
+----------------+
Example: Using random and universally-unique data generation:
示例：使用随机和通用唯一数据生成：
UPDATE emailAddrs SET email = CONCAT(SHA(UUID()), '@example.com') WHERE email IS NULL;
Gives:
得到：
+------------------------------------------------------+
| email |
+------------------------------------------------------+
| aac9e54bbc2548058460041da06271f6c5698ab2@example.com |
| 333053729d4879fd61c86c44f7a6bb78441b8436@example.com |
| e93540b16d908840c194890ef6bf4537286d0c8d@example.com |
| some@email |
| af104449c8f45bd30d23085cc6f63607fa3bab0b@example.com |
| d9df7bd9a8fb579b7ee9053d9f2520e58b46b808@example.com |
+------------------------------------------------------+