Which MySQL UTF8 collation can I use that on one hand supports case-insensitive searches ('Hello' == 'hello') but on the other hand does not ignore umlauts in comparisons ('hällö' != 'hallo')?
我可以使用哪种MySQL UTF8排序规则,一方面支持不区分大小写的搜索('Hello'=='hello'),但另一方面不会忽略比较中的变音符号('hällö'!='hallo')?
utf8_unicode_ci/utf8_general_ci seem to do the former but not the latter, utf8_bin does the latter but not the former.
utf8_unicode_ci / utf8_general_ci似乎做前者但不做后者,utf8_bin做后者而不做前者。
At first glance utf8_swedish_ci seems to work, but I am not sure if this does not cause any other problems.
乍一看utf8_swedish_ci似乎有效,但我不确定这是否会导致任何其他问题。
What's the best practice here?
这里的最佳做法是什么?
1 个解决方案
#1
0
Case insensitive search and NOT ignoring umlauts in comparison both work with utf8_swedish_ci:
不区分大小写的搜索和不忽略变音符号都可以与utf8_swedish_ci一起使用:
mysql> CREATE TABLE users (
id INT(11) default NULL auto_increment,
name varchar(60) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_swedish_ci;
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO `users` (`name`) VALUES ('Hello'), ('hällö');
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users WHERE name='hello';
+----+-------+
| id | name |
+----+-------+
| 1 | Hello |
+----+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users WHERE name='hallo';
Empty set (0.00 sec)
#1
0
Case insensitive search and NOT ignoring umlauts in comparison both work with utf8_swedish_ci:
不区分大小写的搜索和不忽略变音符号都可以与utf8_swedish_ci一起使用:
mysql> CREATE TABLE users (
id INT(11) default NULL auto_increment,
name varchar(60) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_swedish_ci;
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO `users` (`name`) VALUES ('Hello'), ('hällö');
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM users WHERE name='hello';
+----+-------+
| id | name |
+----+-------+
| 1 | Hello |
+----+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users WHERE name='hallo';
Empty set (0.00 sec)