Блог

Согласно документации, MariaDB позволяет использовать пользовательские функции в сгенерированных определениях столбцов https://mariadb.com/kb/en/generated-columns /

 User-defined functions (UDFs) are supported in expressions for generated columns. However, MariaDB can't check whether a UDF is deterministic, so it is up to the user to be sure that they do not use non-deterministic UDFs with VIRTUAL generated columns. 
  

Я использую MariaDB 10.3.20 и создал детерминированную функцию, но при попытке создать поле происходит сбой. Вот несколько примеров кода.

 CREATE TABLE `json_virt` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `json_arr` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

INSERT INTO json_virt SET json_arr = '["a","b"]' ;
INSERT INTO json_virt SET json_arr = '["c","d"]' ;

DELIMITER //
CREATE OR REPLACE FUNCTION `test`.`json_implode`(`data` TEXT, `sep` TEXT) 
RETURNS text 
CHARSET utf8mb4 
COLLATE utf8mb4_unicode_ci
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN

    DECLARE i INT UNSIGNED DEFAULT 0 ;
    DECLARE v_count INT UNSIGNED DEFAULT JSON_LENGTH(data) ;
    DECLARE v_current_item BLOB DEFAULT NULL ;
    DECLARE v_current_path BLOB DEFAULT NULL ;
    
    DECLARE sep_length INT UNSIGNED DEFAULT CHAR_LENGTH(sep) ;
    
    DECLARE str TEXT DEFAULT '' ;
    
    WHILE i < v_count DO
        SET v_current_path =  CONCAT('$[', i, ']') ;
        
        SET v_current_item = JSON_EXTRACT(data, v_current_path) ;
        SET v_current_item = JSON_UNQUOTE(v_current_item) ;
        
        SET str = CONCAT(str, sep, v_current_item) ;
        
        SET i := i   1;
    END WHILE;
    
    SET str = SUBSTRING(str, sep_length 1) ;
    
    RETURN str ;
    
END //
DELIMITER ;

SELECT 
json_virt.*,
json_implode(json_virt.json_arr, ', ') AS json_imp
FROM json_virt
;

ALTER TABLE `json_virt` ADD `json_str` TEXT AS (json_implodex(json_arr, ', ')) VIRTUAL ;

ALTER TABLE `json_virt` ADD `json_str1` TEXT AS json_implode('["x","y"]', ', ') VIRTUAL ;
  

Функция json_implode() работает хорошо, как вы можете видеть по оператору SELECT, но при попытке выполнить последние два оператора ALTER TABLE оба завершаются неудачей.

Меня это интересует с нескольких разных точек зрения:

1. Почему это не работает?
2. Каков пример UDF, который будет работать для сгенерированного определения столбца?
3. Можно ли использовать многострочный или более сложный код в сгенерированном определении столбца?
4. Есть ли лучший способ выполнить то, что я пытаюсь сделать (взять поле, представляющее собой массив JSON, и разложить его так, чтобы каждый элемент разделялся запятой в виде строки)?

Я вижу, что многие разработчики используют «UDF», когда они имеют в виду, что MySQL / MariaDB вызывает хранимую функцию. То есть процедура, написанная на языке, основанном на ANSI SQL, и динамически развертываемая на работающем сервере MySQL.

В то время как термин UDF используется MySQL / MariaDB для совершенно другой функции.

https://mariadb.com/kb/en/create-function-udf / говорит:

Определяемая пользователем функция (UDF) — это способ расширить MariaDB новой функцией, которая работает как собственная (встроенная) функция MariaDB, такая как ABS () или CONCAT().

UDF должны быть написаны на C, C или другом языке, использующем соглашения о вызовах C, MariaDB должен быть динамически скомпилирован, а ваша операционная система должна поддерживать динамическую загрузку.

Обратите внимание, что Microsoft SQL Server использует UDF для чего-то более похожего на хранимую функцию MySQL / MariaDB. Это может быть источником путаницы.