mysql - 尝试编写一个存储函数以在 mysql UPDATE SET 语句中使用

Question

我有两个表 TABLE-2 和 TABLE_3

• 表2

Preferred_Name|name_TABLE_3
---------------------------
Pref_Name1    |
Pref_Name2    |
Pref_Name3    |
...etc

• TABLE_3

name
-----
name1
name2
name3
...etc

我正在尝试使用 MATCH (name) AGAINST ('Preferred_Name') 来使用 TABLE_3 中的最佳匹配名称更新 TABLE-2，例如:

SELECT name, MATCH (name) AGAINST ('Preferred_Name') as relevance FROM TABLE_3 WHERE MATCH (name) AGAINST ('Preferred_Name') having relevance=max(MATCH (name) AGAINST ('Preferred_Name'))

但是由于 AGAINST 不接受变量，我尝试通过存储函数来完成此操作，我尝试在 phpmyadmin 中编写一个存储函数以在 UPDATE SET 语句中使用:

UPDATE `TABLE-2` SET `TABLE-2`.`name_TABLE_3` = best_fulltext_match(`TABLE-2`.`Preferred_Name`);

内部代码:

SELECT name, MATCH (name) AGAINST ('Preferred_Name') as relevance FROM TABLE_3 WHERE MATCH (name) AGAINST ('Preferred_Name') having relevance=max(MATCH (name) AGAINST ('Preferred_Name'))

工作正常，并在一次使用一个关键字时显示正确的查询，但当我执行该函数时，我没有得到任何结果。感谢任何帮助。

CREATE DEFINER=`root`@`localhost` FUNCTION `best_fulltext_match`(`search_string` TEXT) RETURNS varchar(600) CHARSET latin1
DETERMINISTIC
BEGIN
RETURN (SELECT `name`, MATCH (`name`) AGAINST ('search_string') as relevance FROM `TABLE_3` WHERE MATCH (`name`) AGAINST ('search_string') having relevance=max(MATCH (`name`) AGAINST ('search_string'))); 
END

Answer 1

MySQL 函数仅返回单个值，因此您应该使用存储过程，而不是从函数返回整个表。

CREATE PROCEDURE `best_fulltext_match`(`search_string` IN TEXT)
CHARSET latin1
BEGIN
        SELECT `name`, MATCH (`name`) AGAINST (`search_string`) as relevance
        FROM `TABLE_3`
        WHERE MATCH (`name`) AGAINST (`search_string`)
        GROUP BY `name`
        HAVING relevance=max(MATCH (`name`) AGAINST (`search_string`))); 
END