MySQL“ERROR 1046（3D000）：No database selected” on update查询 [英] MySQL "ERROR 1046 (3D000): No database selected" on update query
问题描述
错误1046（3D000）：没有数据库选择。 其他类似结构的查询，但使用INSERT工作正常。
要在测试用例中重复此问题，请尝试运行以下查询：
create table test.object1（
id_object1 int unsigned not null auto_increment，
total int，
weight int，
dt datetime ，
主键（id_object1）
）engine = InnoDB;
create table test.object2（
id_object2 int unsigned not null auto_increment，
主键（id_object2）
）engine = InnoDB;
create table test.score（
id_object1 int unsigned not null，
id_object2 int unsigned not null，
dt datetime，
score float，
主键（id_object1，id_object2），
约束fk_object1外键（id_object1）引用object1（id_object1），
约束fk_object2外键（id_object2）引用object2（id_object2）
）engine = InnoDB;
插入到test.object1（id_object1，total，weight，dt）值（1，0，0，'2012-01-01 00:00:00'）;
插入到test.object1（id_object1，total，weight，dt）值（2，0，0，'2012-01-02 00:00:00'）;
插入到test.object2（id_object2）values（1）;
插入到test.score（id_object1，id_object2，dt，score）值（1,1，'2012-01-03 00:00:00'，10）;
insert into test.score（id_object1，id_object2，dt，score）values（2，1，'2012-01-04 00:00:00'，8）;
update test.object1 p
join（
select ur.id_object1，sum（ur.score * ur.weight）as total，count（*）as weight
from（
select lur。*
from（
select s.id_object1，s.id_object2，s.dt，s.score，1 as weight
from test.score as s
使用（id_object1）将test.object1连接为o1使用（id_object1）
其中s.dt> o1.dt
order by s.id_object1，s.id_object2，s.dt desc
）as lur
group by lur.id_object2，lur.id_object1，date（lur.dt）
order by lur.id_object1，lur.id_object2
）as ur
group by ur .id_object1
）as r using（id_object1）
set
p.total = p.total + r.total，
p.weight = p.weight + r.weight，
p.dt = now（）;
注意：我从PHP环境运行这些查询，我没有明确使用mysql_select_db测试'），因为我不喜欢，没有其他（很多！）查询需要它。我确定使用mysql_select_db将解决我的问题，但我想知道为什么这个特定的查询不工作。
为了比较：如果你运行这个更简单的查询，也不使用mysql_select_db，一切正常：
更新test.object1 set total = 1，weight = 1 ，dt = now（）其中id_object1 = 1;
我已搜索无效。我发现的唯一的事情就是接近，是这个错误报告： http：//bugs.mysql .com / bug.php？id = 28551 ，特别是最后一个（未回答）消息...
你的字段命名不正确，但即使你更正它们，这是一个在 MySQL 中的错误，如果你没有默认数据库，它不会让你这样做。 / p>
更新test.object1 p
join（
select ur.id_object1，sum（ur.score * ur （
select（*）
从（
）选择s.id_object1，s.id_object2，s.dt，s .score，1 as weight
from test.score as s
将test.object1作为o1连接
使用（id_object1）
其中s.dt> o1.dt
order by
s.id_object1，s.id_object2，s.dt desc
）as lur
group by
lur.id_object1，lur.id_object1，date（lur.dt）
order by
lur.id_object1，lur.id_object1
）as ur
group by ur.id_object1
）as r
使用（id_object1）
SET p.total = p.total + r.total，
p.weight = p.weight + r.weight，
p.dt = now（）;
问题是特定于 UPDATE - 嵌套查询，没有默认数据库（ SELECT 或单嵌套查询或默认数据库工作正常）
I have an UPDATE query where I explicitely reference the database, but MySQL still complains with the message: ERROR 1046 (3D000): No database selected.
Other queries that are similar of structure, but use an INSERT work fine. Other queries that only perform SELECTs also run fine.
To repeat the problem in a test case, try running these queries:
create table test.object1 (
id_object1 int unsigned not null auto_increment,
total int,
weight int,
dt datetime,
primary key (id_object1)
) engine=InnoDB;
create table test.object2 (
id_object2 int unsigned not null auto_increment,
primary key (id_object2)
) engine=InnoDB;
create table test.score (
id_object1 int unsigned not null,
id_object2 int unsigned not null,
dt datetime,
score float,
primary key (id_object1, id_object2),
constraint fk_object1 foreign key (id_object1) references object1 (id_object1),
constraint fk_object2 foreign key (id_object2) references object2 (id_object2)
) engine=InnoDB;
insert into test.object1 (id_object1, total, weight, dt) values (1, 0, 0, '2012-01-01 00:00:00');
insert into test.object1 (id_object1, total, weight, dt) values (2, 0, 0, '2012-01-02 00:00:00');
insert into test.object2 (id_object2) values (1);
insert into test.score (id_object1, id_object2, dt, score) values (1, 1, '2012-01-03 00:00:00', 10);
insert into test.score (id_object1, id_object2, dt, score) values (2, 1, '2012-01-04 00:00:00', 8);
update test.object1 p
join (
select ur.id_object1, sum(ur.score * ur.weight) as total, count(*) as weight
from (
select lur.*
from (
select s.id_object1, s.id_object2, s.dt, s.score, 1 as weight
from test.score as s
join test.object1 as o1 using(id_object1)
where s.dt > o1.dt
order by s.id_object1, s.id_object2, s.dt desc
) as lur
group by lur.id_object2, lur.id_object1, date(lur.dt)
order by lur.id_object1, lur.id_object2
) as ur
group by ur.id_object1
) as r using(id_object1)
set
p.total = p.total + r.total,
p.weight = p.weight + r.weight,
p.dt = now();
Note: I'm running these queries from a PHP environment and I have NOT explicitely used mysql_select_db('test'), because I prefer not to and none of the other (many!) queries require it. I'm sure that using mysql_select_db would solve my issue, but I would like to know why exactly this particular query does not work.
For comparison sake: if you'd run this simpler query, also without using mysql_select_db, everything works fine:
update test.object1 set total=1, weight=1, dt=now() where id_object1=1;
I've searched to no avail. The only thing I found that came close, was this bug report: http://bugs.mysql.com/bug.php?id=28551 and especially that last (unanswered) message...
You have fields named incorrectly, but even if you correct them, this is a bug in MySQL that won't let you do it if you don't have default database.
update test.object1 p
join (
select ur.id_object1, sum(ur.score * ur.weight) as total, count(*) as weight
from (
select lur.*
from (
select s.id_object1, s.id_object2, s.dt, s.score, 1 as weight
from test.score as s
join test.object1 as o1
using (id_object1)
where s.dt > o1.dt
order by
s.id_object1, s.id_object2, s.dt desc
) as lur
group by
lur.id_object1, lur.id_object1, date(lur.dt)
order by
lur.id_object1, lur.id_object1
) as ur
group by ur.id_object1
) as r
USING (id_object1)
SET p.total = p.total + r.total,
p.weight = p.weight + r.weight,
p.dt = now();
The problem is specific to UPDATE with double-nested queries and no default database (SELECT or single-nested queries or default database work fine)
这篇关于MySQL“ERROR 1046（3D000）：No database selected” on update查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
