MySql的"view"，"prepared statement"以及“需要重新准备准备好的陈述". [英] MySql "view", "prepared statement" and "Prepared statement needs to be re-prepared"

问题描述

我在使用MySql时遇到问题，这是详细信息: 我创建了一个新的架构/数据库，仅执行以下查询:

I've a problem with MySql, here is the details : I created a new schema/database, executed (only) this queries :

create table mytable (
id varchar(50) not null,
name varchar(50) null default '',
primary key (id));

create view myview as
select id,name from mytable;

insert into mytable values ('1','aaa');
insert into mytable values ('2','bbb');
insert into mytable values ('3','ccc');

然后，如果我运行这些查询:

and then, if I run these queries :

select * from mytable;
select * from myview;
prepare cmd from 'select id,name from mytable where id=?';
set @param1 = '2';
execute cmd using @param1;

查询给出正确的结果(3行，3行，1行).

the queries give the correct result (3 rows,3 rows,1 row).

但是，如果我运行此查询，则存在问题:

but, the problem exists if I run this query:

prepare cmd from 'select id,name from myview where id=?';
set @param1 = '2';
execute cmd using @param1;

错误:#1615-准备好的语句需要重新准备

ERROR: #1615 - Prepared statement needs to be re-prepared

我进行了一些研究，发现在"may"以下增加配置可以解决问题:

I've done some research and found that the increment of configurations below "may" solve the problem :

increase table_open_cache_instances value
increase table_open_cache value
increase table_definition_cache value

据我所知，以上查询是常见的和标准的MySql查询，因此我认为语法没有问题.

As far as I know, the queries above are the common and standard MySql queries, so I think there is no problem with the syntax.

我正在共享的虚拟主机上，并且使用的MySql版本是5.6.22

I'm on a shared webhosting and using MySql version is 5.6.22

但是让我感到困惑的是，它仅包含1个架构/数据库，1个表，3个短记录和1个视图， 我执行了一个通用的标准MySql选择查询， 值的增量是否真的需要? 有没有谁遇到过同样的问题，增加了价值并真正解决了问题? 或者，也许您有其他解决方案，您认为可能或将解决该问题?

But the things that make me confused is, it only contain 1 schema/database, with 1 table with 3 short records and 1 view, and I executed a common and standard MySql select query, does the increment of values above really needed? is there anyone with the same problem had increase the values and really solve the problem? or, perhaps do you have any other solution which you think may or will works to solve this problem?

ps:一天不会发生一次或两次(假定是由某些备份或相关备份引起的)，而是一天(一天24小时).

ps: it does not happen once or twice in a day (which assumed caused by some backup or related), but in all day (24 hours).

谢谢.

推荐答案

是否在每次执行后执行此操作?

Do you do this after each execute?

deallocate prepare cmd;

这篇关于MySql的"view"，"prepared statement"以及“需要重新准备准备好的陈述".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！