Mysql 函数调用

Mysql function call

提问by Samiul

If I call a function several time then will it execute every time or just execute once and the value will be used then after several time? Example:

如果我多次调用一个函数，那么它是每次都执行还是只执行一次，然后在几次之后使用该值？例子：

 select my_function('filed'),my_function('filed')/field2, 
        (my_function('filed')*field1)/field3,
...... from my_table    where group by filed1;

My question is my_function('filed')will be executed once and then the result will be used in my_function('filed')/field2and (my_function('filed')*field1)/field3or every time my_function('filed')will be called and executed in system level ?

我的问题是 my_function('filed')将执行一次，然后将在系统级别使用my_function('filed')/field2和/(my_function('filed')*field1)/field3或每次my_function('filed')将在系统级别调用和执行结果？

回答by wedev27

Why not use a variable that catch the value of your function. For example:

为什么不使用捕获函数值的变量。例如：

declare var_function (datatype(size)); // just to declare proper data type for your function

set var_function = my_function('filed');

select var_function, var_function/field2, 
        (var_function*field1)/field3,

....from my_table    where group by filed1;

in that case, you'll be going to reuse the function result and no need to repeat the process of the function.

在这种情况下，您将重用函数结果，而无需重复函数过程。

回答by triclosan

It is possible to have some optimization if you declare your function as DETERMINISTIC. But it really should be deterministic:

如果您将函数声明为DETERMINISTIC. 但它确实应该是确定性的：

A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.

Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used. Prior to MySQL 5.0.44, the DETERMINISTIC characteristic is accepted, but not used by the optimizer.

如果一个例程对于相同的输入参数总是产生相同的结果，则它被认为是“确定性的”，否则被认为是“不确定的”。如果例程定义中既未给出 DETERMINISTIC 也未给出 NOT DETERMINISTIC，则默认值为 NOT DETERMINISTIC。要声明函数是确定性的，您必须明确指定 DETERMINISTIC。

对例程性质的评估基于创建者的“诚实”：MySQL 不会检查声明为 DETERMINISTIC 的例程是否没有产生不确定结果的语句。但是，错误声明例程可能会影响结果或影响性能。将非确定性例程声明为 DETERMINISTIC 可能会导致优化器做出不正确的执行计划选择，从而导致意外结果。将确定性例程声明为 NONDETERMINISTIC 可能会导致不使用可用优化，从而降低性能。在 MySQL 5.0.44 之前，接受 DETERMINISTIC 特性，但优化器不使用它。

回答by Najzero

As far as I know (not a mysql pro) it is calling this function every time. Your expalin plan should show that issue.

据我所知（不是 mysql pro）它每次都调用这个函数。您的 expalin 计划应该显示该问题。

If you always call the function with the same argument, rather query it once per row via a sub-query.

如果您总是使用相同的参数调用该函数，请通过子查询每行查询一次。

select funcvalue, funcvalue/field2, (funcvalue*field1)/field3,...... 
from SELECT( my_function('filed') funcvalue, ... your other columns... 
FROM TABLE )
where group by filed1;

回答by Sumit Munot

It's very simple to run the MySQL function.

运行MySQL函数非常简单。

Login to MySQL command prompt using command:

使用以下命令登录 MySQL 命令提示符：

$> mysql -u root -p

Then use the database using:

然后使用数据库使用：

mysql> use database_name

Then run the MySQL function using:

然后使用以下命令运行 MySQL 函数：

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

Instead of procedure we can add any multiple line function in above example.

我们可以在上面的示例中添加任何多行函数，而不是过程。