如何“按...分组”在sql_mode = only_full_group_by在MySql中设置时，在严格的字符串比较中？ [英] How to "group by" in strict string comparation when sql_mode=only_full_group_by is setted in 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 
  

但不幸的是，我

SELECT列表的表达式＃1不在GROUP BY子句中，并且包含
nonaggregated列't0.method'，它在GROUP BY子句中的列上不是功能上依赖
;这是与
不兼容的sql_mode = only_full_group_by

那么，什么是正确的方法来做这一切，而不会导致任何编码问题或与sql_mode = only_full_group_by事件不兼容的问题？

注意： >

我无法修改sql_mode选项。

我无法确定数据中是否显示非ascii字符

在生成查询之前，我无法知道使用了什么字符集。所以 collat​​e 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..

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't0.method' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

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:

1. I'm not able to modify the sql_mode option.
2. I'm not able to make sure that there is no non-ascii characters appears in the data
3. 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中设置时，在严格的字符串比较中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！