我应该使用“maxrows”吗？在我的查询中？冷融合/SQL
搜索具有数字主键的行时是否需要使用 maxrows="1" ？
<cfquery maxrows="1">
select *
from table
where RowID = #NumericVariable#
</cfquery>
如果包含或排除 maxrows，ColdFusion 是否具有某种 SQL 绑定或其他任何使其更好/更快/优越的功能？
Is it necessary to use maxrows="1" when searching for a row with a numeric primary key?
<cfquery maxrows="1">
select *
from table
where RowID = #NumericVariable#
</cfquery>
Does ColdFusion have some kind of SQL binding or anything else that makes it better / faster / superior if maxrows is included or excluded?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
不会。
如果表的结构正确（即您限制的字段是主键），则限制为 1 是多余的。根据定义，主键唯一标识一行。
No.
If the table is structured properly (i.e. the field you're limiting on is the primary key), then limiting by one is redundant. By definition, a primary key uniquely identifies one row.
maxrows 限制查询变量从数据库返回后的结果数量。
如果您只返回单个结果，那么这根本不会对查询的性能产生任何影响。
ColdFusion 允许您使用 CFQUERYPARAM 标记传递绑定参数。
例如：
您可以希望通过提供绑定参数并指定要返回的属性来提高数据库的速度。这可能会允许更好的查询缓存和性能改进，具体取决于您使用的数据库引擎。
我想补充的是，使用 CFQUERYPARAM 通常比保留变量不合格且可能遭受 SQL 注入攻击更安全。
maxrows limits the number of results in the query variable after its returned from the database.
If you are only ever returning a single result then this is not going to have any impact at all on the performance of your query.
ColdFusion does allow you to pass bind parameters using the CFQUERYPARAM tag.
For example:
You can hope to improve the speed on your database by providing a bind parameter and specifying the properties to return. This may allow for better query caching and performance improvements depending on the database engine you are using.
I'd add its generally more secure to use CFQUERYPARAM than to leave variables unqualified and potentially open to SQL injection attacks.
我会测试它：
运行上面的代码，因为它适用于 10 个不同的 RowID 值，并将其与运行具有 maxrows 条件的代码进行比较。如果您发现后者的执行时间总是更好，那么您就有了明确的答案！
I would test it out:
Run your above code as it is for 10 different values of RowID, and compare it with running the code with a maxrows condition as well. If you see the execution times are always better for the latter, you have your definitive answer!
其工作原理是，cfquery 仍然尝试查询适合您的 where 子句的所有表行，但它只返回 x 个结果。
所以对于性能来说并不是很好。
至少在 SQL Server 中，另一个替代方案是 SET ROWCOUNT # 命令，它实际上限制了它返回的记录数。但是，如果您在 cfadmin 中进行了持久检查，这可能会影响所有 cfqueries。
或者mysql中有limit命令。
我希望有更好的解决方案，但没有。
How that works, is that cfquery still tries to query all the table row's that fit your where clauses, but it only returns x results.
So not exactly great for performance.
Another alternative at least in SQL Server, is the SET ROWCOUNT # command, which actually limits the number of records it returns. However this can affect all cfqueries if you have check persistant in cfadmin.
Or in mysql there is the limit command.
I wish there was a better solution, but there isn't.
不，完全没有必要。虽然实际数据库查询的 maxrows 可能会受到质疑并被替换为 SQL 限制子句 (mySQL)，但它也优化了数据库的性能，通过“查询的查询”，maxrow 参数是限制结果集的唯一可能性，因为查询的查询不支持 limit 子句。
例如：
只是想指出这个背景。
No, it's not unnecessary at all. While maxrows with a real database query may be questionnable and replaced with a SQL limit clause (mySQL), that optimizes performance of the db as well, with "query of queries", the maxrow-parameter is the only possibility to limit the resultset, since query of queries does not support limit clause.
e.g.:
Just wanted to point out this context.