如何更优雅地在MySQL存储过程中构建动态查询

你的问题核心是动态SQL拼接的可读性和安全性问题，原代码不仅引号嵌套繁琐，还存在SQL注入风险（直接拼接用户输入的表名、列名和参数）。下面提供几种更优雅的解决方案：

1. 参数绑定分离动态与静态部分 #

把固定的SQL结构和动态的表名/列名分离，可变参数用占位符?绑定，避免手动转义引号，同时提升安全性。表名和列名无法用占位符，仍需拼接，但要做合法性校验：

BEGIN
    INSERT IGNORE INTO tag (cod, tag) VALUES (cod_tag_in, tag_in);

    -- 校验动态表名和列名的合法性（可选但推荐，防止注入）
    IF table_in NOT IN ('allowed_table1', 'allowed_table2') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table name';
    END IF;
    IF table_field_in NOT IN ('allowed_column1', 'allowed_column2') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid column name';
    END IF;

    -- 拼接动态结构，参数用占位符
    SET @query = CONCAT(
        "INSERT INTO ", table_in, " (cod, ", table_field_in, ", tag_cod) ",
        "VALUES (?, ?, (SELECT cod FROM tag WHERE tag = ?))"
    );

    -- 绑定参数
    SET @app_cod = app_cod_in;
    SET @section_cod = section_cod_in;
    SET @tag = tag_in;

    PREPARE stmt FROM @query;
    EXECUTE stmt USING @app_cod, @section_cod, @tag;
    DEALLOCATE PREPARE stmt;
END

优势：参数不用加引号，避免嵌套混乱，同时防止参数层面的SQL注入。

2. 使用MySQL 8.0.19+的HEREDOC语法 #

如果你的MySQL版本≥8.0.19，可以用多行字符串字面量（HEREDOC），直接编写SQL模板，不用频繁拼接引号，可读性大幅提升：

BEGIN
    INSERT IGNORE INTO tag (cod, tag) VALUES (cod_tag_in, tag_in);

    -- 合法性校验同上
    IF table_in NOT IN ('allowed_table1', 'allowed_table2') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table name';
    END IF;
    IF table_field_in NOT IN ('allowed_column1', 'allowed_column2') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid column name';
    END IF;

    -- 用HEREDOC编写SQL模板，动态部分直接拼接
    SET @query = CONCAT(
        `INSERT INTO %s (cod, %s, tag_cod)
         VALUES (?, ?, (SELECT cod FROM tag WHERE tag = ?))`,
        table_in, table_field_in
    );

    SET @app_cod = app_cod_in;
    SET @section_cod = section_cod_in;
    SET @tag = tag_in;

    PREPARE stmt FROM @query;
    EXECUTE stmt USING @app_cod, @section_cod, @tag;
    DEALLOCATE PREPARE stmt;
END

优势：SQL结构直观，和普通SQL写法一致，不用处理大量引号嵌套。

3. 使用FORMAT函数简化拼接 #

如果版本较低，HEREDOC不支持，可以用FORMAT()函数，用%s作为占位符代替拼接的部分，减少引号嵌套：

BEGIN
    INSERT IGNORE INTO tag (cod, tag) VALUES (cod_tag_in, tag_in);

    -- 合法性校验同上
    IF table_in NOT IN ('allowed_table1', 'allowed_table2') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table name';
    END IF;
    IF table_field_in NOT IN ('allowed_column1', 'allowed_column2') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid column name';
    END IF;

    SET @query = FORMAT(
        "INSERT INTO %s (cod, %s, tag_cod) VALUES (?, ?, (SELECT cod FROM tag WHERE tag = ?))",
        table_in, table_field_in
    );

    SET @app_cod = app_cod_in;
    SET @section_cod = section_cod_in;
    SET @tag = tag_in;

    PREPARE stmt FROM @query;
    EXECUTE stmt USING @app_cod, @section_cod, @tag;
    DEALLOCATE PREPARE stmt;
END

优势：比直接用CONCAT更简洁，占位符清晰，减少引号出错概率。

关键注意事项 #

• 动态表名/列名必须校验：因为无法用占位符，必须确保输入的表名和列名是预先允许的列表，防止SQL注入（比如攻击者传入恶意表名执行删表操作）。
• 会话变量作用域：@开头的是会话级变量，要注意不要和其他会话逻辑冲突，必要时可以在存储过程结束后手动清空（比如SET @query = NULL;）。

内容的提问来源于stack exchange，提问作者user12706087