MySQL :: SQL 函数（条件、格式化）何时应移至中间件层？

发布于 2024-11-09 20:06:24 字数 756 浏览 0 评论 0原文

多年来，我已经养成了使用 if 条件进行格式化，或使用组函数和日期函数进行日期格式化的习惯（不完全确定是好是坏，这是问题的部分原因）。

示例：

// Grouping: get total goals & assists for each player (1 = goal, 2,3 = assist)
SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

// Date formatting:
DATE_FORMAT(gameDate, '%b %e') AS displayDate

// Text formatting:
IF(gameType='S','(S)','') AS gameTypeDisplay

我可以继续按原样进行，但我正在转向基于 ORM 的系统，其中“select *”是默认值，而字段替换（以实现上述目的）虽然可能，但只会让事情变得一团糟当你的查询中有多个条件需要处理时（在我看来，最好使用纯粹的、可读的 SQL 或 ORM DSL，但不要将两者混合在一起）。

那么，将上面的文本格式条件转移到中间件层会涉及哪些成本？例如1000行查询结果；循环，并对每一行应用条件？

基本上，我想清理中间件/ORM 层代码并卸载便利的 SQL 函数，但前提是我不会因为中间件层执行大量额外处理而拖慢服务器的速度。

服务器设置为 32 位 CentOS 5、JVM（Groovy 中间件）和最新的 MySQL 5。

原文

Over the years I've gotten into the habit (not entirely sure if good or bad, part of the reason for the question) of using if conditionals for formatting, or group functions, and date functions for date formatting.

Examples:

// Grouping: get total goals & assists for each player (1 = goal, 2,3 = assist)
SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

// Date formatting:
DATE_FORMAT(gameDate, '%b %e') AS displayDate

// Text formatting:
IF(gameType='S','(S)','') AS gameTypeDisplay

I would be fine continuing on as is, but I'm moving to an ORM based system where "select *" is the default, and field substitution (to achieve the above), while possible, just makes an utter mess of things when you have multiple conditions to handle in your query (basically better to have pure, readable SQL, or ORM DSL, but not a mish mash of the two, imo).

So, what are the costs involved in moving say, the text formatting conditional above to middleware layer? e.g. 1,000 row query result; loop, and apply conditional for each row?

Basically I'd like to clean up middleware/ORM layer code and offload convenience SQL functions, but only if I'm not going to drag the server to a slow grind as middleware layer performs tons of extra processing.

Server setup is 32-bit CentOS 5, JVM (Groovy middleware) with MySQL 5 latest.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぃ弥猫深巷。 2024-11-16 20:06:24

不知道你的数据结构，但我的猜测是：

// Grouping: get total goals & assists for each player (1 = goal, 2,3 = assist)
SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

无论如何，这些东西无法在应用程序级别有效完成，因此你需要将其保留在 SQL 中。

// Date formatting:
DATE_FORMAT(gameDate, '%b %e') AS displayDate

// Text formatting:
IF(gameType='S','(S)','') AS gameTypeDisplay

这两项都可以在应用程序中高效完成。事实上，应该< /em>，因为这样做意味着数据库的工作量会减少。

Dunno your data structure, but my guess is:

// Grouping: get total goals & assists for each player (1 = goal, 2,3 = assist)
SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

This stuff can't be done efficiently at the app level anyway, so you'll want to keep it in the SQL.

// Date formatting:
DATE_FORMAT(gameDate, '%b %e') AS displayDate

// Text formatting:
IF(gameType='S','(S)','') AS gameTypeDisplay

Both of these can be done efficiently in the app. In fact, should, since doing so will mean less work for your database.

回复收藏 0 原文

埖埖迣鎅 2024-11-16 20:06:24

你可以使用视图。

create or replace view fancy_table AS 
   SELECT DATE_FORMAT(gameDate, '%b %e') AS gameDate,
        IF(gameType='S','(S)','') AS gameType, other, cols, here FROM table;

那么你的 orm 可以在视图上使用 * 。

select * from fancy_table;

you could use a view.

create or replace view fancy_table AS 
   SELECT DATE_FORMAT(gameDate, '%b %e') AS gameDate,
        IF(gameType='S','(S)','') AS gameType, other, cols, here FROM table;

then your orm can use * on the view.