MySQL 表名用变量代替报1064错误.

[wqhjfree]

CREATE DEFINER=`offsee`@`%` PROCEDURE `pro_terminal_report`
()
BEGIN
declare _version int ;
declare _currDate date ;
declare _tableName varchar(25);
declare _sql varchar(1000);

declare cur_version cursor for select id from soft_version where calc_version = 1 order by ver asc;

open cur_version;

cur_version:loop
fetch cur_version into _version ;


set _sql = concat('select * from terminal_log_v',5);
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;

end loop;

set _currDate = date_format(now(),'%Y%m');

insert into terminal_report(province , terminal_count , fee, terminalPayCount , arup , mnc , otime)
select province , count(1) as terminalCount , sum(fee)/100 as fee, count(distinct mobile) as terminalPayCount ,(sum(fee)/100)/count(distinct mobile) arup , mnc ,otime from
(
select province, id, fee , mobile , if(carrier=1,1,0) as mnc , date_format(order_time,'%Y%m') as otime from sync_order as so where date_format(order_time,'%Y%m') = _currDate union
select province, id, fee , mobile , if(carrier=1,1,0) as mnc , date_format(order_time,'%Y%m') as otime from sync_order_month as sm where date_format(order_time,'%Y%m') = _currDate
) t
where otime = _currDate
group by province order by fee desc;

/****提交事务****/
END$$

[ACMAIN_CHM]

MYSQL中不支持表名为变量，必须是实际表名。或者使用 PREPARE

[rucypli]

貌似没什么错误吧 哪里的表名用变量了

[WWWWA]

set _sql = concat('select * from terminal_log_v',5);

SQL语句是这样
select * from terminal_log_v 5
你要达到什么目的

[wqhjfree]

ACMAIN_CHM 你好, 据说MYSQL5.0以后的版本支持变量名为表名


我要达到的目的是:
fetch cur_version into _version ;


set _sql = concat('select * from terminal_log_v',cur_version );
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;

end loop;

set _currDate = date_format(now(),'%Y%m');

通过游标cur_version动态传递一个版本号cur_version, 然后根据这个版本号到对应的日志表terminal_log_v查询, 如这条语句_sql = concat('select * from terminal_log_v',cur_version );
.例如: 如果 cur_version 为5 , 那么我想执行的语句是: select * from terminal_log_v5,
如果 cur_version 为6 , 那么我想执行的语句是: select * from terminal_log_v6.
但是执行下面语句就报1064错误, 注释这段代码就正常.
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;

[WWWWA]

set @_sql = concat('select * from terminal_log_v',cur_version );
prepare _stmt from @_sql;
execute _stmt;
deallocate prepare _stmt;

[wqhjfree]

[Quote=引用 3 楼 wwwwa 的回复:]

set _sql = concat('select * from terminal_log_v',5);

SQL语句是这样
select * from terminal_log_v 5
你要达到什么目的
[/Quote]


set _sql = concat('select * from terminal_log_v',5 );这句写错了,应该是

set _sql = concat('select * from terminal_log_v',_version );

我要达到的目的是:
fetch cur_version into _version ;

set _sql = concat('select * from terminal_log_v',5 );这句写错了,应该是

set _sql = concat('select * from terminal_log_v',_version );
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;

end loop;

set _currDate = date_format(now(),'%Y%m');

通过游标cur_version动态传递一个版本号cur_version, 然后根据这个版本号到对应的日志表terminal_log_v查询, 如这条语句_sql = concat('select * from terminal_log_v',_version );
.例如: 如果 _version 为5 , 那么我想执行的语句是: select * from terminal_log_v5,
如果 _version 为6 , 那么我想执行的语句是: select * from terminal_log_v6.
但是执行下面语句就报1064错误, 注释这段代码就正常.
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;

[WWWWA]

[Quote=引用 5 楼 wwwwa 的回复:]
set @_sql = concat('select * from terminal_log_v',cur_version );
prepare _stmt from @_sql;
execute _stmt;
deallocate prepare _stmt;
[/Quote]
or
set @sql = concat('select * from terminal_log_v',cur_version );
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

[ACMAIN_CHM]

无法理解，你执行这么一个select * from terminal_log_v6的意义何在？对于MYSQL来说，你只是想执行一个SELECT查询，但你的后续处理是什么？

[ymhtt]

空格！！

[wqhjfree]

[Quote=引用 7 楼 wwwwa 的回复:]

引用 5 楼 wwwwa 的回复:
set @_sql = concat('select * from terminal_log_v',cur_version );
prepare _stmt from @_sql;
execute _stmt;
deallocate prepare _stmt;

or
set @sql = concat('select * from termi……
[/Quote]
非常感谢wwwwa的指教 , 请问为什么@sql可以 , 而_sql去不行呢.

[WWWWA]

语法要求，呵呵，开始没有注意