代码之家 › 专栏 › 技术社区 › Musikdoktor

不完全正确的字符会导致sql查询出现问题

sql

Musikdoktor · 技术社区 · 5 年前

好吧,这很奇怪。

我试图在数据库中找到nirvana,但由于某种原因sql漏掉了一些条目。

Check this Demo

你知道如何避免这种巫术吗??

1 回复 | 直到 5 年前

Dharman vijay 5 年前

你可以试着跳过一个字母。

select * from test where test.Title like '%nirv%n%'

或

select * from test where test.Title like '%nirv_n_%'

Ahmmed 5 年前

到目前为止,我发现,你可以创建一个函数来翻译所有的西里尔字母,并在你的where条件下使用这个函数。 https://github.com/igstan/sql-utils/blob/master/transliterate.sql


CREATE FUNCTION `transfunc`(original VARCHAR(512)) RETURNS varchar(512) CHARSET utf8
BEGIN
 
DECLARE translit VARCHAR(512) DEFAULT '';
DECLARE len INT(3) DEFAULT 0;
DECLARE pos INT(3) DEFAULT 1;
DECLARE letter CHAR(4);
 
SET original = TRIM(LOWER(original));
SET len = CHAR_LENGTH(original);
 
WHILE (pos <= len) DO
SET letter = SUBSTRING(original, pos, 1);
 
CASE TRUE
 
WHEN letter IN('Ã¡','Ã ','Ã¢','Ã¤','Ã¥','a','a','a','?','?') THEN SET letter = 'a';
WHEN letter IN('c','c','Ã§','c') THEN SET letter = 'c';
WHEN letter IN('d','d','?','?') THEN SET letter = 'd';
WHEN letter IN('Ã©','e','Ã«','Ã¨','Ãª','e','e','?','?') THEN SET letter = 'e';
WHEN letter IN('g','g') THEN SET letter = 'g';
WHEN letter IN('Ã','Ã®','Ã¯','i','Ã®','?','?') THEN SET letter = 'i';
WHEN letter IN('k') THEN SET letter = 'k';
WHEN letter IN('l','l','l','l') THEN SET letter = 'l';
WHEN letter IN('n','n','n','Ã±') THEN SET letter = 'n';
WHEN letter IN('Ã³','Ã¶','Ã¸','Ãµ','Ã´','o','o','?','?') THEN SET letter = 'o';
WHEN letter IN('r','r','?','?') THEN SET letter = 'r';
WHEN letter IN('Å¡','s','?','s','?','?') THEN SET letter = 's';
WHEN letter IN('t','?') THEN SET letter = 't';
WHEN letter IN('Ãº','u','Ã¼','Ã¹','Ã»','u','u','u') THEN SET letter = 'u';
WHEN letter IN('Ã½','?','?') THEN SET letter = 'y';
WHEN letter IN('Å¾','z','z') THEN SET letter = 'z';
 
WHEN letter = '?' THEN SET letter = 'b';
WHEN letter = '?' THEN SET letter = 'v';
WHEN letter = '?' THEN SET letter = 'g';
WHEN letter = '?' THEN SET letter = 'd';
WHEN letter = '?' THEN SET letter = 'zh';
WHEN letter = '?' THEN SET letter = 'z';
WHEN letter = '?' THEN SET letter = 'i';
WHEN letter = '?' THEN SET letter = 'i';
WHEN letter = '?' THEN SET letter = 'k';
WHEN letter = '?' THEN SET letter = 'l';
WHEN letter = '?' THEN SET letter = 'm';
WHEN letter = '?' THEN SET letter = 'n';
WHEN letter = '?' THEN SET letter = 'p';
WHEN letter = '?' THEN SET letter = 't';
WHEN letter = '?' THEN SET letter = 'f';
WHEN letter = '?' THEN SET letter = 'ch';
WHEN letter = '?' THEN SET letter = 'c';
WHEN letter = '?' THEN SET letter = 'ch';
WHEN letter = '?' THEN SET letter = 'sh';
WHEN letter = '?' THEN SET letter = 'shch';
WHEN letter = '?' THEN SET letter = '';
WHEN letter = '?' THEN SET letter = 'y';
WHEN letter = '?' THEN SET letter = 'e';
WHEN letter = '?' THEN SET letter = 'ju';
WHEN letter = '?' THEN SET letter = 'ja';
 
WHEN letter IN ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','x','y','z')
OR letter REGEXP '^[0-9]+$'
THEN SET letter = letter;

ELSE
SET letter = '-';
 
END CASE;
 
SET translit = CONCAT(translit, letter);
SET pos = pos + 1;
END WHILE;
 
WHILE (translit REGEXP '\-{2,}') DO
SET translit = REPLACE(translit, '--', '-');
END WHILE;
 
RETURN TRIM(BOTH '-' FROM translit);
 
END

select * from test where transfunc(test.Title) like '%nirvana%'