嗯,Sami Kuhmonen对我的问题的评论实际上已经消耗了所有可能性。
但是我必须选择任何有效的解决方案,所以我决定从GitHub复制PHP Doctrine的
Doctrine_Inflector:unaccent
方法。 MySQL
的实现看起来肯定不好看,但我猜嵌套REPLACE()
函数是在MySQL
中执行多字符串替换的唯一方式。
这是整个TRIGGER
:
CREATE DEFINER = CURRENT_USER TRIGGER `product_BEFORE_INSERT` BEFORE INSERT ON `product` FOR EACH ROW
# new value
SET NEW.name_uri = LOWER(
REPLACE(
# ugly set of nested replaces
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
# the original value
NEW.name,
# every accented latin character represented by its ASCII code
# is replaced with normalized letter
CHAR(195,128), 'A'), CHAR(195,129), 'A'), CHAR(195,130), 'A'), CHAR(195,131), 'A'), CHAR(195,132), 'A'), CHAR(195,133), 'A'),
CHAR(195,135), 'C'), CHAR(195,136), 'E'), CHAR(195,137), 'E'), CHAR(195,138), 'E'), CHAR(195,139), 'E'), CHAR(195,140), 'I'),
CHAR(195,141), 'I'), CHAR(195,142), 'I'), CHAR(195,143), 'I'), CHAR(195,145), 'N'), CHAR(195,146), 'O'), CHAR(195,147), 'O'),
CHAR(195,148), 'O'), CHAR(195,149), 'O'), CHAR(195,150), 'O'), CHAR(195,153), 'U'), CHAR(195,154), 'U'), CHAR(195,155), 'U'),
CHAR(195,156), 'U'), CHAR(195,157), 'Y'), CHAR(195,159), 's'), CHAR(195,160), 'a'), CHAR(195,161), 'a'), CHAR(195,162), 'a'),
CHAR(195,163), 'a'), CHAR(195,164), 'a'), CHAR(195,165), 'a'), CHAR(195,167), 'c'), CHAR(195,168), 'e'), CHAR(195,169), 'e'),
CHAR(195,170), 'e'), CHAR(195,171), 'e'), CHAR(195,172), 'i'), CHAR(195,173), 'i'), CHAR(195,174), 'i'), CHAR(195,175), 'i'),
CHAR(195,177), 'n'), CHAR(195,178), 'o'), CHAR(195,179), 'o'), CHAR(195,180), 'o'), CHAR(195,181), 'o'), CHAR(195,182), 'o'),
CHAR(195,182), 'o'), CHAR(195,185), 'u'), CHAR(195,186), 'u'), CHAR(195,187), 'u'), CHAR(195,188), 'u'), CHAR(195,189), 'y'),
CHAR(195,191), 'y'),
# Decompositions for Latin Extended-A
CHAR(196,128), 'A'), CHAR(196,129), 'a'), CHAR(196,130), 'A'), CHAR(196,131), 'a'), CHAR(196,132), 'A'), CHAR(196,133), 'a'),
CHAR(196,134), 'C'), CHAR(196,135), 'c'), CHAR(196,136), 'C'), CHAR(196,137), 'c'), CHAR(196,138), 'C'), CHAR(196,139), 'c'),
CHAR(196,140), 'C'), CHAR(196,141), 'c'), CHAR(196,142), 'D'), CHAR(196,143), 'd'), CHAR(196,144), 'D'), CHAR(196,145), 'd'),
CHAR(196,146), 'E'), CHAR(196,147), 'e'), CHAR(196,148), 'E'), CHAR(196,149), 'e'), CHAR(196,150), 'E'), CHAR(196,151), 'e'),
CHAR(196,152), 'E'), CHAR(196,153), 'e'), CHAR(196,154), 'E'), CHAR(196,155), 'e'), CHAR(196,156), 'G'), CHAR(196,157), 'g'),
CHAR(196,158), 'G'), CHAR(196,159), 'g'), CHAR(196,160), 'G'), CHAR(196,161), 'g'), CHAR(196,162), 'G'), CHAR(196,163), 'g'),
CHAR(196,164), 'H'), CHAR(196,165), 'h'), CHAR(196,166), 'H'), CHAR(196,167), 'h'), CHAR(196,168), 'I'), CHAR(196,169), 'i'),
CHAR(196,170), 'I'), CHAR(196,171), 'i'), CHAR(196,172), 'I'), CHAR(196,173), 'i'), CHAR(196,174), 'I'), CHAR(196,175), 'i'),
CHAR(196,176), 'I'), CHAR(196,177), 'i'), CHAR(196,178), 'IJ'), CHAR(196,179), 'ij'), CHAR(196,180), 'J'), CHAR(196,181), 'j'),
CHAR(196,182), 'K'), CHAR(196,183), 'k'), CHAR(196,184), 'k'), CHAR(196,185), 'L'), CHAR(196,186), 'l'), CHAR(196,187), 'L'),
CHAR(196,188), 'l'), CHAR(196,189), 'L'), CHAR(196,190), 'l'), CHAR(196,191), 'L'), CHAR(197,128), 'l'), CHAR(197,129), 'L'),
CHAR(197,130), 'l'), CHAR(197,131), 'N'), CHAR(197,132), 'n'), CHAR(197,133), 'N'), CHAR(197,134), 'n'), CHAR(197,135), 'N'),
CHAR(197,136), 'n'), CHAR(197,137), 'N'), CHAR(197,138), 'n'), CHAR(197,139), 'N'), CHAR(197,140), 'O'), CHAR(197,141), 'o'),
CHAR(197,142), 'O'), CHAR(197,143), 'o'), CHAR(197,144), 'O'), CHAR(197,145), 'o'), CHAR(197,146), 'OE'), CHAR(197,147), 'oe'),
CHAR(197,148), 'R'), CHAR(197,149), 'r'), CHAR(197,150), 'R'), CHAR(197,151), 'r'), CHAR(197,152), 'R'), CHAR(197,153), 'r'),
CHAR(197,154), 'S'), CHAR(197,155), 's'), CHAR(197,156), 'S'), CHAR(197,157), 's'), CHAR(197,158), 'S'), CHAR(197,159), 's'),
CHAR(197,160), 'S'), CHAR(197,161), 's'), CHAR(197,162), 'T'), CHAR(197,163), 't'), CHAR(197,164), 'T'), CHAR(197,165), 't'),
CHAR(197,166), 'T'), CHAR(197,167), 't'), CHAR(197,168), 'U'), CHAR(197,169), 'u'), CHAR(197,170), 'U'), CHAR(197,171), 'u'),
CHAR(197,172), 'U'), CHAR(197,173), 'u'), CHAR(197,174), 'U'), CHAR(197,175), 'u'), CHAR(197,176), 'U'), CHAR(197,177), 'u'),
CHAR(197,178), 'U'), CHAR(197,179), 'u'), CHAR(197,180), 'W'), CHAR(197,181), 'w'), CHAR(197,182), 'Y'), CHAR(197,183), 'y'),
CHAR(197,184), 'Y'), CHAR(197,185), 'Z'), CHAR(197,186), 'z'), CHAR(197,187), 'Z'), CHAR(197,188), 'z'), CHAR(197,189), 'Z'),
CHAR(197,190), 'z'), CHAR(197,191), 's'),
# Euro Sign
CHAR(226,130,172), 'E'),
# GBP (Pound) Sign
CHAR(194,163), ''),
'Ä', 'Ae'), 'ä', 'ae'), 'Ü', 'Ue'), 'ü', 'ue'),
'Ö', 'Oe'), 'ö', 'oe'), 'ß', 'ss'),
# Norwegian characters
'Å', 'Aa'), 'Æ', 'Ae'), 'Ø', 'O'), 'æ', 'a'), 'ø', 'o'), 'å', 'aa')
, ' ', '_'));