在严格的字符串比较中

在严格的字符串比较中

本文介绍了如何“按...分组”在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字符

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

    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中设置时,在严格的字符串比较中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

    08-01 03:17