Using the employee example database as an example, the The test environment uses a single statement.
SELECT * FROM employees GROUP BY gender;
It works fine in the test environment, but it has problems online, reporting errors as follows.
MySQL has the following constraints in
When you execute a SELECT statement with GROUP BY and ORDER BY, you need to make sure that the columns you SELECT are in GROUP BY and ORDER BY.
As an analogy, if you have data as follows.
SELECT emp_no,gender FROM employees GROUP BY gender; will only return one record, then the
emp_no field can’t show both 1 and 2, in MySQL’s view, this is not the standard way to use, so there will be the beginning of the error report.
Add dependencies between columns
We can make emp_no correspond to gender one by one, for example, create a joint index between emp_no and gender. Then there will be no problem with the
But obviously there is still a problem here, because this has an impact on the data and the transformation cost is too high.
We can use ANY_VALUE() to tell MySQL that the value of emp_no in the example, any value would be fine.
This solution is better than the first one, but there will still be a transformation cost to transform the query statement. For example, it would have to be changed to
SELECT ANY_VALUE(emp_no),gender FROM employees GROUP BY gender;
This restriction is brought by the
ONLY_FULL_GROUP_BY schema and can be turned off by turning off the
ONLY_ FULL_GROUP_BY mode.
Turn off ONLY_FULL_GROUP_BY globally
Find the MySQL configuration file (
my.cnf) to modify and save. First find the configuration file path with the following command.
Find it, edit and save it, restart MySQL and it will take effect.
Turn off ONLY_FULL_GROUP_BY by sql_mode variable
To see the current sql_mode.
Close it with the following command:
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
Turn off ONLY_FULL_GROUP_BY in Spring Boot/HikariCP
connection-init-sql doesn’t support multiple MySQL statements, and the original
SET SESSION time_zone='+08:00', it’s not easy to set this.
After looking up the MySQL documentation for setting variables, you can set multiple variables in one statement, for example, we can write it like this