如何从MySQL表指定列中移除所有表情符号字符？

一、你的查询语句问题分析 #

你写的查询语句：

SELECT bikeshop_name FROM bsdirectory WHERE HEX(`bikeshop_name`) RLIKE "^(..)*F.";

存在两个明显问题：

• 覆盖范围不全：该语句仅能匹配UTF-8编码中以F开头字节的字符，但很多表情符号（比如☕的UTF-8编码是E2 98 95）的首字节是E，会直接被漏掉，无法找出所有含表情的记录。
• 效率低下：HEX()函数加RLIKE的组合无法利用索引，会触发全表扫描，虽然5000条数据影响不大，但绝非最优方案。

二、更优的含表情记录查询方法 #

MySQL 8.0及以上版本 #

直接用正则匹配表情符号的Unicode范围，精准筛选：

SELECT bikeshop_name 
FROM bsdirectory 
WHERE bikeshop_name REGEXP '[\\x{1F600}-\\x{1F64F}\\x{1F300}-\\x{1F5FF}\\x{1F680}-\\x{1F6FF}\\x{2600}-\\x{26FF}]';

MySQL 5.x版本 #

如果你的店铺名称仅包含英文、数字和常规符号，可通过字符长度与字节长度的差异快速筛选（多字节的表情符号会导致两者不等）：

SELECT bikeshop_name 
FROM bsdirectory 
WHERE CHAR_LENGTH(bikeshop_name) != LENGTH(bikeshop_name);

三、移除所有表情符号的解决方案 #

MySQL 8.0及以上版本（推荐） #

用REGEXP_REPLACE函数直接替换掉所有表情符号，可选择精准匹配表情范围，或保留指定合法字符：

方案1：精准移除表情符号

UPDATE bsdirectory 
SET bikeshop_name = REGEXP_REPLACE(bikeshop_name, 
    '[\\x{1F600}-\\x{1F64F}\\x{1F300}-\\x{1F5FF}\\x{1F680}-\\x{1F6FF}\\x{2600}-\\x{26FF}\\x{2700}-\\x{27BF}\\x{1F900}-\\x{1F9FF}\\x{1F1E0}-\\x{1F1FF}]', 
    '');

方案2：保留合法字符（英文、数字、空格、常见标点）

如果店铺名没有非英文的合法特殊字符，直接保留安全字符更简单：

UPDATE bsdirectory 
SET bikeshop_name = REGEXP_REPLACE(bikeshop_name, '[^a-zA-Z0-9\\s\\-\\.,\']', '');

MySQL 5.x版本 #

由于5.x没有REGEXP_REPLACE，需自定义函数来过滤字符：
首先创建过滤函数：

DELIMITER $$
CREATE FUNCTION remove_emojis(str TEXT) RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE len INT DEFAULT CHAR_LENGTH(str);
    DECLARE result TEXT DEFAULT '';
    DECLARE char_code INT;
    WHILE i <= len DO
        SET char_code = ORD(SUBSTRING(str, i, 1));
        -- 保留ASCII范围内的字符（0-127），可根据需求调整范围（比如添加欧洲重音字符）
        IF char_code <= 127 THEN
            SET result = CONCAT(result, SUBSTRING(str, i, 1));
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN result;
END$$
DELIMITER ;

然后执行更新：

UPDATE bsdirectory SET bikeshop_name = remove_emojis(bikeshop_name);

注意：执行UPDATE前建议先备份数据，或用SELECT语句测试替换结果，避免误操作。

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