quote_identifier()

• 返回字符串作为引用的标识符，即给字符串前后加上反撇，对于已存在的反撇直接替换为两个反撇，当SQL语句文本中存在着保留字或者是反撇号(`)字符时，该函数可以快速帮助添加反撇

  • 该函数在MySQL 5.7.14中新增

• 参数：

  • in_identifier TEXT：要作为引用标识符的文本字符串

• 返回值：一个TEXT文本值

• 定义语句

DROP FUNCTION IF EXISTS quote_identifier;

DELIMITER $$

-- https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
-- Maximum supported length for any of the current identifiers in 5.7.5+ is 256 characters.
-- Before that, user variables could have any length.
--
-- Based on Paul Dubois' suggestion in Bug #78823/Bug #22011361.
CREATE DEFINER='root'@'localhost' FUNCTION quote_identifier(in_identifier TEXT)
    RETURNS TEXT CHARSET UTF8
    COMMENT '
            Description
            -----------

            Takes an unquoted identifier (schema name, table name, etc.) and
            returns the identifier quoted with backticks.

            Parameters
            -----------

            in_identifier (TEXT):
              The identifier to quote.

            Returns
            -----------

            TEXT

            Example
            -----------

            mysql> SELECT sys.quote_identifier(''my_identifier'') AS Identifier;
            +-----------------+
            | Identifier      |
            +-----------------+
            | `my_identifier` |
            +-----------------+
            1 row in set (0.00 sec)

            mysql> SELECT sys.quote_identifier(''my`idenfier'') AS Identifier;
            +----------------+
            | Identifier    |
            +----------------+
            | `my``idenfier` |
            +----------------+
            1 row in set (0.00 sec)
            '
    SQL SECURITY INVOKER
    DETERMINISTIC
    NO SQL
BEGIN
    RETURN CONCAT('`', REPLACE(in_identifier, '`', '``'), '`');
END$$

DELIMITER ;

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-quote-identifier.html

---

上一篇： ps_thread_trx_info()函数 | 下一篇： sys_get_config()函数