2个数据库的2个表如何做联合查询? 如题 mysql的 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 一样的,在表前面添加数据库名称 例如select t1.* from db1.t1 as t1 join db2.t2 as t2 on t1.id=t2.id MYSQL目前不支持跨服务器的查询,导入到一个中查询吧,没有链接服务器的概念 还是可以这样,不过您需要链接到另外一个资料库的表格,参见http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#federated-storage-engine如果不能链接,您或者把数据导到1个mysql系统,然后再作! 两个数据库服务器上的则没有办法。目前的MYSQL版本还不支持 database link federated-storage可以连接到其他mysql服务器的资料表的从MySQL 5.1.26开始,默认安装没有启用federated-storage,您可以添加下面的配置到配置文件my.cnf或my.ini启用[mysqld]federated末学在本机安装了两个mysql 端口是3306 3307 测试如下(3307端口的开启了federated)C:\Users\coolwind>mysql -uroot -p -P3306 -h127.0.0.1Enter password: **********mysql> use test;Database changedmysql> drop table if exists `test_table`;Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `test_table` ( -> `id` int(20) NOT NULL AUTO_INCREMENT, -> `name` varchar(32) NOT NULL DEFAULT '', -> `other` int(20) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`), -> KEY `name` (`name`), -> KEY `other_key` (`other`) -> ) DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.02 sec)mysql> insert into test_table(name) values('a'),('b'),('c'),('d');Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from test_table;+----+------+-------+| id | name | other |+----+------+-------+| 1 | a | 0 || 2 | b | 0 || 3 | c | 0 || 4 | d | 0 |+----+------+-------+4 rows in set (0.00 sec)mysql> exitByeC:\Users\coolwind>mysql -uroot -P3307 -h127.0.0.1 -pEnter password:mysql> use test;Database changedmysql> drop table if exists test_table;Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> CREATE TABLE `test_table` ( -> `id` int(20) NOT NULL AUTO_INCREMENT, -> `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', -> `other` int(20) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`), -> KEY `name` (`name`), -> KEY `other_key` (`other`) -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CONNECTION='mysql://root:[email protected]:3306/test/test_table';Query OK, 0 rows affected (0.06 sec)mysql> select * from test_table;+----+------+-------+| id | name | other |+----+------+-------+| 1 | a | 0 || 2 | b | 0 || 3 | c | 0 || 4 | d | 0 |+----+------+-------+4 rows in set (0.01 sec)mysql> drop table if exists test;Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `test` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `age` tinyint(3) unsigned DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.03 sec)mysql> insert into test(age) values(80);Query OK, 1 row affected (0.00 sec)mysql> select * from test as t1 join test_table as t2 on t1.id=t2.id;+----+------+----+------+-------+| id | age | id | name | other |+----+------+----+------+-------+| 1 | 80 | 1 | a | 0 |+----+------+----+------+-------+1 row in set (0.00 sec)mysql> exitBye 楼上 loveflea(coolwind) 的好办法! 用 FEDERATED 引擎。学习 数据库表是只读属性的 using delete 如何使用Mysql一次性读取大量数据 求 SQL 比较差异化 like语句怎样在两个field里查询? 关于替换一个字段中的字符串的问题 100分请mysql高手看看我的 my.cnf配置有问题吗? 有没有办法把mysql库中的某一个表的增量导出到一个数据文件? 请教交手机话费的系统其数据库中的表结构是怎么样的?? 求助 ,一条select查询出多条数据 id自整张 用户明密码分别存两个表 存储过程怎么写好呢 mysql数据库双向复制的疑难问题
select t1.* from db1.t1 as t1 join db2.t2 as t2 on t1.id=t2.id
http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#federated-storage-engine如果不能链接,您或者把数据导到1个mysql系统,然后再作!
目前的MYSQL版本还不支持 database link
federated-storage可以连接到其他mysql服务器的资料表的
从MySQL 5.1.26开始,默认安装没有启用federated-storage,您可以添加下面的配置到配置文件my.cnf或my.ini启用
[mysqld]
federated末学在本机安装了两个mysql 端口是3306 3307 测试如下(3307端口的开启了federated)C:\Users\coolwind>mysql -uroot -p -P3306 -h127.0.0.1
Enter password: **********mysql> use test;
Database changed
mysql> drop table if exists `test_table`;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `test_table` (
-> `id` int(20) NOT NULL AUTO_INCREMENT,
-> `name` varchar(32) NOT NULL DEFAULT '',
-> `other` int(20) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`),
-> KEY `name` (`name`),
-> KEY `other_key` (`other`)
-> ) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)mysql> insert into test_table(name) values('a'),('b'),('c'),('d');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from test_table;
+----+------+-------+
| id | name | other |
+----+------+-------+
| 1 | a | 0 |
| 2 | b | 0 |
| 3 | c | 0 |
| 4 | d | 0 |
+----+------+-------+
4 rows in set (0.00 sec)mysql> exit
Bye
C:\Users\coolwind>mysql -uroot -P3307 -h127.0.0.1 -p
Enter password:mysql> use test;
Database changed
mysql> drop table if exists test_table;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> CREATE TABLE `test_table` (
-> `id` int(20) NOT NULL AUTO_INCREMENT,
-> `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
-> `other` int(20) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`),
-> KEY `name` (`name`),
-> KEY `other_key` (`other`)
-> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CONNECTIO
N='mysql://root:[email protected]:3306/test/test_table';
Query OK, 0 rows affected (0.06 sec)mysql> select * from test_table;
+----+------+-------+
| id | name | other |
+----+------+-------+
| 1 | a | 0 |
| 2 | b | 0 |
| 3 | c | 0 |
| 4 | d | 0 |
+----+------+-------+
4 rows in set (0.01 sec)mysql> drop table if exists test;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `test` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `age` tinyint(3) unsigned DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)mysql> insert into test(age) values(80);
Query OK, 1 row affected (0.00 sec)mysql> select * from test as t1 join test_table as t2 on t1.id=t2.id;
+----+------+----+------+-------+
| id | age | id | name | other |
+----+------+----+------+-------+
| 1 | 80 | 1 | a | 0 |
+----+------+----+------+-------+
1 row in set (0.00 sec)mysql> exit
Bye