缓存PDO准备的语句 [英] Caching PDO prepared statements

问题描述

保存PDO准备好的语句以在会话中重用是否有意义?

Is there any point in saving PDO prepared statements for reuse in a session?

我正在构建一个使用MySQL全文查询的网站，例如，我将其整合到我的PHP中

I'm building a site that uses MySQL fulltext queries, which I'm putting together in my PHP, like

SELECT * FROM table 
WHERE MATCH (title) AGAINST ($search_string IN BOOLEAN MODE) AND 
      MATCH (keywords) AGAINST ($keywords IN BOOLEAN MODE)
ORDER BY $order_by $asc_desc

使用绑定参数准备和执行查询时，运行这种查询的时间似乎要比仅准备并执行包含值的查询字符串时花费的时间长得多.但是我需要使用准备好的语句来防止SQL注入的风险.

It seems to take a lot longer to run this kind of query when I prepare and execute it with bound parameters, than when I just prepare and execute a query string with the values included. But I need to use prepared statements to prevent the risk of SQL injection.

在任何会话中，我很可能会使用不同的参数值多次运行相同的查询.对我而言，一旦创建了PDOStatement对象(例如在会话中)，保存它是否有意义?如果是这样，最好的方法是什么?将每个准备好的语句创建时以SQL查询字符串作为每个键的键，将其保存在关联数组中是一种好习惯吗?

In any session I would very likely run the same query several times, with different parameter values. Would it make sense for me to save the PDOStatement object once it's been created (for example in the session)? If so, what would be the best way to do that? Would it be good practice to save each prepared statement in an associative array as it's created, with the SQL query string as the key for each?

在进一步阅读中，我发现您不能对语句的ORDER BY和ASC/DESC部分使用绑定参数.当我将它们替换为固定值时，性能会提高.

On further reading I found you can't use bound params for the ORDER BY and ASC / DESC part of a statement. When I replace these with fixed values the performance improves.

推荐答案

将prepare语句存储到会话中以实现可重用的目的没有任何好处，
昂贵的成本在于查询执行本身.

There is no benefits to store prepare statement into session for reusable purpose,
the expensive cost is on the query execution itself.

这篇关于缓存PDO准备的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！