blocks|key|554558|text|当MySQL的only_full_group_by模式打开时，这意味着在使用GROUP+BY时将应用严格的ANSI规则。对于您的查询，这意味着如果您选择了GROUP+BY的proof_type列，那么您只能选择两件事：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|554559|proof_type列，或|unordered-list-item|554560|任何其他列的集合|554561|对于其他列的“聚合”，我的意思是将聚合函数(如MIN()、MAX()或AVG()+)与另一列一起使用。因此，在您的示例中，以下查询将是有效的：|554562|SELECT+proof_type,
+++++++MAX(id)+AS+max_id,
+++++++MAX(some_col),
+++++++MIN(some_other_col)
FROM+tbl_customer_pod_uploads
WHERE+load_id+=+'78'+AND
++++++status+=+'Active'
GROUP+BY+proof_type|code-block|syntax|javascript|554563|我看到的绝大多数MySQL+GROUP+BY问题都关闭了严格模式，因此查询正在运行，但结果不正确。在您的例子中，查询根本不运行，迫使您考虑真正想要做什么。|554564|注意:+ANSI扩展了允许在GROUP+BY中选择的内容，还包括在功能上依赖于所选列的列。函数依赖的一个例子是按表中的主键列进行分组。由于主键对于每个记录都是唯一的，因此也将确定任何其他列的值。MySQL是允许这样做的数据库之一(+Server和Oracle+)。|554565|entityMap^0|7|I|12|8|25|8|2E|A|0|0|A|0|0|N|5|T|5|Z|5|0|0|E|8|0|E|8|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|I|3|J|5|H|7|19|8|@]|D|@]|E|$]]|$1|K|3|L|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|M|3|N|5|O|7|1H|8|@]|D|@]|E|$P|Q]]|$1|R|3|S|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|T|3|U|5|6|7|1L|8|@$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|V|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]]|W|$]]

When MySQL's <code>only_full_group_by</code> mode is turned on, it means that strict ANSI SQL rules will apply when using <code>GROUP BY</code>. With regard to your query, this means that if you <code>GROUP BY</code> of the <code>proof_type</code> column, then you can only select two things:

<ul>
<li>the <code>proof_type</code> column, or</li>
<li>aggregates of any other column</li>
</ul>

 
By "aggregates" of other columns, I mean using an aggregate function such as <code>MIN()</code>, <code>MAX()</code>, or <code>AVG()</code> with another column. So in your case the following query would be valid:

<pre><code>SELECT proof_type,
 MAX(id) AS max_id,
 MAX(some_col),
 MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = '78' AND
 status = 'Active'
GROUP BY proof_type
</code></pre>

The vast majority of MySQL <code>GROUP BY</code> questions which I see on SO have strict mode turned off, so the query is running, but with incorrect results. In your case, the query won't run at all, forcing you to think about what you really want to do.

Note: The ANSI SQL extends what is allowed to be selected in <code>GROUP BY</code> by also including columns which are functionally dependent on the column(s) being selected. An example of functional dependency would be grouping by a primary key column in a table. Since the primary key is guaranteed to be unique for every record, therefore the value of any other column would also be determined. MySQL is one of the databases which allows for this (SQL Server and Oracle do not AFAIK).

blocks|key|1581079|text|这|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1581080|SELECT+list的表达式#1不是按子句分组，而是包含非聚合列'returntr_prod.tbl_customer_pod_uploads.id‘，它在功能上不依赖于GROUP子句中的列；这与sql_mode=only_full_group_by不兼容。|blockquote|1581081|只需通过以下命令更改MySQL中的sql模式即可解决，|1581082|SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|code-block|syntax|javascript|1581083|这也适用于我..。我使用它，因为在我的项目中有许多类似这样的查询，所以我只是将这个sql模式更改为only_full_group_by。|1581084|或简单地包含SELECT语句指定的GROUP+BY子句中的所有列。可以让sql_mode保持启用状态。|1581085|谢谢..。:-)|1581086|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|V|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|W|8|@]|9|@]|A|$J|K]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|Z|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|S|$]]

This
<blockquote>
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</blockquote>
will be simply solved by changing the sql mode in MySQL by this command,
<pre><code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code></pre>
This too works for me..
I used this, because in my project there are many Queries like this so I just changed this sql mode to only_full_group_by
OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled.
Thank You... :-)

blocks|key|1778183|text|您可以通过命令禁用sql_mode=only_full_group_by，您可以通过终端或MySql+IDE尝试此操作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1778184|mysql>+set+global+sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql>+set+session+sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';|code-block|syntax|javascript|1778185|entityMap^0|9|R|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can disable
sql_mode=only_full_group_by
by some command you can try this by terminal or MySql IDE

<pre><code>mysql&gt; set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql&gt; set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
</code></pre>

blocks|key|1778213|text|打开WAMP面板并打开MySQL配置文件。在它中搜索"sql_mode“，如果您发现它，将它设置为”否则“，如果找不到它，就将sql_mode=”添加到文件中“。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778214|重新启动MySQL服务器，您就可以开始了.|1778215|编码愉快。|1778216|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Open you WAMP panel and open MySQL configuration file. In it search for "sql_mode" if you find it set it to "" else if you don't find it add sql_mode="" to the file.

Restart the MySQL server and you are good to go...

happy coding.

blocks|key|1581160|text|要使查询在SQL92中合法，必须从select列表中省略name列或在GROUP+BY子句中命名。
如果每个可选特性(+SQL99+)在功能上依赖于按列分组(+GROUP+)，则T301和以后的每个可选特性允许这类非聚合:如果名称和种子之间存在这样的关系，则查询是合法的。例如，这将是客户的一个主要关键。
MySQL+5.7.5和up实现了功能依赖的检测。如果启用了ONLY_FULL_GROUP_BY+SQL模式(默认情况下是这种模式)，则MySQL拒绝选择列表、具有条件或按列表排序的查询，这些查询引用的非聚合列既不是在GROUP子句中命名，也不是在功能上依赖于它们。
通过MySQL+:：MySQL+5.7参考手册:：12.19.3|type|blockquote|depth|inlineStyleRanges|entityRanges|offset|length|data|1581161|您可以通过使用以下命令更改sql模式来解决这个问题：|unstyled|1581162|SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|style|CODE|1581163|还有..。记得重新连接数据库！|1581164|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html^0|81|U|0|0|0|0|21|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$]]|$1|G|3|H|5|F|7|Z|8|@$A|10|B|11|I|J]]|9|@]|C|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|C|$]]|$1|M|3|-4|5|F|7|13|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

<blockquote>
 For the query to be legal in SQL92, the name column must be omitted from the select list or named in the GROUP BY clause.
 
 SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.
 
 MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.
 
 via <a href="https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html" rel="noreferrer">MySQL :: MySQL 5.7 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY</a>
</blockquote>

You can solve it by changing the sql mode with this command:

<code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code>

and ... remember to reconnect the database!!!

blocks|key|1581176|text|从外观上看，我认为按多个列/字段分组不会损害您的结果。你为什么不试着像这样加入这个组呢：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1581177|GROUP+BY+`proof_type`,+`id`|code-block|syntax|javascript|1581178|这将首先按proof_type分组，然后按id分组。我希望这不会改变结果。在某些/大多数情况下，按多列分组会产生错误的结果。|offset|length|style|CODE|1581179|entityMap^0|0|0|5|A|L|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

From how it looks, I think grouping by multiple columns/fields wont hurt your result. Why don't you try adding to the group by like this:

<pre><code>GROUP BY `proof_type`, `id`
</code></pre>

This will group by <code>proof_type</code> first then <code>id</code>. I hope this does not alter the results. In some/most cases group by multiple columns gives wrong results.

blocks|key|551613|text|在ONLY_FULL_GROUP_BY引擎中有一个系统变量MySql。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|551614|MySQLVersion5.7.5：默认情况下启用ONLY_FULL_GROUP_BY+SQL模式|551615|默认情况下，在5.7.5版本之前没有启用：：ONLY_FULL_GROUP_BY。|551616|如果启用了ONLY_FULL_GROUP_BY+SQL模式(默认情况下为5.7.5版本)，则MySQL将拒绝选择列表、HAVING条件或ORDER+BY列表引用非聚合列的查询，这些列既不是在GROUP+BY子句中命名，也不是在功能上依赖于它们。|551617|若要解决此问题，请使用任何一种解决方案(以下为3)。|551618|(1)+PHPMyAdmin|551619|禁用：ONLY_FULL_GROUP_BY模式|551620|如果您正在使用sql_mode，那么更改下面的屏幕截图中提到的phpMyAdmin设置。|551621|📷|atomic|551622|​|551623|编辑sql+mode变量并从值中删除ONLY_FULL_GROUP_BY文本|551624|或|551625|(2)+SQL/命令提示符|551626|通过运行以下命令禁用：ONLY_FULL_GROUP_BY模式。|551627|SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|code-block|syntax|javascript|551628|551629|SELECT+*+(3)不要使用|551630|不会禁用的ONLY_FULL_GROUP_BY模式，但是|551631|在SELECT查询中使用相关列。相关的意思是列，它们要么出现在group+by子句中，要么带有聚合函数的列(MAX、MIN、SUM、COUNT等)。|551632|重要注|551633|使用point(1)+OR+point(2)所做的更改不会永久地设置它，每次重新启动后它都会恢复。|551634|因此，您应该在您的配置文件中设置它(例如，在/etc/mysql/my.cnf部分中的[mysqld]部分)，以便在MySQL重新启动后这些更改仍然有效：|551635|Config文件：/etc/mysql/my.cnf|551636|变量名：sql_mode+或+sql-mode|551637|从值中删除word+ONLY_FULL_GROUP_BY并保存文件。|551638|备注：如果您没有在配置文件中找到sql_mode变量，请在文件末尾插入以下2行|551639|[mysqld]
sql_mode+=+STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|551640|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/807ba4250f8246b55779aae280ea9e87.png|imageAlt^0|1|I|1|I|0|P|I|0|K|1|M|I|0|5|I|1N|6|1W|8|2N|8|0|0|0|E|0|0|2|3|I|0|7|8|7|8|0|0|1|0|0|0|2|8|I|I|0|0|1|0|0|D|0|0|A|B|I|0|0|0|1|0|0|8|9|7|0|8|0|0|4|5|I|0|1|6|V|8|1I|3|1M|3|1Q|3|1U|5|0|0|3|0|2|K|0|M|H|17|8|0|0|8|9|H|0|0|3|D|1|4|8|F|8|0|A|I|A|I|0|0|2|G|8|0|0^^$0|@$1|2|3|4|5|6|7|28|8|@$9|29|A|2A|B|C]|$9|2B|A|2C|B|D]]|E|@]|F|$]]|$1|G|3|H|5|6|7|2D|8|@$9|2E|A|2F|B|D]]|E|@]|F|$]]|$1|I|3|J|5|6|7|2G|8|@$9|2H|A|2I|B|C]|$9|2J|A|2K|B|D]]|E|@]|F|$]]|$1|K|3|L|5|6|7|2L|8|@$9|2M|A|2N|B|D]|$9|2O|A|2P|B|D]|$9|2Q|A|2R|B|D]|$9|2S|A|2T|B|D]]|E|@]|F|$]]|$1|M|3|N|5|6|7|2U|8|@]|E|@]|F|$]]|$1|O|3|P|5|6|7|2V|8|@$9|2W|A|2X|B|C]]|E|@]|F|$]]|$1|Q|3|R|5|6|7|2Y|8|@$9|2Z|A|30|B|C]|$9|31|A|32|B|D]]|E|@]|F|$]]|$1|S|3|T|5|6|7|33|8|@$9|34|A|35|B|C]|$9|36|A|37|B|D]]|E|@]|F|$]]|$1|U|3|V|5|W|7|38|8|@]|E|@$9|39|A|3A|1|3B]]|F|$]]|$1|X|3|Y|5|6|7|3C|8|@]|E|@]|F|$]]|$1|Z|3|10|5|6|7|3D|8|@$9|3E|A|3F|B|D]|$9|3G|A|3H|B|D]]|E|@]|F|$]]|$1|11|3|12|5|6|7|3I|8|@$9|3J|A|3K|B|C]]|E|@]|F|$]]|$1|13|3|14|5|6|7|3L|8|@$9|3M|A|3N|B|C]]|E|@]|F|$]]|$1|15|3|16|5|6|7|3O|8|@$9|3P|A|3Q|B|C]|$9|3R|A|3S|B|D]]|E|@]|F|$]]|$1|17|3|18|5|19|7|3T|8|@]|E|@]|F|$1A|1B]]|$1|1C|3|12|5|6|7|3U|8|@$9|3V|A|3W|B|C]]|E|@]|F|$]]|$1|1D|3|1E|5|6|7|3X|8|@$9|3Y|A|3Z|B|C]|$9|40|A|41|B|C]|$9|42|A|43|B|D]]|E|@]|F|$]]|$1|1F|3|1G|5|6|7|44|8|@$9|45|A|46|B|C]|$9|47|A|48|B|D]]|E|@]|F|$]]|$1|1H|3|1I|5|6|7|49|8|@$9|4A|A|4B|B|D]|$9|4C|A|4D|B|D]|$9|4E|A|4F|B|D]|$9|4G|A|4H|B|D]|$9|4I|A|4J|B|D]|$9|4K|A|4L|B|D]]|E|@]|F|$]]|$1|1J|3|1K|5|6|7|4M|8|@$9|4N|A|4O|B|C]]|E|@]|F|$]]|$1|1L|3|1M|5|6|7|4P|8|@$9|4Q|A|4R|B|D]]|E|@]|F|$]]|$1|1N|3|1O|5|6|7|4S|8|@$9|4T|A|4U|B|D]|$9|4V|A|4W|B|D]]|E|@]|F|$]]|$1|1P|3|1Q|5|6|7|4X|8|@$9|4Y|A|4Z|B|C]|$9|50|A|51|B|D]]|E|@]|F|$]]|$1|1R|3|1S|5|6|7|52|8|@$9|53|A|54|B|C]|$9|55|A|56|B|C]|$9|57|A|58|B|D]|$9|59|A|5A|B|D]]|E|@]|F|$]]|$1|1T|3|1U|5|6|7|5B|8|@$9|5C|A|5D|B|C]|$9|5E|A|5F|B|D]]|E|@]|F|$]]|$1|1V|3|1W|5|6|7|5G|8|@$9|5H|A|5I|B|C]|$9|5J|A|5K|B|D]]|E|@]|F|$]]|$1|1X|3|1Y|5|19|7|5L|8|@]|E|@]|F|$1A|1B]]|$1|1Z|3|-4|5|6|7|5M|8|@]|E|@]|F|$]]]|20|$21|$5|22|23|24|F|$25|26|27|-4]]]]

There is a system variable <code>ONLY_FULL_GROUP_BY</code> in MySql engine.
From Mysql Version 5.7.5: <code>ONLY_FULL_GROUP_BY</code> SQL mode is enabled by default
Before Version 5.7.5: <code>ONLY_FULL_GROUP_BY</code> was not enabled by default.
If the <code>ONLY_FULL_GROUP_BY</code> SQL mode is enabled (which it is by default from version 5.7.5), MySQL rejects queries for which the select list, <code>HAVING</code> condition, or <code>ORDER BY</code> list refer to non-aggregated columns that are neither named in the <code>GROUP BY</code> clause nor are functionally dependent on them.
<h1>To sort out the issue, use any one solution (out of below 3)</h1>
(1) PHPMyAdmin
Disable: <code>ONLY_FULL_GROUP_BY</code> mode
if you are using phpMyAdmin then change the <code>sql_mode</code> setting as mentioned in the below screenshot.
<a href="https://i.stack.imgur.com/5FA9i.png" rel="noreferrer"><img src="https://i.stack.imgur.com/5FA9i.png" alt="enter image description here" /></a>
Edit <code>sql mode</code> variable and remove the <code>ONLY_FULL_GROUP_BY</code> text from the value
OR
(2) SQL/Command prompt
Disable: <code>ONLY_FULL_GROUP_BY</code> mode by running the below command.
<pre class="lang-sql prettyprint-override"><code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code></pre>
OR
(3) Don't use <code>SELECT *</code>
Do not disable the <code>ONLY_FULL_GROUP_BY</code> mode but
Use relevant column in <code>SELECT</code> query. relevant means columns, which are either coming in <code>group by</code> clause or column with the aggregate function (<code>MAX</code>, <code>MIN</code>, <code>SUM</code>, <code>COUNT</code> etc)
<hr />
<h2>Important note</h2>
Changes made by using <code>point(1) OR point(2)</code> does not set it PERMANENTLY, and it will revert after every restart.
So you should set this in your config file (e.g. <code>/etc/mysql/my.cnf</code> in the <code>[mysqld]</code> section), so that the changes remain in effect after MySQL restart:
Config File: <code>/etc/mysql/my.cnf</code>
Variable name: <code>sql_mode</code> OR <code>sql-mode</code>
Remove word <code>ONLY_FULL_GROUP_BY</code> from the value and save the file.
Note: If you have not found <code>sql_mode</code> variable in the config file than please insert below 2 lines at the end of the file
<pre class="lang-sql prettyprint-override"><code>[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
</code></pre>

blocks|key|551663|text|在您的my.ini中，编写以下内容：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|551664|[mysqld]
sql_mode+=+"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"|code-block|syntax|javascript|551665|取决于你的版本。或者：|551666|[mysqld]
sql_mode+=+""|551667|或者简单地删除这个：ONLY_FULL_GROUP_BY|551668|entityMap^0|3|6|0|0|0|0|A|I|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

In your <code>my.ini</code>, write this:

<pre><code>[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
</code></pre>

depend on your version. Or:

<pre><code>[mysqld]
sql_mode = ""
</code></pre>

or simply remove this: <code>ONLY_FULL_GROUP_BY</code>

blocks|key|1778451|text|登录到phpMyAdmin|type|ordered-list-item|depth|inlineStyleRanges|entityRanges|data|1778452|导航到:+Server:+localhost:3306，不要选择任何数据库|1778453|单击顶部菜单中的变量|1778454|搜索"sql模式“并将相应的值编辑为:+NO_ENGINE_SUBSTITUTION|1778455|就这样。|unstyled|1778456|我在我的Ec2上做了这件事，它很有魅力。|1778457|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$]]|$1|K|3|L|5|J|7|T|8|@]|9|@]|A|$]]|$1|M|3|-4|5|J|7|U|8|@]|9|@]|A|$]]]|N|$]]

<ol>
<li>Login to phpMyAdmin </li>
<li>Navigate to : Server: localhost:3306 and do
not select any database</li>
<li>Click on variables from the top menu</li>
<li>Search for "sql mode" and edit the corresponding value to : NO_ENGINE_SUBSTITUTION</li>
</ol>

That's all.

I did this in my Ec2 and it worked like charm.

blocks|key|1778505|text|以下方法解决了我的问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778506|在布温图|1778507|类型：sudo+vi+/etc/mysql/my.cnf|offset|length|style|CODE|1778508|输入A进入插入模式|1778509|在最后一行中，粘贴到两行代码下面：|1778510|[mysqld]
sql_mode+=+STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|code-block|syntax|javascript|1778511|输入esc退出输入模式|1778512|Type+:wq+to+save+and+close+vim.|1778513|键入sudo+service+mysql+restart以重新启动MySQL。|1778514|entityMap^0|0|0|3|P|0|0|0|0|0|0|2|Q|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@$F|13|G|14|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|16|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|17|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|P|7|19|8|@]|9|@]|A|$Q|R]]|$1|W|3|X|5|6|7|1A|8|@$F|1B|G|1C|H|I]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Z|$]]

Below method solved my problem:

In ubuntu 

Type: <code>sudo vi /etc/mysql/my.cnf</code>

type A to enter insert mode

In the last line paste below two line code:

<pre><code>[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
</code></pre>

Type esc to exit input mode

<pre><code>Type :wq to save and close vim.
</code></pre>

Type <code>sudo service mysql restart</code> to restart MySQL.

blocks|key|1581561|text|更新MySQL+8.0|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1581562|您的sql-mode不会像这里提到的那样具有NO_AUTO_CREATE_USER，因为它已经被删除了-+如何设置-sql模式-我的cnf-in-mysql-8|CODE|1581563|[mysqld]
sql-mode+=+"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"|code-block|syntax|javascript|1581564|另外，如果某人没有my.cnf文件，那么他们可以在/etc/mysql/my.cnf中创建一个新的文件，然后添加上面的行。|1581565|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/54011071/3192664^0|0|B|0|2|8|M|J|1H|R|0|0|0|9|6|P|H|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@$9|11|A|12|B|H]|$9|13|A|14|B|H]]|D|@$9|15|A|16|1|17]]|E|$]]|$1|I|3|J|5|K|7|18|8|@]|D|@]|E|$L|M]]|$1|N|3|O|5|6|7|19|8|@$9|1A|A|1B|B|H]|$9|1C|A|1D|B|H]]|D|@]|E|$]]|$1|P|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|Q|$R|$5|S|T|U|E|$V|W]]]]

Update for MySQL 8.0
Your <code>sql-mode</code> will not have <code>NO_AUTO_CREATE_USER</code> as it has been removed as mentioned here - <a href="https://stackoverflow.com/a/54011071/3192664">how-to-set-sql-mode-in-my-cnf-in-mysql-8</a>
<pre><code>[mysqld]
sql-mode = &quot;STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&quot;
</code></pre>
Also if someone doesn't have a <code>my.cnf</code> file then they can create a new one in <code>/etc/mysql/my.cnf</code> and then add the above lines.

blocks|key|551778|text|在Ubuntu|type|unstyled|depth|inlineStyleRanges|entityRanges|data|551779|步骤1：|551780|sudo+vi+/etc/mysql/mysql.conf.d/mysqld.cnf|code-block|syntax|javascript|551781|步骤2:转到最后一行并添加以下内容|551782|sql_mode+=+""|551783|步骤3:保存|551784|步骤4:重新启动mysql服务器。|551785|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

In Ubuntu 

Step 1: 

<pre><code>sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
</code></pre>

Step 2: Go to last line and add the following

<pre><code>sql_mode = ""
</code></pre>

Step 3: Save

Step 4: Restart mysql server.

blocks|key|1778628|text|嗨，不要使用所有的列，只需使用ANY_VALUE(column_name)获取所需的内容。它运转得很好。检查一下。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1778629|例如：|1778630|SELECT+proof_type,any_value("customer_name")+as+customer_name
FROM+`tbl_customer_pod_uploads`
WHERE+`load_id`+=+'78'+AND+`status`+=+'Active'+GROUP+BY+`proof_type`|code-block|syntax|javascript|1778631|entityMap^0|F|M|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Hi instead of taking all columns, just take what you need by using <code>ANY_VALUE(column_name)</code>. It is working perfectly. Just check.
E.g.:
<pre class="lang-sql prettyprint-override"><code>SELECT proof_type,any_value(&quot;customer_name&quot;) as customer_name
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND `status` = 'Active' GROUP BY `proof_type`
</code></pre>

blocks|key|1581660|text|如果您正在使用PhpMyAdmin并取出值：ONLY_FULL_GROUP_BY，搜索"SQL模式“，就可以了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1581661|entityMap^0|M|I|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Search for "SQL mode" if you are using PhpMyAdmin and take off the value: <code>ONLY_FULL_GROUP_BY</code>, just did and it okay.

blocks|key|1581674|text|转到phpmyadmin并打开控制台并执行以下请求|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1581675|SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|code-block|syntax|javascript|1581676|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

go to the phpmyadmin and open the console and execute this request

<pre><code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code></pre>

blocks|key|551804|text|>+sudo+nano+/etc/mysql/my.cnf|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|551805|输入下面|unstyled|551806|[mysqld]
sql_mode+=+""|551807|Ctrl+%2BO+=>+Y=+Ctrl+%2BX|551808|>+sudo+service+mysql+restart|551809|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>&gt; sudo nano /etc/mysql/my.cnf
</code></pre>

Enter below

<pre><code>[mysqld]
sql_mode = ""
</code></pre>

Ctrl + O => Y = Ctrl + X

<pre><code>&gt; sudo service mysql restart
</code></pre>

blocks|key|1778720|text|下面是一种非常快速和简单的永久设置方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778721|注意:运行SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));是临时的，在服务器重新启动时，仍然会出现错误。若要永久修复此问题，请执行以下操作|offset|length|style|CODE|1778722|以root身份登录到您的服务器|ordered-list-item|1778723|在您的终端中运行wget+https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh|1778724|通过运行chmod+%2Bx+disable-strict-mode.sh使脚本可执行|1778725|通过运行./disable-strict-mode.sh运行脚本|1778726|您的工作完成后，将对mysql进行更改并重新启动。|1778727|entityMap^0|0|5|21|0|0|8|34|0|4|V|0|4|O|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|Y|8|@]|9|@]|A|$]]|$1|K|3|L|5|J|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|M|3|N|5|J|7|12|8|@$D|13|E|14|F|G]]|9|@]|A|$]]|$1|O|3|P|5|J|7|15|8|@$D|16|E|17|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|18|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|T|$]]

Here is a really fast and easy way of setting it permanently

NB: running <code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));</code> is temporary and on server restart you will still end up with the error. 
To fix this permanently do the below 

<ol>
<li>Login to your server as root</li>
<li>in your terminal run 
<code>wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh</code></li>
<li>Make the script executable by running <code>chmod +x disable-strict-mode.sh</code></li>
<li>Run the script by running <code>./disable-strict-mode.sh</code></li>
</ol>

And your done , changes will be made to mysql and it will be restarted

blocks|key|1581722|text|我的部分数据库崩溃也有类似的问题。我所做的是通过PHPStorm数据库控制台更改DB中的参数，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1581723|+++SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|code-block|syntax|javascript|1581724|现在工作起来很有魅力|1581725|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I had similar problem with part of my database crushing. What I did is I changed the parameter in the DB via PHPStorm database console like this:
<pre><code> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code></pre>
works like charm now

blocks|key|1581750|text|only_full_group_by+=+on告诉MySQL+engine:当您对显示和抛出错误的结果有疑问时，不要应用GROUP+BY。如果专门告诉您要做什么，那么才会应用它。也就是说，当Group+By是充分和完美的！|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|1581751|only_full_group_by+=+off告诉MySQL+engine:始终应用GROUP+BY，如果您对选择什么结果有疑问，只需随机选择一个！|1581752|如果正确使用GROUP+BY+，则不必关闭！|1581753|示例：|1581754|表:用户|1581755|+id+++%7C++name
----------------
++1++++++ali
++2++++++john
++3++++++ali|code-block|syntax|javascript|1581756|当在GROUP+BY列上使用name时：|1581757|SELECT+*+FROM+users+GROUP+BY+name;|1581758|有两种可能的结果：|1581759|++1++++++ali
++2++++++john+++++|1581760|或|1581761|++2++++++john
++3++++++ali|1581762|MYSQL不知道选择什么结果！因为有不同的id，但两者都有name=ali。|1581763|Solution1:|1581764|仅选择name字段：|1581765|SELECT+name+FROM+users+GROUP+BY+name;|1581766|结果：|1581767|++ali
++john+++++|1581768|这是一个完美的解决方案。删除使GROUP+BY混淆的列。这意味着你知道你在做什么。通常，你不需要|1581769|这些列，但如果需要，请转到Solution3！|1581770|Solution2:|1581771|关掉only_full_group_by。MYSQL将选择两个可能的结果之一随机！(如果您不关心id会选择什么，也没关系，但是请记住，在查询之后立即打开，以防止将来的groupBys中出现意外行为)|1581772|Solution3|1581773|使用像MIN()，MAX()这样的MIN()函数来帮助MYSQL决定它必须选择什么。|1581774|例如：|1581775|SELECT+MAX(id),+name+FROM+users+GROUP+BY+name;|1581776|它将选择具有最大id的ali行：|1581777|++2++++++john+++++
++3++++++ali|1581778|entityMap^0|0|N|1Z|C|1O|8|2N|8|0|0|O|17|8|0|6|8|F|7|6|8|0|0|3|0|0|4|0|0|0|K|2|8|E|4|0|0|0|9|0|0|0|1|0|0|0|12|L|2|T|8|0|0|A|0|0|A|3|4|0|0|0|3|0|0|0|1C|F|8|0|0|N|0|0|A|0|0|24|2|I|1C|2|0|0|9|0|3|5|9|5|H|5|0|0|0|B|3|0|0^^$0|@$1|2|3|4|5|6|7|23|8|@$9|24|A|25|B|C]|$9|26|A|27|B|C]|$9|28|A|29|B|D]|$9|2A|A|2B|B|D]]|E|@]|F|$]]|$1|G|3|H|5|6|7|2C|8|@$9|2D|A|2E|B|C]|$9|2F|A|2G|B|D]]|E|@]|F|$]]|$1|I|3|J|5|6|7|2H|8|@$9|2I|A|2J|B|C]|$9|2K|A|2L|B|C]|$9|2M|A|2N|B|D]]|E|@]|F|$]]|$1|K|3|L|5|6|7|2O|8|@$9|2P|A|2Q|B|C]]|E|@]|F|$]]|$1|M|3|N|5|6|7|2R|8|@$9|2S|A|2T|B|C]]|E|@]|F|$]]|$1|O|3|P|5|Q|7|2U|8|@]|E|@]|F|$R|S]]|$1|T|3|U|5|6|7|2V|8|@$9|2W|A|2X|B|C]|$9|2Y|A|2Z|B|D]|$9|30|A|31|B|D]]|E|@]|F|$]]|$1|V|3|W|5|Q|7|32|8|@]|E|@]|F|$R|S]]|$1|X|3|Y|5|6|7|33|8|@$9|34|A|35|B|C]]|E|@]|F|$]]|$1|Z|3|10|5|Q|7|36|8|@]|E|@]|F|$R|S]]|$1|11|3|12|5|6|7|37|8|@$9|38|A|39|B|C]]|E|@]|F|$]]|$1|13|3|14|5|Q|7|3A|8|@]|E|@]|F|$R|S]]|$1|15|3|16|5|6|7|3B|8|@$9|3C|A|3D|B|C]|$9|3E|A|3F|B|D]|$9|3G|A|3H|B|D]]|E|@]|F|$]]|$1|17|3|18|5|6|7|3I|8|@$9|3J|A|3K|B|C]]|E|@]|F|$]]|$1|19|3|1A|5|6|7|3L|8|@$9|3M|A|3N|B|C]|$9|3O|A|3P|B|D]]|E|@]|F|$]]|$1|1B|3|1C|5|Q|7|3Q|8|@]|E|@]|F|$R|S]]|$1|1D|3|1E|5|6|7|3R|8|@$9|3S|A|3T|B|C]]|E|@]|F|$]]|$1|1F|3|1G|5|Q|7|3U|8|@]|E|@]|F|$R|S]]|$1|1H|3|1I|5|6|7|3V|8|@$9|3W|A|3X|B|C]|$9|3Y|A|3Z|B|D]]|E|@]|F|$]]|$1|1J|3|1K|5|6|7|40|8|@$9|41|A|42|B|C]]|E|@]|F|$]]|$1|1L|3|1M|5|6|7|43|8|@$9|44|A|45|B|C]]|E|@]|F|$]]|$1|1N|3|1O|5|6|7|46|8|@$9|47|A|48|B|C]|$9|49|A|4A|B|D]|$9|4B|A|4C|B|D]]|E|@]|F|$]]|$1|1P|3|1Q|5|6|7|4D|8|@$9|4E|A|4F|B|C]]|E|@]|F|$]]|$1|1R|3|1S|5|6|7|4G|8|@$9|4H|A|4I|B|D]|$9|4J|A|4K|B|D]|$9|4L|A|4M|B|D]]|E|@]|F|$]]|$1|1T|3|1U|5|6|7|4N|8|@]|E|@]|F|$]]|$1|1V|3|1W|5|Q|7|4O|8|@]|E|@]|F|$R|S]]|$1|1X|3|1Y|5|6|7|4P|8|@$9|4Q|A|4R|B|D]]|E|@]|F|$]]|$1|1Z|3|20|5|Q|7|4S|8|@]|E|@]|F|$R|S]]|$1|21|3|-4|5|6|7|4T|8|@]|E|@]|F|$]]]|22|$]]

only_full_group_by = on tells MySQL engine: Do not apply <code>GROUP BY</code> when you have doubt about what results to show and throw an error. Only apply it if <code>Group By</code> specifically tells you what to do. i.e. when the <code>Group By</code> is full and perfect!
only_full_group_by = off tells MySQL engine: always apply <code>GROUP BY</code> and if you have doubt about what results to choose, just pick one randomly!
You don't have to turn it off if you use <code>GROUP BY</code> properly!
Example:
Table: users
<pre><code> id | name
----------------
 1 ali
 2 john
 3 ali
</code></pre>
When you use <code>GROUP BY</code> on the <code>name</code> column:
<pre><code>SELECT * FROM users GROUP BY name;
</code></pre>
There are two possible results:
<pre><code> 1 ali
 2 john 
</code></pre>
OR
<pre><code> 2 john
 3 ali
</code></pre>
MYSQL does not know what result to choose! Because there are different <code>id</code>s but both have <code>name=ali</code>.
Solution1:
only selecting the <code>name</code> field:
<pre><code>SELECT name FROM users GROUP BY name;
</code></pre>
result:
<pre><code> ali
 john 
</code></pre>
This is a perfect solution. removing columns that makes <code>GROUP BY</code> confused. This means you know what you're doing. Usually, you do not need 
those columns, but if you need them, go to Solution3!
Solution2:
Turning off <code>only_full_group_by</code>. MYSQL will pick one of the two possible results RANDOMLY!! (It's ok if you do not really care what <code>id</code> it will choose, but remember to turn it on immediately after your query to prevent unexpected behaviors in future groupBys)
Solution3
Use an <code>Aggregate</code> function like <code>MIN()</code>, <code>MAX()</code> to help MYSQL to decide what it must choose.
For example:
<pre><code>SELECT MAX(id), name FROM users GROUP BY name;
</code></pre>
It will choose the <code>ali</code> row which has the maximum id:
<pre><code> 2 john 
 3 ali
</code></pre>

blocks|key|551840|text|我面临着这个问题，下面的查询不够|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|551841|SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));|code-block|syntax|javascript|551842|因为我使用的是存储过程。执行此查询后，我必须删除过程并再次创建。|551843|entityMap^0|E|2|0|0|7|4|M|9|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

I was facing this issue and the following query wasn't enough
<pre><code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</code></pre>
because I was using Stored Procedure. After executing this query I had to drop the procedure and create again.

blocks|key|554844|text|在运行查询之前，请运行以下查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|554845|SET+SQL_MODE+=+'';|code-block|syntax|javascript|554846|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Before running you query run bellow query
<pre><code>SET SQL_MODE = '';
</code></pre>

blocks|key|1778986|text|对于本地使用CentOS/RHEL+(Linux服务器)和XAMPP的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778987|你好，我找到了一个完全不同的解决方案，我希望这能帮上忙。(看起来可能有点过分了，但这确实对我有效，不像其他解决方案)|1778988|我正在运行CentOS+7，并且我的所有代码都在本地工作，但是当我将它上传到服务器时，我开始得到这个问题解决的错误。|offset|length|style|BOLD|1778989|几个小时后，我尝试从不同的角度看待这个问题，我记得我的本地设置使用XAMPP，并且我认为我使用的是MySQL+8+(MySQL+8安装在我的服务器上)。|1778990|但是在从XAMPP登录到MySql之后，我得到了下一个输出：|1778991|mysql+-u+root+-p+//Login


Welcome+to+the+MariaDB+monitor.++Commands+end+with+;+or+\g.
Your+MariaDB+connection+id+is+27
Server+version:+10.4.18-MariaDB+mariadb.org+binary+distribution

Copyright+(c)+2000,+2018,+Oracle,+MariaDB+Corporation+Ab+and+others.

Type+'help;'+or+'\h'+for+help.+Type+'\c'+to+clear+the+current+input+statement.|code-block|syntax|javascript|1778992|是的，您可能也注意到了，XAMPP使用+MariaDB+，所以解决方案是卸载mysql并安装MariaDB。|1778993|在运行Centos+7时，执行以下步骤：|1778994|从服务器中删除mysql|ordered-list-item|1778995|#+yum+remove+mysql+mysql-server|1778996|更新您的系统(这是常见的，每次我们安装新的东西)|1778997|#+sudo+yum++-y+update|1778998|将MariaDB添加到存储库|1778999|#+sudo+tee+/etc/yum.repos.d/MariaDB.repo<<EOF+
[mariadb]
name+=+MariaDB
baseurl+=+http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF|1779000|更新缓存|1779001|#+sudo+yum+makecache+fast|1779002|安装MariaDB|1779003|#+sudo+yum+-y+install+MariaDB-server+MariaDB-client|1779004|将MariaDB添加到启动(每次系统重新启动时启动MariaDB+)|1779005|#sudo+systemctl+enable+--now+mariadb|1779006|然后，您可以通过运行下一个命令来保护您的安装：|1779007|#+sudo+mysql_secure_installation|1779008|最后一个命令将开始一个进程，您可以在其中设置密码和其他选项。|1779009|在此之后，不要忘记添加非根用户并授予它必要的特权，这是因为您不能在应用程序中使用root用户(是的，我以后必须解决这个问题)。|1779010|使用根帐户的第一个登录：|1779011|#+mysql+-u+root+-p|1779012|然后添加用户并授予特权：|1779013|CREATE+USER+'YourUserName'@localhost+IDENTIFIED+BY+'YourPassword';
GRANT+ALL+PRIVILEGES+ON+*.*+TO+'YourUserName'@localhost+IDENTIFIED+BY+'YourPassword'+WITH+GRANT+OPTION;|1779014|最后，您必须创建数据库，导入表/数据/触发器/过程。|1779015|现在，您将能够运行您的代码没有任何问题，就像您的本地设置。(可能您必须安装mysql扩展，以防您也不得不在本地安装mysql扩展)。|1779016|entityMap^0|0|0|5|8|0|X|5|1D|7|0|0|0|J|9|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|27|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|28|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|29|8|@$F|2A|G|2B|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|2C|8|@$F|2D|G|2E|H|I]|$F|2F|G|2G|H|I]]|9|@]|A|$]]|$1|L|3|M|5|6|7|2H|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|2I|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|2J|8|@$F|2K|G|2L|H|I]]|9|@]|A|$]]|$1|U|3|V|5|6|7|2M|8|@]|9|@]|A|$]]|$1|W|3|X|5|Y|7|2N|8|@]|9|@]|A|$]]|$1|Z|3|10|5|P|7|2O|8|@]|9|@]|A|$Q|R]]|$1|11|3|12|5|Y|7|2P|8|@]|9|@]|A|$]]|$1|13|3|14|5|P|7|2Q|8|@]|9|@]|A|$Q|R]]|$1|15|3|16|5|Y|7|2R|8|@]|9|@]|A|$]]|$1|17|3|18|5|P|7|2S|8|@]|9|@]|A|$Q|R]]|$1|19|3|1A|5|Y|7|2T|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|P|7|2U|8|@]|9|@]|A|$Q|R]]|$1|1D|3|1E|5|Y|7|2V|8|@]|9|@]|A|$]]|$1|1F|3|1G|5|P|7|2W|8|@]|9|@]|A|$Q|R]]|$1|1H|3|1I|5|Y|7|2X|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|P|7|2Y|8|@]|9|@]|A|$Q|R]]|$1|1L|3|1M|5|6|7|2Z|8|@]|9|@]|A|$]]|$1|1N|3|1O|5|P|7|30|8|@]|9|@]|A|$Q|R]]|$1|1P|3|1Q|5|6|7|31|8|@]|9|@]|A|$]]|$1|1R|3|1S|5|6|7|32|8|@]|9|@]|A|$]]|$1|1T|3|1U|5|6|7|33|8|@]|9|@]|A|$]]|$1|1V|3|1W|5|P|7|34|8|@]|9|@]|A|$Q|R]]|$1|1X|3|1Y|5|6|7|35|8|@]|9|@]|A|$]]|$1|1Z|3|20|5|P|7|36|8|@]|9|@]|A|$Q|R]]|$1|21|3|22|5|6|7|37|8|@]|9|@]|A|$]]|$1|23|3|24|5|6|7|38|8|@]|9|@]|A|$]]|$1|25|3|-4|5|6|7|39|8|@]|9|@]|A|$]]]|26|$]]

<h2>For The ones using CentOS/RHEL (Linux server) and XAMPP Locally</h2>
Hello, I've come to a very different solution and I hope this helps somone. (May look overkill but this really worked for me unlike other solutions)
I am running CentOS 7 and all my code was working locally, but when I uploaded it to my server I started getting the error which this question addresses.
After some hours I tried viewing the problem from a different angle, I remembered that my local setup uses XAMPP and I thought I was using MySQL 8 (MySQL 8 was installed on my server).
But after logging in into MySql from XAMPP I got the next output:
<pre><code>mysql -u root -p //Login


Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.4.18-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
</code></pre>
Yes you may have noticed the same, XAMPP uses MariaDB, so the solution was to uninstall mysql and install MariaDB
And as I am running Centos 7 the steps to do this are the ones below:
<ol>
<li>Remove mysql from your server</li>
</ol>
<hr />
<pre><code># yum remove mysql mysql-server
</code></pre>
<ol start="2">
<li>Update your system (this is common everytime we install something new)</li>
</ol>
<hr />
<pre><code># sudo yum -y update
</code></pre>
<ol start="3">
<li>Add MariaDB to repository</li>
</ol>
<hr />
<pre><code># sudo tee /etc/yum.repos.d/MariaDB.repo&lt;&lt;EOF 
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
</code></pre>
<ol start="4">
<li>Update cache</li>
</ol>
<hr />
<pre><code># sudo yum makecache fast
</code></pre>
<ol start="5">
<li>Install MariaDB</li>
</ol>
<hr />
<pre><code># sudo yum -y install MariaDB-server MariaDB-client
</code></pre>
<ol start="6">
<li>Add MariaDB to the startup (Start MariaDB every time your system gets restarted)</li>
</ol>
<hr />
<pre><code>#sudo systemctl enable --now mariadb
</code></pre>
<h2>Then you can secure your installation by running the next command:</h2>
<pre><code># sudo mysql_secure_installation
</code></pre>
This last command will begin a process where you can set password, and other options.
After that dont forget to add non-root user and grant it the necessary privileges, that's because you can't use root user in your applications (Yes I had to figure that out later)
<h2>First Login using the root account:</h2>
<pre><code># mysql -u root -p
</code></pre>
<h2>Then add your user and grant privileges:</h2>
<pre><code>CREATE USER 'YourUserName'@localhost IDENTIFIED BY 'YourPassword';
GRANT ALL PRIVILEGES ON *.* TO 'YourUserName'@localhost IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;
</code></pre>
And finally you have to create your database, import your tables/data/triggers/procedures.
Now you will be able to run your code without any problems, just as your local setup. (Maybe you will have to install mysql extensions in case you had to do it locally too).

blocks|key|1779115|text|还可以简单地将GROUP_CONCAT()添加到非聚合列中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1779116|就像GROUP_CONCAT(your_column)|CODE|1779117|entityMap^0|7|E|0|2|P|0^^$0|@$1|2|3|4|5|6|7|K|8|@$9|L|A|M|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|N|8|@$9|O|A|P|B|H]]|D|@]|E|$]]|$1|I|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|J|$]]

You can also simply add GROUP_CONCAT() to the nonaggregated columns.
like <code>GROUP_CONCAT(your_column)</code>

blocks|key|551896|text|登录到phpMyAdmin，导航到:+Server：http://localhost/phpmyadmin，并且不要选择任何数据库单击SQL从顶部菜单和pest下面的代码运行SQL查询/查询服务器SET+GLOBAL+sql_mode=(SELECT+REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));+Sorce链路|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|551897|entityMap|0|LINK|mutability|MUTABLE|url|http://localhost/phpmyadmin|1|https://www.findinall.com/blog/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column/^0|2Q|21|Q|R|0|4S|7|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@$9|S|A|T|1|U]|$9|V|A|W|1|X]]|E|$]]|$1|F|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]|N|$5|I|J|K|E|$L|O]]]]

Login to phpMyAdmin
Navigate to : Server: http://localhost/phpmyadmin and do not select any database
Click on SQL from the top menu and pest below code Run SQL query/queries on server
<code>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));</code>
<a href="https://www.findinall.com/blog/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column/" rel="nofollow noreferrer">Sorce link</a>

blocks|key|554911|text|有两种解决办法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|554912|(+a)使用|554913|设置全局sql_mode=(选择替换(@@sql_mode，‘@@sql_mode_FULL_GROUP’，‘’)；|blockquote|554914|b)通过使用ONLY_FULL_GROUP_BY+()引用非聚合列，可以在不禁用ANY_VALUE的情况下实现同样的效果。|554915|ANY_VALUE(价值)|554916|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|P|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|Q|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|L|$]]

Two solutions:
a) Use
<blockquote>
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
</blockquote>
b) You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.
<blockquote>
ANY_VALUE(value)
</blockquote>

blocks|key|1581935|text|就我而言，这是我的旧sql_mode|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1581936|sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"|code-block|syntax|javascript|1581937|您可以将sql_mode与|1581938|show+variables+like+"sql_mode";|1581939|然后我把它设为|1581940|SET+sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';|1581941|entityMap^0|A|8|0|0|4|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

In my case, This was my old sql_mode
<pre><code>sql_mode=&quot;ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&quot;
</code></pre>
You can check sql_mode with
<pre><code>show variables like &quot;sql_mode&quot;;
</code></pre>
then i SET it as this
<pre><code>SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
</code></pre>

blocks|key|551936|text|步骤1.检查sql模式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|551937|SELECT+@@GLOBAL.sql_mode;|code-block|syntax|javascript|551938|步骤2.在/etc/mysql/conf.d.d/目录下创建一个新的配置文件：|551939|sudo+nano+/etc/mysql/conf.d/disable_strict_mode.cnf|551940|在编辑器上输入以下文本：|551941|[mysqld]

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|551942|步骤3.重新启动MySQL：|551943|sudo+service+mysql+restart|551944|步骤4.确认更改：|551945|SELECT+@@GLOBAL.sql_mode;|551946|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Step 1. Check sql mode:
<pre><code>SELECT @@GLOBAL.sql_mode;
</code></pre>
Step 2. Create a new configuration file under the /etc/mysql/conf.d/ directory:
<pre><code>sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf
</code></pre>
Enter the text below on the editor:
<pre><code>[mysqld]

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
</code></pre>
Step 3. Restart MySQL:
<pre><code>sudo service mysql restart
</code></pre>
Step 4. Confirm the change:
<pre><code>SELECT @@GLOBAL.sql_mode;
</code></pre>

blocks|key|1582049|text|如果不想禁用ONLY_FULL_GROUP_BY模式，则只需指定要在GROUP子句中选择的所有列。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1582050|不要使用通配符(*)，并确保您的列与您的需要相关|1582051|entityMap^0|6|I|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

if you don't want to disable <code>ONLY_FULL_GROUP_BY</code> mode you just have to specify every columns you want to select in GROUP BY clause too.
Don't use wildcard (*) and make sure your columns are relevant for your needs

AM using MySQL 5.7.13 on my windows PC with WAMP Server

Here my Problem is While executing this query

<pre><code>SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
 `status` = 'Active'
GROUP BY `proof_type`
</code></pre>

Am getting always error like this 

<blockquote>
 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</blockquote>

Can you please tell me the best solution...

I need Result like

<pre><code>+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name | is_private | status | createdon | updatedon |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| 1 | 1 | 78 | 1 | 21.1212 | 21.5454 | 1 | 1 | id_Card.docx | 0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
</code></pre>

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的windows PC和WAMP服务器正在使用MySQL 5.7.13这里我的问题是在执行这个查询时SELECT *FROM `tbl_customer_pod_uploads`WHERE `load_id` = '78' AND      `status` = 'Active'GROUP BY `proof_typ...

问SELECT list不在GROUP BY子句中，包含非聚合列.与sql_mode=only_full_group_by不兼容
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SELECT list不在GROUP BY子句中，包含非聚合列.与sql_mode=only_full_group_by不兼容EN