为什么 MySQLi 准备了语句?

2021-12-25 00:00:00 php mysqli prepared-statement

在 MySQLi 中使用准备好的语句有什么好处?

What are the advantages of using prepared statements with MySQLi?

如果唯一的目的是保护查询,那么使用类似 mysqli_real_escape_string 之类的东西来清理查询不是更好,而不是为每个查询编写这么多行代码(比如 prepare、bind_param、执行、关闭等)?

If the only purpose is to secure the query, isn't it better to clean the query using something like mysqli_real_escape_string instead of writing so many lines of code for each query (like prepare, bind_param, execute, close, etc.)?

推荐答案

准备语句不仅仅是为了代码安全.它可以帮助 SQL 服务器解析您的语句并为您的查询生成执行计划.

Preparing statements is not just for code security. It helps the SQL server parse your statement and generate an execution plan for your query.

如果您运行 SELECT 1000 次,那么 SQL 服务器将必须解析、准备并生成一个关于如何获取数据 1000 次的计划.

If you run a SELECT 1000 times then the SQL server will have to parse, prepare, and generate a plan on how to get your data 1000 times.

如果您准备一个语句,然后使用不同的绑定值运行准备好的语句 1,000 次,那么该语句只会被解析一次,并且每次都使用相同的查询计划.

If you prepare a statement then run the prepared statement 1,000 times each with different bound values then the statement is parsed only once and the same query plan is used each time.

这不仅有助于您在脚本中运行 1,000 次查询,而且有助于您只有 1 个语句并运行脚本 1,000 次.服务器可以记住计划服务器端.下次您的脚本运行时,它将再次使用相同的计划.

This helps not only when you run 1,000 queries inside the script but also if you only have 1 statement and run the script 1,000 times. The server can remember the plan server side. The next time your script runs it will use the same plan again.

当然,一两个查询可能看起来微不足道,但是当您开始执行大量查询或重复执行相同查询时,您将节省大量处理时间.

Sure it may seem trivial for one or two queries but when you start executing large numbers of queries or the same query repeatedly you will save a lot of processing time.

相关文章