Description:
MySQL char type has different result when using and without index
for example
CREATE TABLE `insp` (
`a` char(6) DEFAULT NULL,
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into insp values('a\r');
insert into insp values('a\n');
insert into insp values('a\t');
mysql>select a,hex(a),length(a) from insp FORCE INDEX(idx_a) where a <'a ';
Empty set (0.00 sec)
mysql>select a,hex(a),length(a) from insp IGNORE INDEX(idx_a) where a <'a ';
+------+--------+-----------+
| a | hex(a) | length(a) |
+------+--------+-----------+
| 610D | 2 |
| a
| 610A | 2 |
| a | 6109 | 2 |
+------+--------+-----------+
The storage layer remove the trailing spaces when comparing the data。
Some effective results are filtered out.
for exmple 'a\r', '\t' are smaller than 'a ', but they are bigger than 'a' after removed the trailing spaces.
How to repeat:
CREATE TABLE `insp` (
`a` char(6) DEFAULT NULL,
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into insp values('a\r');
insert into insp values('a\n');
insert into insp values('a\t');
mysql>select a,hex(a),length(a) from insp FORCE INDEX(idx_a) where a <'a ';
Empty set (0.00 sec)
mysql>select a,hex(a),length(a) from insp IGNORE INDEX(idx_a) where a <'a ';
+------+--------+-----------+
| a | hex(a) | length(a) |
+------+--------+-----------+
| 610D | 2 |
| a
| 610A | 2 |
| a | 6109 | 2 |
+------+--------+-----------+
Description: MySQL char type has different result when using and without index for example CREATE TABLE `insp` ( `a` char(6) DEFAULT NULL, KEY `idx_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; insert into insp values('a\r'); insert into insp values('a\n'); insert into insp values('a\t'); mysql>select a,hex(a),length(a) from insp FORCE INDEX(idx_a) where a <'a '; Empty set (0.00 sec) mysql>select a,hex(a),length(a) from insp IGNORE INDEX(idx_a) where a <'a '; +------+--------+-----------+ | a | hex(a) | length(a) | +------+--------+-----------+ | 610D | 2 | | a | 610A | 2 | | a | 6109 | 2 | +------+--------+-----------+ The storage layer remove the trailing spaces when comparing the data。 Some effective results are filtered out. for exmple 'a\r', '\t' are smaller than 'a ', but they are bigger than 'a' after removed the trailing spaces. How to repeat: CREATE TABLE `insp` ( `a` char(6) DEFAULT NULL, KEY `idx_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; insert into insp values('a\r'); insert into insp values('a\n'); insert into insp values('a\t'); mysql>select a,hex(a),length(a) from insp FORCE INDEX(idx_a) where a <'a '; Empty set (0.00 sec) mysql>select a,hex(a),length(a) from insp IGNORE INDEX(idx_a) where a <'a '; +------+--------+-----------+ | a | hex(a) | length(a) | +------+--------+-----------+ | 610D | 2 | | a | 610A | 2 | | a | 6109 | 2 | +------+--------+-----------+