Mysql-如何更新“domain.com”在“address@domain.com”中

Question

在我的数据库中，有很多用户的电子邮件地址拼写错误。这反过来又导致我的 postfix 在发送时事通讯时退回大量邮件。
形式包括（但不限于）“yaho.com”、“yahho.com”等
很烦人！

所以我一直在尝试将这些记录更新为正确的值。
执行 select email from users where email like '%@yaho%' and email not like '%yahoo%'; 并获取列表后，我陷入困境，因为我不知道如何仅更新yaho 部分。我需要完整保留用户名。

所以我想我应该转储数据库并使用 vim 替换，但我无法转义 @ 符号。

顺便说一句，如何选择所有用大写字母写的电子邮件地址？ select upper(email) from users; 只会将所有内容转换为大写字母，而我只需要找出已经写入大写字母的邮件。

Answer 1

您可能想尝试如下操作：

UPDATE   users
SET      email = CONCAT(LEFT(email, INSTR(email, '@')), 'yahoo.com')
WHERE    email LIKE '%@yaho.com%';

测试用例：

CREATE TABLE users (email varchar(50));

INSERT INTO users VALUES ('[email protected]');
INSERT INTO users VALUES ('[email protected]');
INSERT INTO users VALUES ('[email protected]');


UPDATE   users
SET      email = CONCAT(LEFT(email, INSTR(email, '@')), 'yahoo.com')
WHERE    email LIKE '%@yaho.com%';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


SELECT * FROM users;
+-----------------+
| email           |
+-----------------+
| [email protected] |
| [email protected] |
| [email protected] |
+-----------------+
3 rows in set (0.00 sec)

要回答第二个问题，您可能需要 使用区分大小写的排序规则，例如 latin1_general_cs：

SELECT * FROM users WHERE email COLLATE latin1_general_cs = UPPER(email);

测试用例：

INSERT INTO users VALUES ('[email protected]');


SELECT * FROM users;   
+-----------------+
| email           |
+-----------------+
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
+-----------------+
4 rows in set (0.00 sec)


SELECT * FROM users WHERE email COLLATE latin1_general_cs = UPPER(email);
+-----------------+
| email           |
+-----------------+
| [email protected] |
+-----------------+
1 row in set (0.00 sec)

Answer 2

UPDATE users SET email = REPLACE( email, SUBSTRING_INDEX( email,  '@', -1 ) ,  CONCAT(user_id, 'domain.com' ) ) WHERE [MYSQL CONDITION];

将实时电子邮件地址更新为测试电子邮件地址

Answer 3

为了解决你的第二个问题（关于查找大写字母写的电子邮件），这样的事情可能会有所帮助：（

select email from users where upper(email) = email

如果语法不完全正确，请原谅我，因为我习惯了 DB2。这个想法是将直接电子邮件地址与大写版本。）

Answer 4

UPDATE contacts SET email = REPLACE(email, SUBSTRING_INDEX(email, '@', -1), 'domain.com')

Answer 5

您可以尝试将 INSTR 与 SUBSTR 或 LEFT 一起使用来获取“@”符号之前的部分。

像 SELECT LEFT("[email protected]",INSTR ("[电子邮件受保护]","@")-1); 似乎有效。

Answer 6

对于第一个问题，我会选择类似的东西

UPDATE users
SET email = INSERT(email,INSTR(email,'@'), LENGTH(email), '@yahoo.com')
WHERE email LIKE '%@yaho.com'

只是为了彻底，这是多字节安全的，即使我使用了LENGTH。所需要的只是 INSERT 的第三个参数至少与子字符串的末尾一样大。

语法找到全大写电子邮件的答案是一个很好的答案。尽管您可能不会注意到差异，但执行速度可能稍快一些，但

SELECT email FROM users WHERE BINARY(email) NOT REGEXP '[a-z]'

需要更新：BINARY(email) 来强制区分大小写的匹配。