mysql数据库group_key_mysql 5.7版本 only_full_group_by 1055错误及 sql_mode 解析

之前项目的数据库是5.5版本的，之后升级到了5.6.没有什么问题。但是最近因为实施部环境的时候，直接给用成了mysql5.7版本，结果项目启动直接就出问题了。最终把问题解决之后，在我本地环境也进行了设置，现在把过程记录一下：

最开始报出来的结果是：

错误代码1055。不过看了Expression里面的原因描述，我基本知道怎么回事了。原因是因为mysql中对 group by 用法的规定。严格意义上说，就是group by之后。select 的字段只能是group by的字段。或者需要加聚合函数的。在 oracle 中，一直有这项约定。所以直接就会报错。但是mysql 直到 5.7 版本之后才把这个要求明确限制起来。

很明显，这种问题两种思路：第一修改sql。按照规定的规范重写项目中所有有问题的sql。很明显代价太大。风险很高；第二种那就是把这种限制去掉，让功能继续像5.7之前的样子一样能跑。我果断选择了第二种。因为第一种代价太大。

解决办法：

1. 首先如何查看当前数据库使用的sql_mode：

select @@sql_mode;

2. 修改mysql的配置文件，删掉only_full_group_by这一项

注意：Mac：Mysql默认安装在/usr/local目录下，这个目录可以通过command+shift+G进入：如下图

windows下是的配置文件是my.ini，自己可以找一下。

问题解决完了，然后剩下两个问题：

1. mysql里面的 sql_mode 一共有哪些，并且mode的值各自代表什么含义。在网上查询了一下，结果如下：

MySQL5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES

1、ANSI模式：宽松模式，更改语法和行为，使其更符合标准SQL。对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。对于本文开头中提到的错误，可以先把sql_mode设置为ANSI模式，这样便可以插入数据，而对于除数为0的结果的字段值，数据库将会用NULL值代替。

将当前数据库模式设置为ANSI模式：切换到mysql命令下执行：set @@sql_mode=ANSI;

2、TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误，而不仅仅是警告。用于事物时，会进行事物的回滚。 注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。

将当前数据库模式设置为TRADITIONAL模式：切换到mysql命令下执行：set @@sql_mode=TRADITIONAL;

3、STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。

将当前数据库模式设置为STRICT_TRANS_TABLES模式：切换到mysql命令下执行：set @@sql_mode=STRICT_TRANS_TABLES;

mode值的含义：

ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中

STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制

NO_ZERO_IN_DATE：在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_DATE：在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告

ERROR_FOR_DIVISION_BY_ZERO：在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。

NO_AUTO_CREATE_USER：防止GRANT自动创建新用户，除非还指定了密码。

NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

2. 顺便找到 mysql 的配置文件了，里面的配置项分别代表什么含义呢。也查询了一下，结果如下：

#

# The following options will be read by the MySQL Server. Make sure that

# you have installed the server correctly (see above) so it reads this

# file.

#

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

#Path to installation directory. All paths are usually resolved relative to this.

basedir="E:/Java/Mysql/"

#Path to the database root

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is

# created and no character set is defined

character-set-server=gb2312

# The default storage engine that will be used when create new tables when

default-storage-engine=INNODB

# Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will

# allow. One of these connections will be reserved for a user with

# SUPER privileges to allow the administrator to login even if the

# connection limit has been reached.

max_connections=100

# Query cache is used to cache SELECT results and later return them

# without actual executing the same query once again. Having the query

# cache enabled may result in significant speed improvements, if your

# have a lot of identical queries and rarely changing tables. See the

# "Qcache_lowmem_prunes" status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.

query_cache_size=0

# The number of open tables for all threads. Increasing this value

# increases the number of file descriptors that mysqld requires.

# Therefore you have to make sure to set the amount of open files

# allowed to at least 4096 in the variable "open-files-limit" in

# section [mysqld_safe]

table_cache=256

# Maximum size for internal (in-memory) temporary tables. If a table

# grows larger than this value, it is automatically converted to disk

# based table This limitation is for a single table. There can be many

# of them.

tmp_table_size=35M

# How many threads we should keep in a cache for reuse. When a client

# disconnects, the client's threads are put in the cache if there aren't

# more than thread_cache_size threads from before. This greatly reduces

# the amount of thread creations needed if you have a lot of new

# connections. (Normally this doesn't give a notable performance

# improvement if you have a good thread implementation.)

thread_cache_size=8

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger

# than using the key cache by the amount specified here, then prefer the

# key cache method. This is mainly used to force long character keys in

# large tables to use the slower key cache method to create the index.

myisam_sort_buffer_size=69M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# MyISAM tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

key_buffer_size=55M

# Size of the buffer used for doing full table scans of MyISAM tables.

# Allocated per thread, if a full scan is needed.

read_buffer_size=64K

read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in

# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

sort_buffer_size=256K

以下是参数的简介：

port参数也是表示数据库的端口。

basedir参数表示MySQL的安装路径。

datadir参数表示MySQL数据文件的存储位置，也是数据库表的存放位置。

default-character-set参数表示默认的字符集，这个字符集是服务器端的。

default-storage-engine参数默认的存储引擎。

sql-mode参数表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度。

max_connections参数表示允许同时访问MySQL服务器的最大连接数，其中一个连接是保留的，留给管理员专用的。

query_cache_size参数表示查询时的缓存大小，缓存中可以存储以前通过select语句查询过的信息，再次查询时就可以直接从缓存中拿出信息。

table_cache参数表示所有进程打开表的总数。

tmp_table_size参数表示内存中临时表的总数。

thread_cache_size参数表示保留客户端线程的缓存。

myisam_max_sort_file_size参数表示MySQL重建索引时所允许的最大临时文件的大小。

myisam_sort_buffer_size参数表示重建索引时的缓存大小。

key_buffer_size参数表示关键词的缓存大小。

read_buffer_size参数表示MyISAM表全表扫描的缓存大小。

read_rnd_buffer_size参数表示将排序好的数据存入该缓存中。

sort_buffer_size参数表示用于排序的缓存大小

上面是mysql服务器的一些参数，此外，参数中还有关于InnoDB存储引擎使用的参数，如下

#*** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled

# but you do not plan to use it. This will save memory and disk space

# and speed up some things.

#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata

# information. If InnoDB requires more memory for this purpose it will

# start to allocate it from the OS. As this is fast enough on most

# recent operating systems, you normally do not need to change this

# value. SHOW INNODB STATUS will display the current amount used.

innodb_additional_mem_pool_size=3M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the

# disk at each commit, which offers full ACID behavior. If you are

# willing to compromise this safety, and you are running small

# transactions, you may set this to 0 or 2 to reduce disk I/O to the

# logs. Value 0 means that the log is only written to the log file and

# the log file flushed to disk approximately once per second. Value 2

# means the log is written to the log file at each commit, but the log

# file is only flushed to disk approximately once per second.

innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as

# it is full, InnoDB will have to flush it to disk. As it is flushed

# once per second anyway, it does not make sense to have it very large

# (even with long transactions).

innodb_log_buffer_size=2M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system. Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

innodb_buffer_pool_size=107M

# Size of each log file in a log group. You should set the combined size

# of log files to about 25%-100% of your buffer pool size to avoid

# unneeded buffer pool flush activity on log file overwrite. However,

# note that a larger logfile size will increase the time needed for the

# recovery process.

innodb_log_file_size=54M

# Number of threads allowed inside the InnoDB kernel. The optimal value

# depends highly on the application, hardware as well as the OS

# scheduler properties. A too high value may lead to thread thrashing.

innodb_thread_concurrency=18

innodb_additional_mem_pool_size参数表示附加的内存池，用来存储InnoDB表的内容。

innodb_flush_log_at_trx_commit参数是设置提交日志的时机，若设置为1，InnoDB会在每次提交后将事务日志写到磁盘上。

innodb_log_buffer_size参数表示用来存储日志数据的缓存区的大小。

innodb_buffer_pool_size参数表示缓存的大小，InnoDB使用一个缓冲池类保存索引和原始数据。

innodb_log_file_size参数表示日志文件的大小。

innodb_thread_concurrency参数表示在InnoDB存储引擎允许的线程最大数。