问题描述
尝试通过MySQL中的存储过程进行选择时出现以下错误.
Am getting the below error when trying to do a select through a stored procedure in MySQL.
您对这里可能出什么问题有任何想法吗?
Any idea on what might be going wrong here?
表的排序规则是latin1_general_ci，where子句中的列的排序规则是latin1_general_cs.
The collation of the table is latin1_general_ci and that of the column in the where clause is latin1_general_cs.
推荐答案
通常是通过比较两个不兼容的排序规则字符串或尝试将不同排序规则的数据选择到组合列中引起的.
This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column.
子句COLLATE允许您指定查询中使用的排序规则.
The clause COLLATE allows you to specify the collation used in the query.
例如，以下WHERE子句将始终显示您发布的错误:
For example, the following WHERE clause will always give the error you posted:
WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs
您的解决方案是为查询中的两列指定一个共享的排序规则.这是使用COLLATE子句的示例:
Your solution is to specify a shared collation for the two columns within the query. Here is an example that uses the COLLATE clause:
SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;
另一种选择是使用BINARY运算符:
Another option is to use the BINARY operator:
您的解决方案可能看起来像这样:
Your solution might look something like this:
SELECT * FROM table WHERE BINARY a = BINARY b;
或
SELECT * FROM table ORDER BY BINARY a;
这篇关于对“非法混合排序规则"进行故障排除mysql中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！