MySQL implicitly coerces to wrong collation in view
Q: Why is MySQL converting to latin1_swedish_ci
instead of latin1_general_cs
?
Every characterset has a default collation. You can use the SHOW COLLATION
statement to see this. An excerpt from the output shows that latin1_swedish_ci
is the default collation for the latin1
characterset:
Collation Charset Id Default Compiled Sortlen
-------------------- -------- ------ ------- -------- ---------
latin1_german1_ci latin1 5 Yes 1
latin1_swedish_ci latin1 8 Yes Yes 1
latin1_bin latin1 47 Yes 1
latin1_general_ci latin1 48 Yes 1
latin1_general_cs latin1 49 Yes 1
We already know that every table has a default characterset and default collation. With the view definition, MySQL is actually creating a table when the query runs.
In the MySQL vernacular, it's called a "derived table".
(As an aside, MySQL does allow some views can be defined with ALGORITHM=MERGE
instead of the typical and familiar ALGORITHM=TEMPTABLE
. With the MERGE algorithm, we get view handling behavior that's more like the behavior of other relational databases, like Oracle and SQL Server.)
When MySQL creates the derived table, it assigns a characterset along with its the default collation.
That's where the latin1_swedish_ci
is coming from... the default collation for latin1
.
Q2: How do I fix that, other than using CONVERT() explicitly in the query?
You can try specifying a collation without the CONVERT() function:
CREATE VIEW example_view
AS
SELECT username COLLATE latin1_general_cs
FROM example
WHERE SUBSTRING_INDEX(USER(), '@', 1) COLLATE latin1_general_cs = example.username;
(If your client characterset is utf8, then you're likely to encounter an error with that syntax, if you don't also have the CONVERT(... USING ...)
. You can use COLLATE
in conjunction with the CONVERT()
function.
CONVERT(USER() USING latin1) COLLATE latin1_general_cs
NOTE: I don't have any practical experience with stored views; we use inline views all over the place. But we never create stored views, because stored views cause a myriad of problems, way bigger and way more problems than whatever problems the view definition was a solution for.
Not sure what exactly you are asking for, but just to avoid the error message you can:
http://sqlfiddle.com/#!9/2697e/3
create or replace view example_view as
select username
from example
where substring_index(user(), '@', 1) = example.username COLLATE latin1_general_cs;
http://sqlfiddle.com/#!9/bf88d/1