![在严格的字符串比较中]( "在严格的字符串比较中")
本文介绍了如何“按...分组”在sql_mode = only_full_group_by在MySql中设置时，在严格的字符串比较中？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想在严格字符串比较中对 t0.method 进行分组，即在不同组中的GET，GET和get，所以 BINARY 被添加在逐句出现的 t0.method 字段的前面。但我无法在选择句子中出现的字段中添加 BINARY ，因为当数据中出现非ASCII字符时，这会导致编码问题。。
因此，下面是我最终得到的SQL查询：
SELECT`t0`.`method` as`d0`，
SUM（`t0`.`success`）AS`m0`
FROM`invoke_statistics` as`t0`
GROUP BY BINARY`t0`.`method`
LIMIT 20000
但不幸的是，我
那么，什么是正确的方法来做这一切，而不会导致任何编码问题或与sql_mode = only_full_group_by事件不兼容的问题？
注意： >
我无法修改sql_mode选项。
我无法确定数据中是否显示非ascii字符
在生成查询之前，我无法知道使用了什么字符集。所以 collate utf8_bin 或类似的东西无法工作，因为它可能会导致一些错误，如 COLLATION'UTF8_BIN'对于CHARACTER SET'latin1无效'
我正在做一些关于代码生成的事情，所以其他的答案取决于很多假设不能解决我的问题，我需要一些通用的解决方案 解决方案
）
计划A
p>正确的答案是更改表定义中的排序规则：
CREATE TABLE ...
方法... COLLATE xxx_bin
...
其中xxx是涉及的字符集。那是（显然）， latin1_bin 。 （Latin1_General_CS_AS来自其他供应商，而不是MySQL）。
警告：这将影响所有其他比较（ WHERE 的c>， ORDER BY ， = >。 （这可能也可能不是问题。）
B计划
在查询中做到这一点的方式是：
... GROUP BY方法COLLATE latin1_bin ...
code>
COLLATE 子句可以用于各种各样的事情，包括比较和 ORDER BY 。但是，请注意，它可以防止使用索引进行优化。
I want to group by t0.method in strict string comparation, i.e. "GET ", "GET" and "get" in different group, so a BINARY is added in front of the t0.method field which appears in the group by sentence. but I can't add a BINARY to this field which appears in the select sentence, since this will cause an encoding problem when non-ascii characters appears in the data.
So following is the SQL query I finally get:
SELECT `t0`.`method` AS `d0`,
SUM(`t0`.`success`) AS `m0`
FROM `invoke_statistics` AS `t0`
GROUP BY BINARY `t0`.`method`
LIMIT 20000
But unfortunately, I got the following error message then..
So, what is the correct way to do all this without causing any encoding problem or incompatibility problem with the "sql_mode=only_full_group_by" things?
NOTE:
- I'm not able to modify the sql_mode option.
- I'm not able to make sure that there is no non-ascii characters appears in the data
- I'm not able to know what char set is used before generate a query. so the collate utf8_bin or similar things is not able to work since it may cause some errors like COLLATION 'UTF8_BIN' is not valid for CHARACTER SET 'latin1'
I'm doing something about code generation, so the other's answer which depends on a lot of assumptions can't solve my problem, I need some general solution
解决方案
(There seem to be two questions rolled into one. This addresses the collation problem.)
Plan A
The "right" answer is to change the collation in the table definition:
CREATE TABLE ...
method ... COLLATE xxx_bin
...
Where xxx is the charset involved. That is (apparently), latin1_bin. ("Latin1_General_CS_AS" comes from some other vendor, not MySQL.)
Caveat: This will affect all other comparisons (WHERE, ORDER BY, =) involving the column method. (This may or may not be an issue.)
Plan B
The way to do it in the query is
... GROUP BY method COLLATE latin1_bin ...
A COLLATE clause can be tacked onto a variety of things, including comparisons and ORDER BY. But, note, it prevents the use of an index for optimization.
这篇关于如何“按...分组”在sql_mode = only_full_group_by在MySql中设置时，在严格的字符串比较中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！