Mysql总结之去除重复数据方法

逃离我推掉我的手 2022-12-08 05:23 128阅读 0赞

1.Distinct关键字  
SELECT DISTINCT \[字段名\] FROM \[表名\] WHERE \[检索条件字句\]

示例：  
SELECT DISTINCT (B) FROM TEST  
或者 select distinct username from msg;

注意点：  
SELECT DISTINCT \[字段名\]后面不能再跟其他的字段，否则检索出来的记录仍然会含有重复项；实际上,我们上面SQL语句结果集里就只有B字段;(一般情况下，这种结果应该是很难满足需求的)

2.GROUP BY 分组

示例：  
select \* from msg group by terminal\_id;  
注意点：  
使用带有GROUP BY字句的查询语句时,在SELECT列表指定的列要么是GROUP BY 指定的列,要么包含聚合组函数

SELECT timestamp,number,company,parent\_company,product,url FROM contract WHERE timestamp>‘2015-1-1’ and timestamp<‘2020-9-15’ GROUP BY number

*  错误：

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

*  原因：  
    SQL 标准中不允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态 ONLY\_FULL\_GROUP\_BY 来标识是否遵从这一标准，默认为开启状态。

也就是说我们group by 需要与前面的选择字段相同。才可以进行聚合查询，  
但这样的修改查询出来就可能就不是想要的结果了。

*  解决：  
    （1）关闭 ONLY\_FULL\_GROUP\_BY

可以选择关掉 MySQL 的 ONLY\_FULL\_GROUP\_BY 模式。

有两种方式，通过设置 sql\_mode 来关闭。

首先查看变更前的 sql\_mode：

mysql> SELECT @@sql_mode;
    +-----------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +-----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

通过以下脚本关闭 ：

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

再次查询 @@sql\_mode 返回中应该已经没有该模式了。

mysql> SELECT @@sql_mode;
    +----------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                         |
    +----------------------------------------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

第二种是找到 MySQL 配置文件修改并保存。

MySQL 的配置文件名为 my.cnf，可通过以下命令查看你位置：

$ mysql --help | grep cnf
                          order of preference, my.cnf, $MYSQL_TCP_PORT,
    /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

找到后编辑并保存，重启 MySQL 后生效。

[mysqld]
    -sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    +sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

如果文件中没有 sql\_mode 配置项可手动添加上。

因为 ONLY\_FULL\_GROUP\_BY 更加符合 SQL 标准，所以不建议关掉。

（2）ANY\_VALUE()

还可以通过 ANY\_VALUE() 来改造查询语句以避免报错。

使用 ANY\_VALUE() 包裹的值不会被检查，跳过该错误。所以这样是可以的：

SELECT gender, 
    -       last_name
    +       ANY_VALUE(last_name) 
    FROM   employees 
    GROUP  BY gender

（3）添加列间的依赖  
像这个示例中，

SELECT gender, 
           last_name 
    FROM   employees 
    GROUP  BY gender

假如我们让 gender 变成不重复的主键，last\_name 便与 gender 产生了一种关系，即 gender 可唯一确定 last\_name。此时便可进行 GROUP BY 了。因为，之所以报错是因为在进行聚合的时候有不能确定的列参与了进来。

*  总结  
    一般 GROUP BY 会与另外的聚合函数配合使用，比如 COUNT(), SUM() 等。查询所有列无差别地进行 GROUP BY 的情况并不是正常的使用姿势。