I know about it, I knew about it all along, but… it’s so easy to fall for it; there’s just so much absurdity!
A CHAR type has a known number of characters. For example, the column:
CountryCode CHAR(3) CHARSET ascii NOT NULL
– is known to have exactly three characters. These could be ‘USA’, ‘FRA’, etc.
What happens with spaces? A SQL CHAR type ignores any trailing spaces; thus, the code ‘GB ‘ (the characters ‘G’, ‘B’, and the space ‘ ‘) is interpreted as ‘GB’. Trailing spaces are not regarded as part of the text. Want to see some absurdity?
CREATE TABLE `c_test` (
`c` char(1) DEFAULT NULL
);
INSERT INTO c_test VALUES ('a');
INSERT INTO c_test VALUES ('b');
INSERT INTO c_test VALUES (' ');
SELECT c, LENGTH(c), c = 'a', c = 'b', c = ' ', LENGTH(' ') FROM c_test;
+------+-----------+---------+---------+---------+-------------+
| c | LENGTH(c) | c = 'a' | c = 'b' | c = ' ' | LENGTH(' ') |
+------+-----------+---------+---------+---------+-------------+
| a | 1 | 1 | 0 | 0 | 1 |
| b | 1 | 0 | 1 | 0 | 1 |
| | 0 | 0 | 0 | 1 | 1 |
+------+-----------+---------+---------+---------+-------------+