优化 MySQL 查询以避免不必要的用户定义函数调用
我有一个查询,它在 SELECT 语句中对用户定义的函数进行多次调用。函数 (vfget) 从字符串中包含的 key=value 对返回值。
查询是否可以只调用该函数一次并将其存储在变量中,以便可以在同一查询中重用?
目前我的查询是:
SELECT
CASE WHEN(right(vfget(appmod_params, 'service'), 3) = '_dd' OR right(vfget(appmod_params, 'service'), 3) = '_wp')
THEN left(vfget(appmod_params, 'service'), length(vfget(appmod_params, 'service'))-3)
ELSE vfget(appmod_params, 'service')
END
FROM ota1003
LIMIT 10
I have a query that makes several calls within a SELECT statement to a user-defined function. The function (vfget) returns the value back from key=value pairs contained within a string.
Is it possible for the query to just call the function once and store this in a variable so that it can be reused within the same query?
Currently my query is:
SELECT
CASE WHEN(right(vfget(appmod_params, 'service'), 3) = '_dd' OR right(vfget(appmod_params, 'service'), 3) = '_wp')
THEN left(vfget(appmod_params, 'service'), length(vfget(appmod_params, 'service'))-3)
ELSE vfget(appmod_params, 'service')
END
FROM ota1003
LIMIT 10
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
或者这样:
MySQL
中的嵌套查询会被缓冲,因此效率较低,尽管只有 10 条记录,这应该不是什么大问题。or this:
Nested queries in
MySQL
are buffered and hence less efficient, though with only 10 records this should not be much of a problem.