Mysql - как обновить "domain.com" в "[email protected]"

В моей базе данных у меня много пользователей, которые ошибочно написали свой адрес электронной почты. Это, в свою очередь, заставляет мой постфикс отказываться от большого количества писем при отправке информационного бюллетеня.
Формы включают (но не ограничиваются ими) "yaho.com", "yahho.com" и т.д.
Очень раздражает!

Итак, я пытался обновить эти записи до правильного значения.
После выполнения select email from users where email like '%@yaho%' and email not like '%yahoo%'; и получения списка я застрял, потому что не знаю, как обновить только часть yaho. Мне нужно, чтобы имя пользователя оставалось неповрежденным.

Итак, я думал, что просто удалю базу данных и воспользуюсь vim для замены, но я не могу избежать символа @.

Кстати, как выбрать все адреса электронной почты, написанные в CAPS? select upper(email) from users; просто преобразует все в CAPS, тогда как мне просто нужно узнать уже написанные в CAPS сообщения.

Ответ 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)

Ответ 2

Чтобы ответить на второй вопрос (об обнаружении писем, написанных в шапках), возможно, что-то вроде этого:

select email from users where upper(email) = email

(Простите меня, если синтаксис не совсем корректен, поскольку я привык к DB2. Идея состоит в том, чтобы сравнить прямой адрес электронной почты с версией с верхним расположением.)

Ответ 3

Вы можете попробовать использовать INSTR вместе с SUBSTR или LEFT, чтобы, возможно, получить часть перед символом "@".

Кажется, что-то вроде SELECT LEFT("[email protected]",INSTR("[email protected]","@")-1);.

Ответ 4

Для первого вопроса я бы выбрал что-то вроде

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) необходимо для принудительного соответствия регистра.

Ответ 5

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