MySQL用户定义函数 [英] MySQL user defined functions

问题描述

我有一个表，其中包含几列，例如:column_1，column_2和column_3.

I have a table contains a few columns say: column_1, column_2 and column_3.

我在表中追加了一个新列，称为score.我要做的是基于这三列计算分数并轻松调整参数.

I appended an new column to the table called score. What I want to do is calculate the score based on these three columns and tune the parameters easily.

说我的得分公式如下:

score = a * column_1 + b * column_2 + c * column_3

是否可以创建一个udf或进程(以前从未使用过)来轻松做到这一点?

is it possible to create a udf or process(never used before) to easily do that?

所以我有一个类似getScore(a，b，c)的函数 我可以做类似的事情:

so I have a function like getScore(a,b,c) and I could do something like:

select 
    column_1,
    column_2, 
    column_3, 
    getScore(0.5, 0.1, 0.4) as score
from table

或

update table set score = getScore(0.5, 0.1, 0.4)

谢谢！

推荐答案

是.

CREATE FUNCTION `getScore`(`a` DECIMAL(12,4), `b` DECIMAL(12,4), `c` DECIMAL(12,4)) RETURNS DECIMAL(12,4)
BEGIN
    RETURN a + b + c;
END

SELECT getScore(0.3, 0.4, 0.5)
-> 1.2000

但是，如果您需要表中的某些值，则也需要将这些值也包含在内作为参数.

But if you need some values from the table, you need to include those as parameters too.

SELECT getScore(column1, column2, column3, 0.5, 0.1, 0.4) AS score FROM table

这篇关于MySQL用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！