如何通过PHP定义SQL函数？TYPO3环境创建失败排查

MySQL/MariaDB支持自定义服务器端函数，这类函数创建后可用于后续查询，直到被删除、服务器重置或权限变更。我希望直接通过PHP定义这类函数，确保代码运行的所有服务器上都能使用，哪怕没有数据库CLI访问权限。

但在SQL控制台能正常创建的函数，用PHP尝试时全部失败：

• 开发TYPO3扩展时，首先尝试使用QueryBuilder，却报语法错误（推测Doctrine层不支持创建函数的特殊语法）；
• 改用mysqli执行语句，无报错但函数实际未创建。

相关PHP代码如下：

$levenshteinFunction = <<<'LEVENSHTEIN_FUNCTION'
CREATE FUNCTION LEVENSHTEIN(s1 VARCHAR(255) CHARACTER SET utf8, s2 VARCHAR(255) CHARACTER SET utf8)
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR CHARACTER SET utf8;
    -- max strlen=255 for this function
    DECLARE cv0, cv1 VARBINARY(256);

    SET s1_len = CHAR_LENGTH(s1),
        s2_len = CHAR_LENGTH(s2),
        cv1 = 0x00,
        j = 1,
        i = 1,
        c = 0;

    IF (s1 = s2) THEN
      RETURN (0);
    ELSEIF (s1_len = 0) THEN
      RETURN (s2_len);
    ELSEIF (s2_len = 0) THEN
      RETURN (s1_len);
    END IF;

    WHILE (j <= s2_len) DO
        SET cv1 = CONCAT(cv1, CHAR(j)),
          j = j + 1;
    END WHILE;

    WHILE (i <= s1_len) DO
        SET s1_char = SUBSTRING(s1, i, 1),
          c = i,
          cv0 = CHAR(i),
          j = 1;

      WHILE (j <= s2_len) DO
          SET c = c + 1,
            cost = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);

        SET c_temp = ORD(SUBSTRING(cv1, j, 1)) + cost;
        IF (c > c_temp) THEN
          SET c = c_temp;
        END IF;

        SET c_temp = ORD(SUBSTRING(cv1, j+1, 1)) + 1;
        IF (c > c_temp) THEN
          SET c = c_temp;
        END IF;

        SET cv0 = CONCAT(cv0, CHAR(c)),
            j = j + 1;
      END WHILE;

      SET cv1 = cv0,
          i = i + 1;
    END WHILE;

    RETURN (c);
  END $$
LEVENSHTEIN_FUNCTION;
                
$levenshteinratioFunction = <<<'LEVENSHTEINRATIO_FUNCTION'
CREATE FUNCTION LEVENSHTEINRATIO( s1 VARCHAR(255), s2 VARCHAR(255) )
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, max_len INT;
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
    IF s1_len > s2_len THEN
      SET max_len = s1_len;
    ELSE
      SET max_len = s2_len;
    END IF;
    RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
  END $$
LEVENSHTEINRATIO_FUNCTION;

$LS = [
    'show function status;',
    'drop function IF EXISTS levenshtein ;',
    'drop function IF EXISTS levenshteinratio ;',
    'DELIMITER $$',
    $levenshteinFunction,
    'DELIMITER ;',
    'DELIMITER $$',
    $levenshteinratioFunction,
    'DELIMITER ;',
    'show function status;'
];

/*
// first attempt
$output ='';
foreach ($LS as  $lsStatement) {
    $output .= '*** '.$lsStatement.' ***'.PHP_EOL;
    $levenshteinQuery = $this->createQuery();
    $levenshteinQuery->statement($lsStatement,[]);
    $result = $levenshteinQuery->execute();
    $output .= print_r($result->toArray(), false). PHP_EOL.PHP_EOL;
    //\TYPO3\CMS\Core\Utility\DebugUtility::debug($result,'result of '.substr($lsStatement,0,30));
    unset($levenshteinQuery);
}
echo $output;
die();
*/

$mysqliConnection = new \mysqli(
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['host'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['user'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['password'],
    $GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['dbname']
);

$output = '';
foreach ($LS as  $lsStatement) {
    $output .= '*** '.$lsStatement.' ***'.PHP_EOL;
    $result = $mysqliConnection->query($lsStatement);
    $output .= print_r($result, true). PHP_EOL.PHP_EOL;
}
$mysqliConnection->close();
echo '<pre>'.PHP_EOL.$output.'</pre>';
die();

调试输出显示后续查询报错undefined function levenshtein，但在SQL CLI中定义函数后，后续查询可正常运行。

内容的提问来源于stack exchange，提问作者Bernd Wilke πφ