MySQL:如果我在SQL查询中多次使用函数,每次都会重新计算它? [英] MySQL: If I use a function in SQL query more than one time will it get re-calculated every time?
问题描述
select column1
,column2
,my_function1(第3列)作为f1
,my_function2(column4)作为f2
,my_function3(my_function1(column3),my_function2(column4))作为f3
其中第1列的一些条件
具有f1 >一些值
my_function1(column3)会被调用3次吗?
或者有一些优化/缓存重新使用计算的值?
谢谢
create function determin_rand(i integer)解决方案
返回浮点数DETERMINISTIC
返回rand();
创建函数not_determin_rand(i integer)
返回浮点数
返回rand();
选择determin_rand(1)作为d1,determin_rand(1)作为d2,
not_determin_rand(1)作为nd1,not_determin_rand(1)作为nd2
0.00850549154 0.831901073456 0.133989050984 0.174242004752
由于值不同,函数每次都会被调用。在第一个函数中,我声明它是确定性的,但它没有什么区别。
我为你制作了一个sqlfiddle,让它可以用不同版本的mysql来试用它。
http://sqlfiddle.com/#!2/a8536/2
in MySQL, given the following query:
select column1
, column2
, my_function1(column3) as f1
, my_function2(column4) as f2
, my_function3 (my_function1(column3), my_function2(column4)) as f3
where some condition on column 1
having f1 > some value
does my_function1(column3) will be called 3 times? Or there's some optimization/cache that re-use the calculated value?
thank you
Here's a short experiment to see what happens.
create function determin_rand (i integer)
returns float DETERMINISTIC
return rand();
create function not_determin_rand (i integer)
returns float
return rand();
select determin_rand(1) as d1 , determin_rand(1) as d2,
not_determin_rand(1) as nd1, not_determin_rand(1) as nd2
0.00850549154 0.831901073456 0.133989050984 0.174242004752
Since the values are different, the function is getting called each time. In the first function I declared it deterministic, but it didn't make a difference.
I made a sqlfiddle for you to try it out with different versions of mysql.
http://sqlfiddle.com/#!2/a8536/2
这篇关于MySQL:如果我在SQL查询中多次使用函数,每次都会重新计算它?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!