工作中遇到了一个问题, 邮件系统群发失败, 后来经过排查查找到了原因
原来是因为 mysql 中的两张表的关联字段竟然不一致,
表 A
- mysql> desc rm_user_router;
- +------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+-------+
- | CORPID | int(10) | NO | PRI | NULL | |
- | LOGINNAME | varchar(60) | NO | PRI | NULL | |
- | UIN | int(10) | NO | MUL | NULL | |
- | SPID | int(10) | NO | | NULL | |
- | MIID | int(10) | NO | | NULL | |
- | ASID | int(10) | NO | | 0 | |
- | FLAG | int(1) | NO | | 0 | |
- | REGISTERID | varchar(60) | NO | | NULL | |
- | STATUS | int(2) | NO | | NULL | |
- | RES1 | int(10) | NO | | 0 | |
- | RES2 | int(10) | NO | | 0 | |
- | CREATETIME | datetime | NO | | NULL | |
- +------------+-------------+------+-----+---------+-------+
表 B
- mysql> desc rm_group_info;
- +----------------+---------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +----------------+---------------+------+-----+---------+-------+
- | UIN | int(10) | NO | PRI | NULL | |
- | CORPID | int(10) | NO | MUL | NULL | |
- | GROUPID | varchar(60) | NO | | NULL | |
- | GROUPNAME | varchar(32) | YES | | NULL | |
- | MAXMEMBERNUM | int(10) | YES | | 0 | |
- | CURMEMBERNUM | int(10) | YES | | 0 | |
- | STATUS | int(2) | YES | | 0 | |
- | FLAG | int(2) | YES | | 0 | |
- | RECVTYPE | int(2) | YES | | 0 | |
- | CREATETIME | datetime | NO | | NULL | |
- | MODIFYTIME | datetime | NO | | NULL | |
- | WHITELIST | varchar(2000) | YES | | NULL | |
- | BLACKLIST | varchar(2000) | YES | | NULL | |
- | join_privilege | int(10) | YES | | 0 | |
- | exit_privilege | int(10) | YES | | 0 | |
- | is_auto | int(1) | NO | | 0 | |
- +----------------+---------------+------+-----+---------+-------+
其中两张表的 UIN 字段竟然不一致, 现在需要解决的是 rm_group_info 表格中的 groupnam 字段的 uin 要和 rm_user_router 表格中的 loginname 字段的 uin 要一致
1. 先备份 2 张表
2. 更新同步数据
update rm_user_router inner join rm_group_info on rm_user_router.loginname=rm_group_info.groupname set rm_group_info.uin=rm_user_router.uin;
来源: http://www.bubuko.com/infodetail-2576835.html