如果两个表存在外键连接,可根据外键对两个表进行左连接,这时候外键相对应的其他列如果有NULL值,代表这两个表可能存在不同步的现象,可删除相应的值使2个表同步。
操作实例:
mysql> desc branch;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| bid | int(4) | NO | PRI | | |
| cid | int(3) | NO | | | |
| bdesc | text | NO | | | |
| bloc | varchar(25) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc client;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| cid | int(3) | NO | PRI | NULL | auto_increment |
| cname | text | NO | | | |
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| cid | int(3) | NO | PRI | NULL | auto_increment |
| cname | text | NO | | | |
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> select client.cid,client.cname,branch.bid,branch.bdesc from client left join branch using (cid);
+-----+-------+------+---------------+
| cid | cname | bid | bdesc |
+-----+-------+------+---------------+
| 101 | WANG | 1011 | cororate hq |
| 101 | WANG | 1012 | cDDororate hq |
| 101 | WANG | 1013 | DDrate hq |
| 102 | JV | NULL | NULL |
| 103 | dJV | 1015 | DDratDDhq |
| 103 | dJV | 1016 | DDratDDhDq |
| 104 | JAMES | 1014 | DDratDDhq |
| 105 | TOMES | NULL | NULL |
+-----+-------+------+---------------+
8 rows in set (0.00 sec)
+-----+-------+------+---------------+
| cid | cname | bid | bdesc |
+-----+-------+------+---------------+
| 101 | WANG | 1011 | cororate hq |
| 101 | WANG | 1012 | cDDororate hq |
| 101 | WANG | 1013 | DDrate hq |
| 102 | JV | NULL | NULL |
| 103 | dJV | 1015 | DDratDDhq |
| 103 | dJV | 1016 | DDratDDhDq |
| 104 | JAMES | 1014 | DDratDDhq |
| 105 | TOMES | NULL | NULL |
+-----+-------+------+---------------+
8 rows in set (0.00 sec)
mysql> delete from client where cid in(102,105);
Query OK, 2 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
mysql> select client.cid,client.cname,branch.bid,branch.bdesc from client left join branch using (cid);
+-----+-------+------+---------------+
| cid | cname | bid | bdesc |
+-----+-------+------+---------------+
| 101 | WANG | 1011 | cororate hq |
| 101 | WANG | 1012 | cDDororate hq |
| 101 | WANG | 1013 | DDrate hq |
| 103 | dJV | 1015 | DDratDDhq |
| 103 | dJV | 1016 | DDratDDhDq |
| 104 | JAMES | 1014 | DDratDDhq |
+-----+-------+------+---------------+
6 rows in set (0.00 sec)
+-----+-------+------+---------------+
| cid | cname | bid | bdesc |
+-----+-------+------+---------------+
| 101 | WANG | 1011 | cororate hq |
| 101 | WANG | 1012 | cDDororate hq |
| 101 | WANG | 1013 | DDrate hq |
| 103 | dJV | 1015 | DDratDDhq |
| 103 | dJV | 1016 | DDratDDhDq |
| 104 | JAMES | 1014 | DDratDDhq |
+-----+-------+------+---------------+
6 rows in set (0.00 sec)
转载于:https://blog.51cto.com/dadloveu/197238