将准备好的语句的结果存储为mysql中的表? [英] Storing the results of a prepared statement as a table in mysql?
问题描述
是否可以在mysql中存储准备好的表的结果?
我的用例是-: 我将根据源表的某些条件创建两个变量,然后根据此条件获取随机行.由于我有10个这样的表,因此我应该是第1个加入它们的表,然后根据总体"通过/过滤条件进行此随机化(另请参见下面的@total,这是我的主要标准,PER表)
My use case is -: I am creating two variables based on certain conditions of the source table, then fetching the randomized rows, based on this criteria. Since I have 10 of such tables, should I be 1st joining them and then doing this randomization on the "overall" passing/filtering criteria (See also @total below, which is my main criteria, PER table)
set @total=(select count(*) from tab_1 where predict_var ="4" or predict_var ="2" ) ;
set @sample= ( select @total*(70/30)) ;
PREPARE STMT FROM " SELECT * FROM tab_1 WHERE predict_var = '4' or predict_var = '2' union
(SELECT * FROM tab_1 WHERE predict_var = '0' or predict_var = '1' ORDER BY RAND() limit ? )" ;
EXECUTE STMT USING @sample;
执行完此语句后-我想存储这些行,以便以后检索,最好以表的形式.我想做这样的事情
After I have Executed this statement - I want to be storing these rows, for retrieval later, preferably in form of a table. I would like to do something like this
# incorrect syntax, but I would like something similar
create table tab_derived_1
select * from
EXECUTE STMT USING @sample;
提示:+1表示额外的提示,为什么这不适用于准备好的语句.
Tip : +1 for additionally mentioning, why this does not work with prepared Statements.
推荐答案
在语句中放置创建表:
PREPARE STMT FROM "CREATE TABLE tab_derived_1 SELECT * FROM tab_1 WHERE predict_var = '4' or predict_var = '2' union
(SELECT * FROM tab_1 WHERE predict_var = '0' or predict_var = '1' ORDER BY RAND() limit ? )" ;
EXECUTE STMT USING @sample;
如果您想返回结果,而不仅仅是将它们存储在表中,那么只需做最后一个
And if you want to return the results, not just store them in a table, just do a final
SELECT * FROM tab_derived_1
这篇关于将准备好的语句的结果存储为mysql中的表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!