友荐云推荐

今天碰到一个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服务器上还没法做到跨服务器联合查询,也许是没法共享内存吧,目前只能做实时同步到同一台服务器上做跨库联合查询。希望以上经验对各位朋友的开发技术工作有所帮助。