在PHP中到处使用准备好的语句? (PDO) [英] Use prepared statements everywhere in PHP? (PDO)

问题描述

我将要切换在多个站点/项目中使用的数据库类，从使用自定义mysql_query方法*到使用PDO和预处理语句.但是，我首先有一个问题-我想在所有地方使用准备好的语句吗?即使在查询只会运行一次的地方?在需要做类似事情的情况下该怎么办?

I'm going to be switching my database class that I use in several sites/projects, from using a custom mysql_query method*, to using PDO and prepared statements. However I have a question first - do I want to use prepared statements everywhere? Even in places where the query will only be ran once? What about situations where I need to do something like:

INSERT INTO `table` (`column`, `column`) VALUES ('value','value'), ('value','value'),('value','value'), etc.

我应该使用单个准备好的语句(和单个VALUE)，但是每次都使用不同的变量执行它，还是应该使用上面的样式?如果我在这里使用准备好的语句，那么我们对性能的影响有多糟糕?在这种情况下我需要使用交易吗?

Should I use a single prepared statement (And a single VALUE), but execute it with different variables each time, or should I use the style above? If I do use a prepared statement here, how bad of a performance hit are we talking? Do I need to use transactions in this situation?

*我的mysql_query方法类似于准备好的语句，因为用户可以调用$mysql->Query("SELECT * FROM％s WHERE '%s'='%s'", $var, $var, $var)，并且该方法使用mysql_real_escape_string自动转义所有内容.

*My mysql_query method is similar to a prepared statement in that the user can call $mysql->Query("SELECT * FROM%sWHERE '%s'='%s'", $var, $var, $var), and the method auto-escapes everything with mysql_real_escape_string.

推荐答案

准备好的语句为防止SQL注入提供了很好的保护，并且它们还为某些类型的查询提供了性能上的好处.就个人而言，我会在各处使用它们.

Prepared statements provide a good degree of protection against SQL injection, and they also provide a performance benefit for some types of query. Personally, I would use them everywhere.

如果发现特定查询导致性能问题，则可以进行一些性能分析以找出问题的原因，然后根据需要优化代码或查询.但是在遇到问题之前，请勿尝试进行微优化.

If you discover that a particular query is causing performance problems, you can do some profiling to track down the cause of the problem, and then optimise the code or query as required. But don't try to micro-optimise before you have a problem.

对于交易，只需在需要时使用它们即可.例如，当您需要执行一系列全有或全无的更新时，如果其中一次失败，则整个批次必定会失败.这些对于诸如多对多关系这样的事情很有用，在这种情况下必须更新三个表，并且您不希望在发生故障时保留部分关系.

As for transactions, just use them when you need them. For example, when you need to perform a sequence of all-or-nothing updates, where if one fails, the whole lot must fail. These can be useful for things like many-to-many relationships, where three tables must be updated, and you don't want partial relationships remaining if a failure occurs.

这篇关于在PHP中到处使用准备好的语句? (PDO)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！