将准备好的语句的结果存储为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屋！