list_drop()

• 与list_add()函数类似，只是是从传入列表中删除传入字符串

  • 此函数在MySQL 5.7.9中新增

• 参数：

  • in_list TEXT：要修改值的列表变量名称，注意：该参数只能传入变量类型值，不能是字符串值，详见函数定义语句注释部分示例
  • in_drop_value TEXT：要从列表变量中删除的文本值

• 返回值：一个TEXT文本值

• 定义语句

DROP FUNCTION IF EXISTS list_drop;

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION list_drop (
        in_list TEXT,
        in_drop_value TEXT
    )
    RETURNS TEXT
    COMMENT '
            Description
            -----------

            Takes a list, and a value to attempt to remove from the list, and returns the resulting list.

            Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.

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

            in_list (TEXT):
              The comma separated list to drop a value from

            in_drop_value (TEXT):
              The value to drop from the input list

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

            TEXT

            Example
            --------

            mysql> select @@sql_mode;
            +-----------------------------------------------------------------------------------------------+
            | @@sql_mode                                                                                    |
            +-----------------------------------------------------------------------------------------------+
            | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
            +-----------------------------------------------------------------------------------------------+
            1 row in set (0.00 sec)

            mysql> set sql_mode = sys.list_drop(@@sql_mode, ''ONLY_FULL_GROUP_BY'');
            Query OK, 0 rows affected (0.03 sec)

            mysql> select @@sql_mode;
            +----------------------------------------------------------------------------+
            | @@sql_mode                                                                |
            +----------------------------------------------------------------------------+
            | ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
            +----------------------------------------------------------------------------+
            1 row in set (0.00 sec)

            '
    SQL SECURITY INVOKER
    DETERMINISTIC
    CONTAINS SQL
BEGIN

    IF (in_drop_value IS NULL) THEN
        SIGNAL SQLSTATE '02200'
          SET MESSAGE_TEXT = 'Function sys.list_drop: in_drop_value input variable should not be NULL',
              MYSQL_ERRNO = 1138;
    END IF;

    IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
        -- return the list as it was passed in
        RETURN in_list;
    END IF;

    -- ensure that leading / trailing commas are remove, support values with either spaces or not between commas
    RETURN (SELECT TRIM(BOTH ',' FROM REPLACE(REPLACE(CONCAT(',', in_list), CONCAT(',', in_drop_value), ''), CONCAT(', ', in_drop_value), '')));

END$$

DELIMITER ;

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

---

上一篇： list_add()函数 | 下一篇： ps_is_account_enabled()函数