在 MySQL 和 PHP 中进行计算 [英] Doing calculations in MySQL vs PHP

问题描述

上下文:

• 我们有一个 PHP/MySQL 应用程序.
• 部分计算直接在 SQL 中完成.例如:过去 24 小时内创建的所有用户都将通过 SQL 查询返回(NOW() – 1 天)

我和一位开发人员正在争论，我认为我们应该这样做:

There's a debate going on between a fellow developer and me where I'm having the opinion that we should:

A.将所有计算/代码/逻辑保留在 PHP 中，并将 MySQL 视为愚蠢"的信息存储库

A. Keep all calculations / code / logic in PHP and treat MySQL as a 'dumb' repository of information

他的意见:

B.根据更容易/更快的方式进行混合搭配.http://www.onextrapixel.com/2010/06/23/mysql-has-functions-part-5-php-vs-mysql-performance/

B. Do a mix and match depending on whats easier / faster. http://www.onextrapixel.com/2010/06/23/mysql-has-functions-part-5-php-vs-mysql-performance/

我正在考虑可维护性的观点.他在看速度(正如文章所指出的，MySQL 中的某些操作更快).

I'm looking at maintainability point-of-view. He's looking at speed (which as the article points out, some operations are faster in MySQL).

@bob-the-destroyer@tekretic@OMG 小马@mu 太短了@都铎君士坦丁@坦杜@哈雷

@bob-the-destroyer @tekretic @OMG Ponies @mu is too short @Tudor Constantin @tandu @Harley

我同意(并且很明显)高效的 WHERE 子句属于 SQL 级别.但是，例如:

I agree (and quite obviously) efficient WHERE clauses belong in the SQL level. However, what about examples like:

1. 在 SQL 中使用 NOW() - 1 天计算 24 周期以选择过去 24 小时内创建的所有用户?
2. 返回所有用户的大写名字和姓氏?
3. 连接字符串?
4. (想法，伙计们?)

属于 SQL 域的清晰示例:

Clear examples belonging in the SQL domain:

1. 特定的 WHERE 选择
2. 嵌套的 SQL 语句
3. 排序/排序
4. 选择不同的项目
5. 计算行/项目

推荐答案

我会发挥每个系统的优势.

I'd play to the strengths of each system.

聚合、加入和过滤逻辑显然属于数据层.它更快，不仅因为大多数数据库引擎都为此进行了 10 多年的优化，而且您可以最大限度地减少在数据库和网络服务器之间转移的数据.

Aggregating, joining and filtering logic obviously belongs on the data layer. It's faster, not only because most DB engines have 10+ years of optimisation for doing just that, but you minimise the data shifted between your DB and web server.

另一方面，我使用过的大多数数据库平台在处理单个值方面的功能都很差.诸如日期格式化和字符串操作之类的事情在 SQL 中很糟糕，你最好在 PHP 中完成这些工作.

On the other hand, most DB platforms i've used have very poor functionality for working with individual values. Things likes date formatting and string manipulation just suck in SQL, you're better doing that work in PHP.

基本上，将每个系统用于其构建目的.

Basically, use each system for what it's built to do.

在可维护性方面，只要发生的事情之间的划分是明确的，将它们分离到逻辑类型应该不会造成太大问题，当然也不足以消除好处.在我看来，代码的清晰度和可维护性更多的是关于一致性，而不是将所有逻辑放在一个地方.

In terms of maintainability, as long as the division between what happens where is clear, separating these to types of logic shouldn't cause much problem and certainly not enough to out way the benefits. In my opinion code clarity and maintainability are more about consistency than about putting all the logic in one place.

回复:具体例子...

1. 我知道这也不是你所指的，但日期几乎是一个特例.您要确保系统生成的所有日期都是在 Web 服务器或数据库上创建的.如果数据库服务器和网络服务器被配置为不同的时区(我已经看到这种情况发生)，否则会导致一些潜在的错误.想象一下，例如，您有一个 createdDate 列，默认值为 getDate()，该列应用于 DB 插入.如果您要插入记录，则使用在 PHP 中生成的日期(例如 date("Ymd", time() - 3600)，选择上次创建的记录小时，您可能不会得到您所期望的.至于您应该在哪一层执行此操作，我更喜欢 DB，如示例中所示，它允许您使用列默认值.

1. I know this isn't what you're referring too but dates are almost a special case. You want to make sure that all dates generated by the system are created either on the web server OR the database. Doing otherwise will cause some insidious bugs if the db server and webserver are ever configured for different timezones (i've seen this happen). Imagine, for example, you've got a createdDate column with a default of getDate() that is applied on insert by the DB. If you were to insert a record then, using a date generated in PHP (eg date("Y-m-d", time() - 3600), select records created in the last hour, you might not get what you expect. As for which layer you should do this on, i'd favour the DB for, as in the example, it lets you use column defaults.

对于大多数应用程序，我会在 PHP 中执行此操作.将名字和姓氏结合起来听起来很简单，直到您意识到有时也需要在其中使用称呼、头衔和中间名首字母.另外，您几乎肯定会遇到这样的情况:您需要用户的名字、姓氏和组合称呼 + 名字 + 姓氏.将它们连接到 DB 端意味着您最终会移动更多数据，尽管实际上这非常小.

For most apps i'd do this in PHP. Combining first name and surname sounds simple until you realise you need salutations, titles and middle initials in there sometimes too. Plus you're almost definitely going to end up in a situation where you want a users first name, surname AND a combine salutation + firstname + surname. Concatenating them DB-side means you end up moving more data, although really, it's pretty minor.

视情况而定.如上所述，如果您想单独使用它们，最好在性能方面将它们单独拉出并在需要时连接.也就是说，除非您处理的数据集很大，否则可能还有其他因素(如您提到的，可维护性)具有更大的影响.

Depends. As above, if you ever want to use them separately you're better off performance-wise pulling them out separately and concatenating when needed. That said, unless the datasets your dealing with are huge there are probably other factors (like, as you mention, maintainability) that have more bearing.

一些经验法则:

• 应该在数据库中生成增量 ID.
• 就我个人而言，我喜欢数据库应用的默认值.
• 选择时，任何减少记录数量的事情都应该由 DB 来完成.
• 减少数据集 DB 端的大小通常是好的(就像上面的字符串示例一样).
• 正如你所说；排序、聚合、子查询、连接等应始终在 DB 端.
• 此外，我们还没有讨论它们，但触发器通常是坏的/必要的.

您在这里面临一些核心权衡，平衡实际上取决于您的应用程序.

There are a few core trade-offs your facing here and the balance really depends on you application.

有些事情绝对应该 - 每次 - 总是在 SQL 中完成.为许多任务排除一些异常(如日期) SQL 可能非常笨重，并且可能会让您在不合适的地方留下逻辑.在您的代码库中搜索对特定列的引用(例如)时，很容易错过包含在视图或存储过程中的那些内容.

Some things should definitely-everytime-always be done in SQL. Excluding some exceptions (like the dates thing) for lot of tasks SQL can be very clunky and can leave you with logic in out of the way places. When searching your codebase for references to a specific column (for example) it is easy to miss those contained in a view or stored procedure.

性能始终是一个考虑因素，但取决于您的应用和具体示例，性能可能不是很大.您对可维护性的担忧可能非常有效，而且我提到的一些性能优势非常小，因此请注意过早优化.

Performance is always a consideration but, depending on you app and the specific example, maybe not a big one. Your concerns about maintainability and probably very valid and some of the performance benefits i've mentioned are very slight so beware of premature optimisation.

此外，如果其他系统直接访问数据库(例如，用于报告或导入/导出)，您将受益于数据库中的更多逻辑.例如，如果您想直接从另一个数据源导入用户，则可以在 SQL 中实现诸如电子邮件验证功能之类的可重用功能.

Also, if other systems are accessing the DB directly (eg. for reporting, or imports/exports) you'll benefit from having more logic in the DB. For example, if you want to import users from another datasource directly, something like an email validation function would be reusable is implemented in SQL.

简答:视情况而定.:)

Short answer: it depends. :)

这篇关于在 MySQL 和 PHP 中进行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！