mysql多库查询

[nzqinghui]

有库web,test
我连接的库是：web
mysql4中支持 如下操作：
insert into test.tableName (name,pwd) select name,pwd from web.table 处理成功
换成mysql5就操作失败。不知道是什么原因？难道5不支持这样的操作了吗？如何解决此问题？谢谢

[denniswwh]

支持啊，你报什么错呢？

[WWWWA]

应该可以，提示什么错误信息？没有权限？

[阿_布]

有可能是你登录的用户名只有其中一个数据库权限。

[vinsonshen]

支持在引用对象名前加库名的：

mysql> select * from shen.tb_test_insert;
Empty set (0.00 sec)

mysql> select * from db_syn.tb_test_insert2;
+------+------+
| id   | memo |
+------+------+
|  100 | aaa  |
+------+------+
1 row in set (0.00 sec)
mysql> insert into shen.tb_test_insert
    -> select * from db_syn.tb_test_insert2;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from shen.tb_test_insert;
+------+------+
| id   | memo |
+------+------+
|  100 | aaa  |
+------+------+
1 row in set (0.00 sec)

mysql>
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.27-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

mysql>

[ACMAIN_CHM]

[Quote]insert into test.tableName (name,pwd) select name,pwd from web.table 处理成功
换成mysql5就操作失败。不知道是什么原因？难道5不支持这样的操作了吗？如何解决此问题？谢谢[/Quote]


同样也支持，你的出错信息是什么？

[ACMAIN_CHM]

同样的测试没有任何问题。

mysql> create database web;
Query OK, 1 row affected (0.00 sec)

mysql> create table test.tableName (name int,pwd int);
Query OK, 0 rows affected (0.11 sec)

mysql> create table web.table (name int,pwd int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test.tableName (name,pwd) select name,pwd from web.table ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)

mysql>

[nianzhang747]

1.看看你的当前用户

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

2.查看权限

mysql> use mysql;
Database changed
mysql> select user,db,host from db;
+-------+---------------------+------+
| user  | db                  | host |
+-------+---------------------+------+
| model | db_tigerstone       | %    |
| model | information\_schema | %    |
| model | mysql               | %    |
| model | sample              | %    |
| scot  | sample              | %    |
| model | test                | %    |
+-------+---------------------+------+
6 rows in set (0.03 sec)

3.如果你的用户对database无权限进行第四步（提示我的model用户对db_tigerstone有使用权限）

4.mysql> grant all privileges on database_name.* to newuser@"%";
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

ok 重新链接进入mysql
你现在可以使用了

[sunwch]

mysql5.0支持这样的写法,如果有错误的话可以把错误贴出来.