数据库与前端的输出格式化 [英] Database vs. Front-End for Output Formatting

问题描述

我已经读过（所有东西都相等）PHP在算术和字符串操作操作通常比MySQL快。在这种情况下，人们在要求数据库做什么和由Web服务器做什么之间划一条线？我们使用存储过程作为我们的数据访问层。我的未写入的规则总是将输出格式化（包括字符串操作和算术）留给web服务器。因此，我们的查询返回：

I've read that (all things equal) PHP is typically faster than MySQL at arithmetic and string manipulation operations. This being the case, where does one draw the line between what one asks the database to do versus what is done by the web server(s)? We use stored procedures exclusively as our data-access layer. My unwritten rule has always been to leave output formatting (including string manipulation and arithmetic) to the web server. So our queries return:

• 未格式化日期


• 空值


• 没有计算值（即列foo和bar的返回值，如果需要显示值foobar，则让web服务器计算foo * bar）


• 缩减字段（除非缩短字段显着缩短，我们希望在数据库级别执行此操作以减少结果集大小）


• 两个单独的列，以允许前端将输出

• unformatted dates
• null values
• no calculated values (i.e. return values for columns "foo" and "bar" and let the web server calculate foo*bar if it needs to display value foobar)
• no substring-reduced fields (except when shortened field is so significantly shorter that we want to do it at database level to reduce result set size)
• two separate columns to let front-end case the output as required

我感兴趣的是关于这是否通常是一个合适的方法或其他人是否知道强大的性能/可维护性考虑的反馈

What I'm interested in is feedback about whether this is generally an appropriate approach or whether others know of compelling performance/maintainability considerations that justify pushing these activities to the database.

注意：我有意将这个问题标记为dbms-agnostic，因为我相信这是一个架构上的考虑而不管其具体的dbms。

Note: I'm intentionally tagging this question to be dbms-agnostic, as I believe this is an architectural consideration that comes into play regardless of one's specific dbms.

推荐答案

我将绘制线如何某些图层可以旋转到位为其他实现。很可能你永远不会使用不同的RDBMS或移动版本的网站，但你永远不知道。

I would draw the line on how certain layers could rotate out in place for other implementations. It's very likely that you will never use a different RDBMS or have a mobile version of your site, but you never know.

数据点的正交越多，它应该是以该形式从数据库释放的。如果在您网站的每个理论版本上，您的值A和B都呈现为A * B，那么您的数据库应将其作为A * B返回，而不是计算客户端。

The more orthogonal a data point is, the closer it should be to being released from the database in that form. If on every theoretical version of your site your values A and B are rendered A * B, that should be returned by your database as A * B and never calculated client side.

假设你有一个格式很像日期的东西。有时你有短日期，长日期，英语日期...一个纯形式应该从数据库返回，然后应该格式化为PHP。

Let's say you have something that's format heavy like a date. Sometimes you have short dates, long dates, English dates... One pure form should be returned from the database and then that should be formatted in PHP.

点工作也是相反的。数据点在其表示/显示中越动态，应该在客户端处理越多。如果字符串A总是作为前六个字符的子字符串，那么将它们从数据库中作为预先子字符串返回。如果子字符串的长度取决于某些因素，例如移动设备为6，网络应用程序为10，则从数据库返回较大的字符串，并在运行时使用PHP格式化。

So the orthogonality point works in reverse as well. The more dynamic a data point is in its representation/display, the more it should be handled client side. If a string A is always taken as a substring of the first six characters, then have that be returned from the database as pre-substring'ed. If the length of the substring depends on some factor, like six for mobile and ten for your web app, then return the larger string from the database and format it at run time using PHP.

这篇关于数据库与前端的输出格式化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！