mysql 强制排序_MySQL隐含地强制查看错误的排序规则

在

MySQL 5.6中,视图似乎隐含地将utf8_general_ci强制转换为latin1_swedish_ci而不是预期的latin1_general_cs.

我的设置：

数据库变量：

mysql> show variables like 'col%';

+----------------------+-------------------+

| Variable_name | Value |

+----------------------+-------------------+

| collation_connection | latin1_general_cs |

| collation_database | latin1_general_cs |

| collation_server | latin1_general_cs |

+----------------------+-------------------+

mysql> show variables like 'char%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

这是我的数据库和表格：

CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs */;

CREATE TABLE `example` (

`username` varchar(20) COLLATE latin1_general_cs NOT NULL,

PRIMARY KEY (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

insert into example values ('user_a');

我的观点是：

create or replace view example_view as

select username

from example

where substring_index(user(), '@', 1) = example.username;

我的问题：

从该视图中选择时,我收到错误：

mysql> select * from example_view;

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '='

当我直接运行select语句时,它可以工作.

据我所知,NOTHING设置为使用latin1_swedish_ci.服务器,数据库,表和列都设置为latin1_general_cs.

以下是MySQL认为每个部分的排序规则：

mysql> select COLLATION(username) as username,

-> COLLATION(user()) as user_func,

-> COLLATION(substring_index(user(), '@', 1)) as substr_func

-> from example;

+-------------------+-----------------+-----------------+

| username | user_func | substr_func |

+-------------------+-----------------+-----------------+

| latin1_general_cs | utf8_general_ci | utf8_general_ci |

+-------------------+-----------------+-----------------+

所以MySQL试图从utf8_general_ci转换为匹配latin1_general_cs.但不知何故,在视图的上下文中,它决定使用latin1_swedish_ci.

我知道我可以使用convert(),但我想避免这种情况(部分是出于好奇,部分是因为很多转换器()会导致丑陋的查询).

我的问题：

为什么MySQL转换为latin1_swedish_ci而不是latin1_general_cs？除了在查询中显式使用convert()之外,我该如何解决这个问题？