因为本身存在 le.com 邮件域 和 letv.cn 邮件域
(db_admin@localhost) [ump]> select substring_index(username, '@', 1) as pre_name, username, count(id) as count from ump_user_info where status = 0 group by pre_name having count > 1 ;
+-------------+----------------------+-------+
| pre_name | username | count |
+-------------+----------------------+-------+
| chrysellen | chrysellen@le.com. | 2 |
| crystalngai | crystalngai@letv.com | 2 |
| dorothyyuen | dorothyyuen@letv.com | 2 |
| hejing | hejing@le.com | 2 |
| johnnyyoung | johnnyyoung@letv.com | 2 |
| liuyuelan | liuyuelan@e.com | 2 |
| lixinyan1 | lixinyan1@le.conm | 2 |
| wangquan | wangquan@le.com | 2 |
| zhoujun | zhoujun@yongche.com | 2 |
+-------------+----------------------+-------+
9 rows in set (0.00 sec)
(db_admin@localhost) [ump]> select * from ump_user_info limit 1 \G
*************************** 1. row ***************************
id: 1
staff_no: L017239
username: liusheng1
nickname: 刘声
nickname_eng:
email: liusheng1@le.com
gender: M
phone:
job_title:
supervisor_name:
last_modify_date: NULL
le_country_254:
created_at: 2016-10-17 18:20:30
updated_at: 2016-10-17 18:20:30
type: 1
status: 1
deleted_at: 0000-00-00 00:00:00
1 row in set (0.00 sec)
(db_admin@localhost) [ump]> desc ump_user_info ;
+------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| staff_no | varchar(32) | NO | | | |
| username | varchar(255) | NO | MUL | | |
| nickname | varchar(255) | NO | | | |
| nickname_eng | varchar(255) | NO | | | |
| email | varchar(255) | NO | MUL | | |
| gender | varchar(255) | YES | | NULL | |
| phone | varchar(18) | YES | | | |
| job_title | varchar(255) | YES | | | |
| supervisor_name | varchar(255) | YES | | | |
| last_modify_date | timestamp | YES | | NULL | |
| le_country_254 | varchar(255) | YES | | | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | |
| updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
| type | tinyint(4) | NO | | 1 | |
| status | tinyint(4) | NO | | 1 | |
| deleted_at | datetime | NO | | 0000-00-00 00:00:00 | |
+------------------+------------------+------+-----+---------------------+----------------+
17 rows in set (0.00 sec)
(db_admin@localhost) [ump]>