|
This script:
DROP TABLE IF EXISTS t1;
|
SET NAMES utf8, collation_connection=utf8_general_ci;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8, b INT NOT NULL DEFAULT 0, key(a));
|
INSERT INTO t1 (a) VALUES ('a'),('b'),('c'),('d'),('¢');
|
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN _utf8'¢' and '¢';
|
returns
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| 1 | SIMPLE | t1 | ref | a | a | 33 | const | 1 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
It's using "ref" access. Looks good so far.
Now if I change the session collation to utf8_swedish_ci:
DROP TABLE IF EXISTS t1;
|
SET NAMES utf8, collation_connection=utf8_swedish_ci;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8, b INT NOT NULL DEFAULT 0, key(a));
|
INSERT INTO t1 (a) VALUES ('a'),('b'),('c'),('d'),('¢');
|
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN _utf8'¢' and '¢';
|
It returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 33 | NULL | 1 | Using index condition |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
Notice, it's now using "range" instead of "ref" access. This looks wrong. The value of @@collation_connection should not be important here, because the comparison operation should be done according to the collation of the field "a", which is utf8_general_ci.
|