use test;
create temporary table if not exists testtemporary
(
id int not null
) engine=MyISAM;insert testtemporary select 2 from dual;
update testtemporary inner join (select id from testtemporary where id=2) as A using(id)
set testtemporary.id=3
where testtemporary.id=2;
Error Code: 1137
Can't reopen table: 'testtemporary'use test;
create table if not exists testtemporary
(
id int not null
) engine=MyISAM;insert testtemporary select 2 from dual;
update testtemporary inner join (select id from testtemporary where id=2) as A using(id)
set testtemporary.id=3
where testtemporary.id=2;
1 row(s) affected
Rows matched: 1 Changed: 1 Warnings: 0use test;
create temporary table if not exists testtemporary
(
id int not null
) engine=MyISAM;select * from testtemporary,testtemporary as b;
Error Code: 1137
Can't reopen table: 'testtemporary'
在同一个查询中,临时表不能够以两个名字存在。也不能同时出现在主查询和子查询中。这个限制感觉怪怪的。导致了我想用临时表存储报表结果并进一步处理时不能够完成,但改为物理表时,又会造成线程间共享了。该真么解决呢。
create temporary table if not exists testtemporary
(
id int not null
) engine=MyISAM;insert testtemporary select 2 from dual;
update testtemporary inner join (select id from testtemporary where id=2) as A using(id)
set testtemporary.id=3
where testtemporary.id=2;
Error Code: 1137
Can't reopen table: 'testtemporary'use test;
create table if not exists testtemporary
(
id int not null
) engine=MyISAM;insert testtemporary select 2 from dual;
update testtemporary inner join (select id from testtemporary where id=2) as A using(id)
set testtemporary.id=3
where testtemporary.id=2;
1 row(s) affected
Rows matched: 1 Changed: 1 Warnings: 0use test;
create temporary table if not exists testtemporary
(
id int not null
) engine=MyISAM;select * from testtemporary,testtemporary as b;
Error Code: 1137
Can't reopen table: 'testtemporary'
在同一个查询中,临时表不能够以两个名字存在。也不能同时出现在主查询和子查询中。这个限制感觉怪怪的。导致了我想用临时表存储报表结果并进一步处理时不能够完成,但改为物理表时,又会造成线程间共享了。该真么解决呢。
关键是要适应工具, 或者选择工具。 当然如果你有时间和精力也可以改造工具,把MYSQL的源代码下载后自行修改重新编译即可。
恩,mysql自己的限制,没得办法。