Mysql-как обновить “domain.com” в “address@domain.com”
В моей базе данных у меня есть много пользователей, которые написали их адрес электронной почты c орфографическими ошибками. Это в свою очередь заставляет мой постфикс возвращать много писем при отправке новостной рассылки.
Формы включают (но не ограничены), "yaho.com", "yahho .com" и т.д.
Очень раздражает!
Таким образом, я пытался обновить, они записывают к правильному значению.
После выполнения select email from users where email like '%@yaho%' and email not like '%yahoo%'; и получая список, я застреваю, потому что я не знаю, как обновить только yaho часть. Мне нужно имя пользователя, которое оставят неповрежденным.
Таким образом, я думал, что буду просто выводить базу данных и использовать энергию для замены, но я не могу выйти @ символ..
BTW, как я выбираю все написанные заглавными буквами адреса электронной почты? select upper(email) from users; просто преобразовал бы все в ЗАГЛАВНЫЕ БУКВЫ, тогда как я просто должен был узнать already-written-in-CAPS письма.
4 ответа
Вы можете попробовать что-то вроде следующего:
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 ('test1@yahoo.com');
INSERT INTO users VALUES ('test2@yaho.com');
INSERT INTO users VALUES ('test3@yahoo.com');
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 |
+-----------------+
| test1@yahoo.com |
| test2@yahoo.com |
| test3@yahoo.com |
+-----------------+
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 ('TEST4@YAHOO.COM');
SELECT * FROM users;
+-----------------+
| email |
+-----------------+
| test1@yahoo.com |
| test2@yahoo.com |
| test3@yahoo.com |
| TEST4@YAHOO.COM |
+-----------------+
4 rows in set (0.00 sec)
SELECT * FROM users WHERE email COLLATE latin1_general_cs = UPPER(email);
+-----------------+
| email |
+-----------------+
| TEST4@YAHOO.COM |
+-----------------+
1 row in set (0.00 sec)
Для первого вопроса я бы выбрал что-то вроде
UPDATE users
SET email = INSERT(email,INSTR(email,'@'), LENGTH(email), '@yahoo.com')
WHERE email LIKE '%@yaho.com'
Просто для большей полноты, это многобайтово безопасно, хотя я использовал LENGTH . Все, что нужно, - это чтобы третий аргумент INSERT был не меньше конца подстроки.
Ответ Syntactic о поиске письма с заглавными буквами - хороший ответ. Возможно, выполняется немного быстрее, хотя вы, вероятно, не заметите разницы, это
SELECT email FROM users WHERE BINARY(email) NOT REGEXP '[a-z]'
Обновление: BINARY (электронная почта) необходимо для принудительного сопоставления с учетом регистра.
Чтобы ответить на ваш второй вопрос (о поиске писем, написанных заглавными буквами), может быть полезно что-то вроде этого:
select email from users where upper(email) = email
(Простите, если синтаксис не совсем корректен, поскольку я привык к DB2. Идея заключается в том, чтобы сравнить прямой адрес электронной почты с версией, написанной заглавными буквами.)
.Вы можете попробовать использовать INSTR вместе с SUBSTR или LEFT, чтобы получить часть перед символом "@", возможно.
Что-то вроде SELECT LEFT("foo@yaho.com",INSTR("foo@yaho.com","@")-1);, кажется, работает.