今天碰到一个MySQL需要跨库两个表联合查询的问题，经过测试验证，已经能实现，留底备查。
环境前提条件：操作MySQL的user有db1和db2相同的帐号权限，且db1和db2在同一个服务器上！
现有两个库db1和db2，db1有一个users表，db2有一个是members。
users的主要字段有
ID user_id user_name email
1 101 user01 user1@xx.com
2 102 user02 user2@xx.com
3 103 user03 user3@xx.com
SQL数据结构：
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`user_id` int(8) unsigned NOT NULL DEFAULT ‘0’,
`user_name` varchar(32) CHARACTER SET utf8 NOT NULL,
`email` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;INSERT INTO `users` VALUES (‘1’, ‘101’, ‘user01’, ‘user01@xx.com’);
INSERT INTO `users` VALUES (‘2’, ‘102’, ‘user02’, ‘user02@xx.com’);
INSERT INTO `users` VALUES (‘3’, ‘103’, ‘user03’, ‘user03@xx.com’);
members主要字段有
ID member_id uid phone
1 1 101 13812345671
2 2 102 13812345672
3 3 103 13812345673
4 4 104 13812345674
5 5 105 13812345675
SQL数据结构：
DROP TABLE IF EXISTS `members`;
CREATE TABLE `members` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`member_id` int(8) NOT NULL,
`uid` int(8) unsigned NOT NULL,
`phone` varchar(12) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `members` VALUES (‘1’, ‘1’, ‘101’, ‘13812345671’);
INSERT INTO `members` VALUES (‘2’, ‘2’, ‘102’, ‘13812345672’);
INSERT INTO `members` VALUES (‘3’, ‘3’, ‘103’, ‘13812345673’);
INSERT INTO `members` VALUES (‘4’, ‘4’, ‘104’, ‘13812345674’);
INSERT INTO `members` VALUES (‘5’, ‘5’, ‘105’, ‘13812345675’);
需求：要得到users表中关联结合members表所有相关信息
SQL语句如下：
SELECT * FROM db1.users LEFT JOIN db2.members ON db1.users.user_id=db2.members.uid WHERE 1 LIMIT 0,10;
示例代码：
<?php
$conn1 = mysql_connect(“127.0.0.1”, “root”, “root”, “db1”);
mysql_select_db(“db1”, $conn1);
$conn2 = mysql_connect(“127.0.0.1”, “root”, “root”, “db2”);
mysql_select_db(“db2”, $conn2);$sql1 = “SELECT user_id,user_name,email FROM users LIMIT 0,10″;
mysql_query(‘SET NAMES utf8’);
$query = mysql_query($sql1, $conn1);
while($row = mysql_fetch_array($query)) {
echo $row[‘user_id’].”：”.$row[‘user_name’].” “.$row[’email’].”<br>”;
}
echo “<hr/>”;$sql2 = “SELECT member_id,uid,phone FROM members LIMIT 0,10″;
$query = mysql_query($sql2, $conn2);
while($row = mysql_fetch_array($query)) {
echo $row[‘member_id’].”：”.$row[‘uid’].” “.$row[‘phone’].”<br>”;
}
echo “<hr/>”;$sql = “SELECT * FROM db1.users AS A INNER JOIN db2.members AS B ON A.user_id=B.uid LIMIT 0,10″;
mysql_query(‘SET NAMES utf8’);
// 执行查询
$result = mysql_query($sql);
// 下面显示了实际发送给 MySQL 的查询，以及出现的错误。这对调试很有帮助。
if (!$result) {
$message = ‘Invalid query: ‘.mysql_error().”<br>”;
$message .= ‘SQL query: ‘ . $sql;
die($message);
}
// 结果的使用
while($row = mysql_fetch_array($result)) {
echo $row[‘user_id’].”：”.$row[‘user_name’].” “.$row[’email’].” “.$row[‘phone’].”<br>”;
}?>
注：两台不同MySQL服务器上还没法做到跨服务器联合查询，也许是没法共享内存吧，目前只能做实时同步到同一台服务器上做跨库联合查询。希望以上经验对各位朋友的开发技术工作有所帮助。
评论