delimiter $drop procedure if exists collectUserBoughts$create procedure collectUserBoughts()begin declare dbIndex int(3) default 0; C1:while dbIndex < 3 do
/* 创建视图
*/ select dbIndex;
//显示表号
drop view if exists view_temp; set @sql=concat('create view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid'); prepare stm from @sql; xecute stm; deallocate prepare stm; select count(*) from view_temp;/*该语句结果表明无论dbIndex如何变化,视图里的数据都是来自*userorderitem0的数据*/
set dbIndex = dbIndex + 1; end while C1; /* 删除视图 */ drop view view_temp; end$delimiter;
为什么无论dbIndex的值如何变化,视图的数据都是第一个表的数据?
/* 创建视图
*/ select dbIndex;
//显示表号
drop view if exists view_temp; set @sql=concat('create view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid'); prepare stm from @sql; xecute stm; deallocate prepare stm; select count(*) from view_temp;/*该语句结果表明无论dbIndex如何变化,视图里的数据都是来自*userorderitem0的数据*/
set dbIndex = dbIndex + 1; end while C1; /* 删除视图 */ drop view view_temp; end$delimiter;
为什么无论dbIndex的值如何变化,视图的数据都是第一个表的数据?
/* 创建视图
*/ select dbIndex;
//显示表号
drop view if exists view_temp; set @sql=concat('create view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
select dbIndex,@sql;
prepare stm from @sql; xecute stm; deallocate prepare stm; select count(*) from view_temp;/*该语句结果表明无论dbIndex如何变化,视图里的数据都是来自*userorderitem0的数据*/
set dbIndex = dbIndex + 1; end while C1; /*看一下你的语句。
|
+---------+---------------------------------------------------------------------
-----------------+
| 0 | create view view_temp as select userid,productid from userorderitem0
order by userid |
+---------+---------------------------------------------------------------------
-----------------+
1 row in set (0.02 sec)+----------+
| count(*) |
+----------+
| 630 |
+----------+
1 row in set (0.08 sec)+---------+---------------------------------------------------------------------
-----------------+
| dbIndex | @sql
|
+---------+---------------------------------------------------------------------
-----------------+
| 1 | create view view_temp as select userid,productid from userorderitem1
order by userid |
+---------+---------------------------------------------------------------------
-----------------+
1 row in set (0.08 sec)+----------+
| count(*) |
+----------+
| 630 |
创建视图的语句没有问题
+---------+----------+
| 0 | 630 |
+---------+----------+
1 row in set (0.03 sec)+---------+----------+
| dbIndex | count(*) |
+---------+----------+
| 1 | 630 |
+---------+----------+
1 row in set (0.08 sec)+---------+----------+
| dbIndex | count(*) |
+---------+----------+
| 2 | 630 |
+---------+----------+
1 row in set (0.13 sec)Query OK, 0 rows affected (0.14 sec)mysql> select count(*) from userorderitem0;
+----------+
| count(*) |
+----------+
| 630 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from userorderitem1;
+----------+
| count(*) |
+----------+
| 690 |
+----------+
deallocate prepare stm;show create view view_temp;
select count(*) from view_temp;/*该语句结果表明无论dbIndex如何变化,视图里的数据都是来自*userorderitem0的数据*/
set dbIndex = dbIndex + 1; end while C1;
drop procedure if exists collectUserBoughts$
create procedure collectUserBoughts()
begin
declare dbIndex int(3) default 0;
drop view if exists view_temp;
set @sql=concat('create or replace view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
prepare stm from @sql;
execute stm;
deallocate prepare stm;
select dbIndex,count(*) from view_temp;
set dbIndex = dbIndex + 1;
drop view if exists view_temp;
set @sql=concat('create or replace view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
prepare stm from @sql;
execute stm;
deallocate prepare stm;
select dbIndex,count(*) from view_temp;
set dbIndex = dbIndex + 1;
drop view if exists view_temp;
set @sql=concat('create or replace view view_temp as select userid,productid from userorderitem',dbIndex,' order by userid');
prepare stm from @sql;
execute stm;
deallocate prepare stm;
select dbIndex,count(*) from view_temp;
set dbIndex = dbIndex + 1;
drop view view_temp;
end$
delimiter ;
很奇怪,如果把循环里面的语句都放到外面,执行三次,则能得到我想要的结果,能告诉我为什么吗,榔头老大?
+-----------+--------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------+--------------
--------+----------------------+
| view_temp | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_temp` AS select `userorderitem0`.`U
serId` AS `userid`,`userorderitem0`.`ProductId` AS `productid` from `userorderitem0` order by `userorderitem0`.`UserId` | utf8
| utf8_general_ci |
+-----------+--------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------+--------------
创建的视图确实都是从userorderitem0的,可为什么,是预处理语句的错误还是其他的?