MariaDB：在过程中创建动态 PrepareStatement

我正在尝试创建一个存储过程，它返回与可以在图层中应用的过滤器匹配的表的记录。

该过程接收某些变量作为参数，我想构造一个PrepareStatement，将非空变量添加为过滤器。我正在使用MariaDB 10.6.2

我正在处理的表（删除外键）如下所示：

CREATE OR REPLACE TABLE Thesis_Detail(
    thesis_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    year SMALLINT NOT NULL,
    file VARCHAR(255) NOT NULL UNIQUE,
    abstract TEXT NOT NULL,
    uploaded_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX(year),
    FULLTEXT(title)
) DEFAULT CHARACTER SET utf8mb4;

目标本身就是以这种方式创建它

DELIMITER //
CREATE OR REPLACE PROCEDURE UThesis.searchThesisByFilters(
    IN year_in SMALLINT,
    IN title_in VARCHAR(255),
    IN limit_in TINYINT,
    IN offset_in TINYINT
)
BEGIN
    DECLARE first BIT DEFAULT 0;

    SET @sql = 'SELECT TD.title AS title,' ||
               'TD.year AS year,' ||
               'TD.file AS path,' ||
               'TD.abstract AS abstract,' ||
               'TD.thesis_id AS thesis_id ' ||
               'FROM Thesis_Detail TD ';

    IF NOT ISNULL(title_in) THEN
        SET first = 1;
        SET @sql = @sql + ' WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)';
    END IF;

    IF NOT ISNULL(year_in) THEN
        IF first THEN
            SET @sql = @sql + ' WHERE';
        ELSE
            SET @sql = @sql + ' AND';
        END IF;
        SET @sql = @sql + ' TD.year = ?';
    END IF;

    SET @sql = @sql + ' LIMIT ?  OFFSET  ?';

    PREPARE stmt FROM @sql;
    EXECUTE stmt using title_in, year_in, limit_in, offset_in;
    DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

问题是以下行将是动态的，也就是说，它可能有也可能没有title_inor year_in

EXECUTE stmt using title_in, year_in, limit_in, offset_in;
EXECUTE stmt using year_in, limit_in, offset_in;
EXECUTE stmt using title_in, limit_in, offset_in;
EXECUTE stmt using limit_in, offset_in;

这个例子可以通过一个或两个是否为空的组合来解决，但问题是我必须应用更多的过滤器。总共有5 个过滤器，但每个组合的情况最终都很糟糕。有什么想法可以实现这一目标吗？

在第一个链接中，他们使用了 CONCAT，但我不知道这是否会使该过程容易受到 SQL 注入的攻击。

CREATE OR REPLACE PROCEDURE UThesis.searchThesisByFilters(
    IN year_in SMALLINT,
    IN title_in VARCHAR(255),
    IN limit_in TINYINT,
    IN offset_in TINYINT
)
BEGIN
    DECLARE first BIT DEFAULT 0;

    SET @sql = 'SELECT TD.title AS title,' ||
               'TD.year AS year,' ||
               'TD.file AS path,' ||
               'TD.abstract AS abstract,' ||
               'TD.thesis_id AS thesis_id ' ||
               'FROM Thesis_Detail TD ';

    IF NOT ISNULL(title_in) THEN
        SET first = 1;
        SET @sql = @sql + ' WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)';
    END IF;

    IF NOT ISNULL(title_in) THEN
        IF first THEN
            SET @sql = @sql + ' WHERE';
        ELSE
            SET @sql = @sql + ' AND';
        END IF;
        SET @sql = @sql + CONCAT(' TD.year = ', year_in);
    END IF;

    SET @sql = @sql + CONCAT(' LIMIT', limit_in, ' OFFSET ', offset_in);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END //