在 MySQL 和 PHP 中进行计算

Doing calculations in MySQL vs PHP

提问by siliconpi

Context:

语境：

• We have a PHP/MySQL application.
• Some portions of the calculations are done in SQL directly. eg: All users created in the last 24 hours would be returned via an SQL query ( NOW() – 1 day)

• 我们有一个 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. Keep all calculations / code / logic in PHP and treat MySQL as a 'dumb' repository of information

A. 将所有计算/代码/逻辑保留在 PHP 中，并将 MySQL 视为“哑巴”信息存储库

His opinion:

他的意见：

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/

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

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).

我正在研究可维护性的观点。他关注的是速度（正如文章所指出的，MySQL 中的某些操作速度更快）。

---

---

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

@bob-the-destroyer @tekretic @OMG Ponies @mu 太短了 @Tudor Constantin @tandu @Harley

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

我同意（并且很明显）高效的 WHERE 子句属于 SQL 级别。但是，例如：

1. Calculating a 24 period using NOW() - 1 day in SQL to select all users created in last 24 hours?
2. Return capitalized first name and last name of all users?
3. Concatenating a string?
4. (thoughts, folks?)

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

Clear examples belonging in the SQL domain:

属于 SQL 域的清晰示例：

1. specific WHERE selections
2. Nested SQL statements
3. Ordering / Sorting
4. Selecting DISTINCT items
5. Counting rows / items

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

采纳答案by Molomby

I'd play to the strengths of each system.

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

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.

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

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.

另一方面，我使用过的大多数数据库平台在处理单个值方面的功能都很差。诸如日期格式化和字符串操作之类的事情在 SQL 中很糟糕，您最好在 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.

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

---

---

Re: specific examples...

回复：具体例子...

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 createdDatecolumn 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.

2. 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.

3. 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.

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

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

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

A few rules of thumb:

一些经验法则：

• Generating incremental ids should happen in the DB.
• Personally, i like my default applied by the DB.
• When selecting, anything that reduces the number of records should be done by the DB.
• Its usually good to do things that reduce the size of the dataset DB-side (like with the strings example above).
• And as you say; ordering, aggregation, sub-queries, joins, etc. should always be DB-side.
• Also, we haven't talked about them but triggers are usually bad/necessary.

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

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

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

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 iseasy to miss those contained in a view or stored procedure.

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

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.

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

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.

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

Short answer: it depends. :)

简短的回答：这取决于。:)

回答by Tudor Constantin

I don't like reinventing the wheel. I also like to use the best tool possible for the task needed to be done, so:

我不喜欢重新发明轮子。我也喜欢使用最好的工具来完成需要完成的任务，所以：

• When I can get the resultset straight from DB without further processing I do it - your case it's a simple query with a simple WHEREclause. Imagine what happens when you have 10 millions users and you get them to PHP, just to need 100 of them - you guessed it - it's very possible for your web server to crash
• When you need to get data from 2 or more tables at once, again, MySQL is muchbetter than PHP
• When you need to count records - the DB is great at it
• I tend to favor application level processing to FK constraints
• Also, I tend to avoid stored procedures, preferring to implement that business logic at application level (unless, of course we are talking about huge data sets).

• 当我可以直接从 DB 获取结果集而无需进一步处理时，我会这样做 - 您的情况是一个带有简单WHERE子句的简单查询。想象一下，当你有 1000 万用户并且你让他们使用 PHP，只需要 100 个用户时会发生什么 - 你猜对了 - 你的 Web 服务器很可能崩溃
• 当您需要从一次2个或多个表中获取数据，同样，MySQL是多比PHP更好
• 当您需要计算记录时 - 数据库非常擅长
• 我倾向于对 FK 约束进行应用程序级处理
• 此外，我倾向于避免存储过程，更喜欢在应用程序级别实现该业务逻辑（当然，除非我们谈论的是巨大的数据集）。

In conclusion, I would say that your colleague is right in the case presented

总之，我想说你的同事在提出的案例中是对的

回答by Harley

If you put half your logic in the database and the other half in the php, then 6 months down the track when you come to make a change it will take you twice as long to figure out what is going on.

如果您将一半的逻辑放在数据库中，另一半放在 php 中，那么在 6 个月后进行更改时，您需要两倍的时间才能弄清楚发生了什么。

Having said that though, your database queries should have just enough logic so that they provide your php with exactly the data it needs. If you are finding yourself looping through thousands of mysql records in your php code, then you are doing something wrong. On the other end of the scale though, if you're running if / else statements in your mysql queries you are also doing something wrong (probably just need to rewrite your query).

尽管如此，您的数据库查询应该有足够的逻辑，以便它们为您的 php 提供它需要的准确数据。如果您发现自己在 php 代码中遍历了数千条 mysql 记录，那么您就做错了。但是，另一方面，如果您在 mysql 查询中运行 if / else 语句，那么您也做错了（可能只需要重写您的查询）。

I'd steer clear of stored procedures. While they are a great concept in theory you can usually accomplish the same result in the php with a much faster development time and you also have the added benefit of knowing where all the logic is.

我会避开存储过程。虽然它们在理论上是一个很好的概念，但您通常可以在 php 中以更快的开发时间完成相同的结果，并且您还可以获得知道所有逻辑在哪里的额外好处。

回答by OMG Ponies

MySQL will scale better as result sets increase. Frankly, treating a database as a "dumb data" repository is a waste of resources...

随着结果集的增加，MySQL 的扩展性会更好。坦率地说，将数据库视为“哑数据”存储库是一种资源浪费......

Maintainability tends to be tainted by familiarity. If you're not familiar with PHP, it wouldn't be your initial choice for maintainability -- would it?

可维护性往往会被熟悉程度所玷污。如果您不熟悉 PHP，它就不会是您维护可维护性的最初选择——是吗？

回答by Somnath Muluk

The time taken to fetch the data in SQL is time consuming but once its done calculations are more over same. It won't be much time consuming either way after data is fetched but doing it smartly in the SQL can give better results for large data sets.

在 SQL 中获取数据所花费的时间很耗时，但一旦完成，计算就会更加相同。获取数据后，无论哪种方式都不会耗费太多时间，但是在 SQL 中巧妙地执行此操作可以为大型数据集提供更好的结果。

If you are fetching data from MYSQL and then doing the calculations in PHP over the fetched data, then its far better to fetch the required result and avoid PHP processing, as it will increase more time.

如果您从 MYSQL 获取数据，然后在 PHP 中对获取的数据进行计算，那么获取所需结果并避免 PHP 处理要好得多，因为它会增加更多时间。

Some basic points:

一些基本点：

1. Date formatting in MYSQL is strong, most formats are available in Mysql. If you have very specific date format then you can do it PHP.

2. String manipulation just suck in SQL, better do that work in PHP. If you have not big string manipulation needed to do, then you can do it in Mysql SELECTs.

3. When selecting, anything that reduces the number of records should be done by the SQL and not PHP

4. Ordering data should always be done in Mysql

5. Aggregation should be always done in Mysql because DB engines are specifically designed for this.

6. Sub-Queries and Joins should always be DB-side. It will reduce your lots of PHP code. When you need to get data from 2 or more tables at once, again, SQL is much better than PHP

7. Want to count records, SQL is great.

1. MYSQL 中的日期格式很强，大多数格式在 Mysql 中都可用。如果您有非常具体的日期格式，那么您可以使用 PHP。

2. 字符串操作在 SQL 中很糟糕，最好在 PHP 中进行。如果您没有需要做的大字符串操作，那么您可以在 Mysql SELECTs 中进行。

3. 选择时，任何减少记录数的事情都应该由 SQL 而不是 PHP 来完成

4. 排序数据应该总是在Mysql中完成

5. 聚合应该总是在 Mysql 中完成，因为数据库引擎是专门为此设计的。

6. 子查询和联接应始终位于 DB 端。它将减少您的大量 PHP 代码。当您需要同时从 2 个或多个表中获取数据时，SQL 比 PHP 好得多

7. 想统计记录，SQL很棒。