mysql零宽度空格
1.问题引出:
查询数据时,用mybatis的mapper查询数据时,居然没查到数据,但是数据库又存在该条数据,后发生奇怪的一幕 ------两条一摸一样的sql居然一条查到数据,一条查不到数据。
-- A查询 -不能查到数据
select
code
from
tm_technology_activity
where
code = 'AXAEQEB8-A'
and
sale_region_code = '1'
;
-- B查询 - 能查到数据
select
code
from
tm_technology_activity
where
code = 'AXAEQEB8-A'
and
sale_region_code = '1'
;
A查询结果:
B查询结果:
2.问题排查:
- A查询改造,前后加上%后,能查到数据—问题似乎更有趣了
-- A查询 -不能查到数据
select
*
from
tm_technology_activity
where
code like '%AXAEQEB8-A%'
and
sale_region_code = '1'
;
改造后A查询结果:
卧槽,还能这样吗,难道有黑子
会不会字符编码不一致呀,抱着试试的心态:
编码工具:https://bianma.bmcx.com/#google_vignette
- A查询 ‘AXAEQEB8-A’ 的\xXX格式:
\x41\x58\x41\x45\x51\x45\x42\x38\x2D\x41
- B查询 ‘AXAEQEB8-A’ 的\xXX格式:
\x41\x58\x41\x45\x51\x45\x42\x38\x2D\x41\xE2\x80\x8B
3.分析编码
两段字符数据,其中包含了十六进制编码(\x41, \x58, 等),并且它们可能是某种编码方式的文本表示。将它们解码成可读的文本,以便更好地理解它们之间的关系。
十六进制编码解码
首先,我们将十六进制编码转换成普通的文本。
-
第一段数据
\x41\x58\x41\x45\x51\x45\x42\x38\x2D\x41
我们可以将每个十六进制值转换为相应的字符:\x41 -> A \x58 -> X \x41 -> A \x45 -> E \x51 -> Q \x45 -> E \x42 -> B \x38 -> 8 \x2D -> - \x41 -> A
因此,第一段十六进制数据解码后是:AXAEQEB8-A
-
第二段数据
\x41\x58\x41\x45\x51\x45\x42\x38\x2D\x41\xE2\x80\x8B
我们将十六进制值转换为相应的字符:\x41 -> A \x58 -> X \x41 -> A \x45 -> E \x51 -> Q \x45 -> E \x42 -> B \x38 -> 8 \x2D -> - \x41 -> A \xE2\x80\x8B -> (这是一个零宽度空格字符,通常不可见)
因此,第二段十六进制数据解码后是:AXAEQEB8-A
如此amaze,之前这带有零宽度的字符是如何存进数据库,不会有阴谋吧,离职前故意加的,玩归玩,闹归闹,具体原因就不得而知了,有知道的大佬也可帮我分析下。
4.抽象的解决方案
要从数据库中去除零宽度字符,可以使用SQL的字符串处理函数。以下是一些常用的数据库系统中的示例:
4.1 单个解决
- msql
UPDATE your_table
SET your_column = REPLACE(your_column, CHAR(8203), '');
这里 CHAR(8203) 是零宽度空格的ASCII值。
- PostgreSQL
UPDATE your_table
SET your_column = REPLACE(your_column, CHR(8203), '');
- SQLite
UPDATE your_table
SET your_column = REPLACE(your_column, X'E2808B', '');
在SQLite中,X'E2808B' 是零宽度空格的十六进制表示。
- SQL Server
UPDATE your_table
SET your_column = REPLACE(your_column, NCHAR(8203), '');
在这些示例中,your_table 是你的表名,your_column 是你需要处理的列名。这些命令会扫描列中的零宽度字符并将其替换为空字符串。你可以根据你的数据库系统选择合适的命令。
4.2 多个解决
在 MySQL 中,如果你希望配置全局属性来使数据库匹配或处理某些特定的字符(例如零宽度字符),你可能需要调整一些配置或执行全局性的数据清理操作。然而,MySQL 本身并没有直接的全局属性来处理零宽度字符。相反,你可以采取以下方法来处理这些字符:
4.2.1 使用存储过程或触发器
如果你的需求是自动处理数据中的零宽度字符,你可以创建存储过程或触发器来实现。虽然这不是一种全局设置,但它可以确保所有插入或更新操作都会清理掉这些字符。
- 创建触发器的示例
DELIMITER //
CREATE TRIGGER clean_zero_width_chars
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
SET NEW.your_column = REPLACE(NEW.your_column, CHAR(8203), '');
END;
//
DELIMITER ;
- 更新已有数据的示例
你可以创建一个存储过程来清理现有数据:
DELIMITER //
CREATE PROCEDURE clean_zero_width_chars()
BEGIN
UPDATE your_table
SET your_column = REPLACE(your_column, CHAR(8203), '');
END;
//
DELIMITER ;
- 然后调用存储过程:
CALL clean_zero_width_chars();
4.2.2 使用 COLLATE 和 CHARACTER SET
MySQL 的 COLLATE 和 CHARACTER SET 设置影响字符匹配和排序行为,但它们不直接处理零宽度字符。你可以在查询中使用这些设置来调整字符匹配规则。例如:
SELECT * FROM your_table
WHERE your_column COLLATE utf8mb4_bin LIKE '%AXAEQEB8-A%';
4.2.3 定期数据清理脚本
可以创建一个定期执行的脚本(例如,通过事件调度器)来清理表中的零宽度字符。这样,即使数据库设置中没有专门的全局选项,数据仍然可以保持干净。
- 使用 MySQL 事件调度器的示例
首先,确保事件调度器已启用:
SET GLOBAL event_scheduler = ON;
然后,创建一个定期清理数据的事件:
CREATE EVENT clean_zero_width_chars_event
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
UPDATE your_table
SET your_column = REPLACE(your_column, CHAR(8203), '');
END;
MySQL 中没有直接的全局设置来自动处理零宽度字符。你可以通过创建触发器、存储过程、事件调度器等方式来实现数据的清理和管理。务必在执行这些操作之前备份数据,以避免意外的数据丢失或其他问题。
补充-显示零宽度字符
-
软件:notepad++
-
版本: 8.5.3
-
A|B查询显示
备注WZSP打错了,是ZWSP -
数据库修复
UPDATE ti_activity
SET activity_code = 'AXAEQEB8-A'
WHERE id = 2787
;
- 别用REPLACE(your_column, CHR(8203), ‘’),执行了没有用,具体原因,不知
各位哥哥~~,有好的解决方案,记得留言分享哦,不胜感激。