我有一个类似于这里发现的问题:How to find rows in SQL that start with the same string (similar rows)?,这个解决方案在MySQL 5.6中工作,而不是5.7。
我有一个包含多列的数据库(t),重要的列是id和filepath,我想要完成的是检索所有文件路径,这些路径具有相同的最后5个字符。以下内容在MySQL5.6中工作,第二个SELECT在5.7中工作得很好:
SELECT id, filepath FROM t
WHERE SUBSTRING(filepath, -5) IN
(
SELECT SUBSTRING(filepath, -5)
FROM t
GROUP BY SUBSTRING(filepath, -5)
HAVING COUNT(*) > 1
)但是当我试图在5.7上运行它时,我得到了错误
Expression #1 of HAVING clause is not in GROUP BY clause and contains
nonaggregated column 't.filepath' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by样本数据:
id filepath
1 /Desktop/file1.txt
2 /Desktop/file2.txt
3 /Desktop/file1.txt我希望返回id 1和3的行。如何为MySQL5.7修复这个问题?
编辑:还有人能为SQL移除副本指出正确的方向吗?因此,我希望删除id 3的条目,但保留id 1和2的条目。
发布于 2017-09-19 18:44:37
请阅读关于主题组和sql_mode only_full_group_by的mysql文档(就像您的错误消息说的那样):https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
我认为将内部查询更改为此可能会解决以下问题:
SELECT SUBSTRING(filepath, -5) AS fpath
FROM t
GROUP BY fpath
HAVING COUNT(fpath) > 1编辑:
关于为什么添加"AS fpath“的问题:添加别名"fpath”只是一种简单的方法。ONLY_FULL_GROUP_BY的要点是,在SELECT、HAVING或ORDER中使用的每个字段也必须在组BY中。
因此,由于多种原因,我添加了fpath-别名:
发布于 2017-09-19 19:05:40
我不喜欢将子查询放在IN()谓词中,因为MySQL倾向于多次运行子查询。
您可以以不同的方式编写查询,将子查询作为派生表放在FROM子句中。这将使MySQL只运行一次子查询。
SELECT id, filepath
FROM (
SELECT SUBSTRING(filepath, -5) AS suffix, COUNT(*) AS count
FROM t
GROUP BY suffix
HAVING count > 1
) AS t1
JOIN t AS t2 ON SUBSTRING(t2.filepath, -5) = t1.suffix但是,这肯定会执行表格扫描,所以这将是一个代价高昂的查询。在进行这样的子字符串比较时,它不能使用索引。
要优化这一点,您可以创建一个包含索引的虚拟列。
ALTER TABLE t
ADD COLUMN filepath_last VARCHAR(10) AS (SUBSTRING_INDEX(filepath, '/', -1)),
ADD KEY (filepath_last);然后您可以像这样查询它,并且至少子查询使用一个索引:
SELECT id, filepath
FROM (
SELECT filepath_last, COUNT(*) AS count
FROM t
GROUP BY filepath_last
HAVING count > 1
) AS t1
STRAIGHT_JOIN t AS t2 ON t2.filepath_last = t1.filepath_last发布于 2017-09-19 19:07:47
在这里找到了为我工作的解决方案:BY
我运行了SELECT @@sql_mode,然后是SET @@sql_mode =,后面是包含第一个查询返回的所有值的字符串,除了only_full_group_by之外,但我仍然感兴趣的是如何在不更改only_full_group_by设置的情况下完成这一任务。
https://stackoverflow.com/questions/46307430
复制相似问题