I was wondering if there is a best practice regarding the formatting of SQL output. Is it better to do this on PHP side or in the SQL statement itself ? My SGBD is MySQL.
我想知道是否有关于SQL输出格式的最佳实践。在PHP端或SQL语句本身做这个更好吗?我的SGBD是MySQL。
5 个解决方案
#1
1
Unless your formatting affects ordering (see below), you're probably better of doing all the formatting in PHP.
除非你的格式化影响排序(见下文),否则你可能更擅长在PHP中进行所有格式化。
- Passing complex/formatted data though all the application layers leaves you with a handicap of a sort. You should rather pass simple/primitive values and format them just before displaying them to the user. This way, you can use these values for other purposes, not only for display.
通过所有应用程序层传递复杂/格式化数据会给您带来一些障碍。您应该在将它们显示给用户之前传递简单/原始值并对其进行格式化。这样,您可以将这些值用于其他目的,而不仅仅用于显示。
The primary reason you'll want to format your data in SQL is when it affects item selection or order, for example:
您希望在SQL中格式化数据的主要原因是它会影响项目选择或订单,例如:
SELECT complex_expression(x) as fx FROM t ORDER BY fx LIMIT 20
- Doing math in SQL is useful when it somehow limits or orders data in the returned dataset. If you think you can do the same in PHP, you probably can, but it's usually much easier in SQL.
在以某种方式在返回的数据集中限制或排序数据时,在SQL中进行数学运算很有用。如果你认为你可以在PHP中做同样的事情,你可能会这样做,但它在SQL中通常要容易得多。
#2
1
What do you mean with "formatting"?
你对“格式化”有什么意思?
If your question is about way of displaying data (data format, currency format, etc.), use PHP for formatting. Formatting is responsibility of presentation layer.
如果您的问题是关于显示数据的方式(数据格式,货币格式等),请使用PHP进行格式化。格式化是表示层的责任。
If you need some additional computations or if you need aggregation results like summaries, the best way is usually to use database features. You don't need to transport lots of data to application server, you spare its memory and processor.
如果您需要一些额外的计算,或者如果您需要聚合结果(如摘要),最好的方法通常是使用数据库功能。您不需要将大量数据传输到应用程序服务器,而是节省内存和处理器。
#3
0
Depends on what kind of formatting you need. Do your math in MySQL. Is faster than using similar PHP functions. If you need basic date formatting you can do it in MySQL, if you need advanced formatting you're better of doing it with PHP.
取决于您需要什么样的格式。在MySQL学习数学。比使用类似的PHP函数更快。如果您需要基本的日期格式化,您可以在MySQL中进行,如果您需要高级格式化,您最好使用PHP。
#4
0
I use mysql for small logic for example you need counting, adding and these kind of stuff..
我使用mysql作为小逻辑,例如你需要计数,添加和这些东西..
If you need to work out complex calculations I would use PHP to not overload the SQL server
如果你需要计算复杂的计算,我会使用PHP来不重载SQL服务器
#5
0
If by formatting you mean formatting numbers, currencies, datetime etc. you should format it in your PHP code. It should be easier for you to revise data formatting functions in your PHP code instead of locating and altering queries.
如果通过格式化表示格式化数字,货币,日期时间等,则应在PHP代码中对其进行格式化。您应该更容易修改PHP代码中的数据格式化函数,而不是查找和更改查询。
And then there are situations where the formatting needs to vary on a per-user basis.
然后有些情况下格式化需要根据每个用户而有所不同。
#1
1
Unless your formatting affects ordering (see below), you're probably better of doing all the formatting in PHP.
除非你的格式化影响排序(见下文),否则你可能更擅长在PHP中进行所有格式化。
- Passing complex/formatted data though all the application layers leaves you with a handicap of a sort. You should rather pass simple/primitive values and format them just before displaying them to the user. This way, you can use these values for other purposes, not only for display.
通过所有应用程序层传递复杂/格式化数据会给您带来一些障碍。您应该在将它们显示给用户之前传递简单/原始值并对其进行格式化。这样,您可以将这些值用于其他目的,而不仅仅用于显示。
The primary reason you'll want to format your data in SQL is when it affects item selection or order, for example:
您希望在SQL中格式化数据的主要原因是它会影响项目选择或订单,例如:
SELECT complex_expression(x) as fx FROM t ORDER BY fx LIMIT 20
- Doing math in SQL is useful when it somehow limits or orders data in the returned dataset. If you think you can do the same in PHP, you probably can, but it's usually much easier in SQL.
在以某种方式在返回的数据集中限制或排序数据时,在SQL中进行数学运算很有用。如果你认为你可以在PHP中做同样的事情,你可能会这样做,但它在SQL中通常要容易得多。
#2
1
What do you mean with "formatting"?
你对“格式化”有什么意思?
If your question is about way of displaying data (data format, currency format, etc.), use PHP for formatting. Formatting is responsibility of presentation layer.
如果您的问题是关于显示数据的方式(数据格式,货币格式等),请使用PHP进行格式化。格式化是表示层的责任。
If you need some additional computations or if you need aggregation results like summaries, the best way is usually to use database features. You don't need to transport lots of data to application server, you spare its memory and processor.
如果您需要一些额外的计算,或者如果您需要聚合结果(如摘要),最好的方法通常是使用数据库功能。您不需要将大量数据传输到应用程序服务器,而是节省内存和处理器。
#3
0
Depends on what kind of formatting you need. Do your math in MySQL. Is faster than using similar PHP functions. If you need basic date formatting you can do it in MySQL, if you need advanced formatting you're better of doing it with PHP.
取决于您需要什么样的格式。在MySQL学习数学。比使用类似的PHP函数更快。如果您需要基本的日期格式化,您可以在MySQL中进行,如果您需要高级格式化,您最好使用PHP。
#4
0
I use mysql for small logic for example you need counting, adding and these kind of stuff..
我使用mysql作为小逻辑,例如你需要计数,添加和这些东西..
If you need to work out complex calculations I would use PHP to not overload the SQL server
如果你需要计算复杂的计算,我会使用PHP来不重载SQL服务器
#5
0
If by formatting you mean formatting numbers, currencies, datetime etc. you should format it in your PHP code. It should be easier for you to revise data formatting functions in your PHP code instead of locating and altering queries.
如果通过格式化表示格式化数字,货币,日期时间等,则应在PHP代码中对其进行格式化。您应该更容易修改PHP代码中的数据格式化函数,而不是查找和更改查询。
And then there are situations where the formatting needs to vary on a per-user basis.
然后有些情况下格式化需要根据每个用户而有所不同。