mysql 查看函数执行次数,MySQL：如果我在SQL查询中多次使用函数，每次都会重新计算它？...

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.