资讯专栏INFORMATION COLUMN

Mac 的mysql5.7没有配置文件,如何解决only_full_group_by 问题

Flands / 616人阅读

摘要:属性是在数据库,表中,点进去,可以看到默认属性是解决这个问题,就需要修改这个字段的属性。方法二修改配置的文件在安装这个版本的的时候,我没有发现的配置文件。

数据库版本是5.7.19,在写语句的时候,只要涉及ORDER BY,就会报错,

</>复制代码

  1. ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column "postscan.verifyDelayLog.auditor" which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个时候,百度发现,是因为这个版本的mysql 数据库默认开启了 sql_mode 字段的only_full_group_by 属性。这个属性是在你写语句时,当你 ORDER BY 的字段不在select 的字段当中,都会报错。

sql_mode 属性是在mysql数据库,event表中,

点进去,可以看到默认属性是

</>复制代码

  1. ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

解决这个问题,就需要修改这个字段的属性。

方法一
在navicat 里面直接针对event表进行修改

</>复制代码

  1. 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 ";

这样就能去掉only_full_group_by 字段。但弊端是这个方法是针对session的,也就是说每次连接mysql 前,都需要set一次。

方法二
修改配置mysql的文件
mac在安装这个版本的mysql的时候,我没有发现mysql 的配置文件。不管是
/etc 下还是 mysql 的support-files 下都没有

这个时候,可以自己在etc目录下创建一个

关闭mysql

</>复制代码

  1. sudo vim /etc/my.cnf

然后往文件里写这个配置就ok。

</>复制代码

  1. # Example MySQL config file for medium systems.
  2. #
  3. # This is for a system with little memory (32M - 64M) where MySQL plays
  4. # an important part, or systems up to 128M where MySQL is used together with
  5. # other programs (such as a web server)
  6. #
  7. # MySQL programs look for option files in a set of
  8. # locations which depend on the deployment platform.
  9. # You can copy this option file to one of those
  10. # locations. For information about these locations, see:
  11. # http://dev.mysql.com/doc/mysql/en/option-files.html
  12. #
  13. # In this file, you can use all long options that a program supports.
  14. # If you want to know which options a program supports, run the program
  15. # with the "--help" option.
  16. # The following options will be passed to all MySQL clients
  17. [client]
  18. default-character-set=utf8
  19. #password = your_password
  20. port = 3306
  21. socket = /tmp/mysql.sock
  22. # Here follows entries for some specific programs
  23. # The MySQL server
  24. [mysqld]
  25. character-set-server=utf8
  26. init_connect="SET NAMES utf8
  27. port = 3306
  28. socket = /tmp/mysql.sock
  29. skip-external-locking
  30. key_buffer_size = 16M
  31. max_allowed_packet = 1M
  32. table_open_cache = 64
  33. sort_buffer_size = 512K
  34. net_buffer_length = 8K
  35. read_buffer_size = 256K
  36. read_rnd_buffer_size = 512K
  37. myisam_sort_buffer_size = 8M
  38. character-set-server=utf8
  39. sql_mode="NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES"
  40. init_connect="SET NAMES utf8"
  41. # Don"t listen on a TCP/IP port at all. This can be a security enhancement,
  42. # if all processes that need to connect to mysqld run on the same host.
  43. # All interaction with mysqld must be made via Unix sockets or named pipes.
  44. # Note that using this option without enabling named pipes on Windows
  45. # (via the "enable-named-pipe" option) will render mysqld useless!
  46. #
  47. #skip-networking
  48. # Replication Master Server (default)
  49. # binary logging is required for replication
  50. log-bin=mysql-bin
  51. # binary logging format - mixed recommended
  52. binlog_format=mixed
  53. # required unique id between 1 and 2^32 - 1
  54. # defaults to 1 if master-host is not set
  55. # but will not function as a master if omitted
  56. server-id = 1
  57. # Replication Slave (comment out master section to use this)
  58. #
  59. # To configure this host as a replication slave, you can choose between
  60. # two methods :
  61. #
  62. # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
  63. # the syntax is:
  64. #
  65. # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
  66. # MASTER_USER=, MASTER_PASSWORD= ;
  67. #
  68. # where you replace , , by quoted strings and
  69. # by the master"s port number (3306 by default).
  70. #
  71. # Example:
  72. #
  73. # CHANGE MASTER TO MASTER_HOST="125.564.12.1", MASTER_PORT=3306,
  74. # MASTER_USER="joe", MASTER_PASSWORD="secret";
  75. #
  76. # OR
  77. #
  78. # 2) Set the variables below. However, in case you choose this method, then
  79. # start replication for the first time (even unsuccessfully, for example
  80. # if you mistyped the password in master-password and the slave fails to
  81. # connect), the slave will create a master.info file, and any later
  82. # change in this file to the variables" values below will be ignored and
  83. # overridden by the content of the master.info file, unless you shutdown
  84. # the slave server, delete master.info and restart the slaver server.
  85. # For that reason, you may want to leave the lines below untouched
  86. # (commented) and instead use CHANGE MASTER TO (see above)
  87. #
  88. # required unique id between 2 and 2^32 - 1
  89. # (and different from the master)
  90. # defaults to 2 if master-host is set
  91. # but will not function as a slave if omitted
  92. #server-id = 2
  93. #
  94. # The replication master for this slave - required
  95. #master-host =
  96. #
  97. # The username the slave will use for authentication when connecting
  98. # to the master - required
  99. #master-user =
  100. #
  101. # The password the slave will authenticate with when connecting to
  102. # the master - required
  103. #master-password =
  104. #
  105. # The port the master is listening on.
  106. # optional - defaults to 3306
  107. #master-port =
  108. #
  109. # binary logging - not required for slaves, but recommended
  110. #log-bin=mysql-bin
  111. # Uncomment the following if you are using InnoDB tables
  112. #innodb_data_home_dir = /usr/local/mysql/data
  113. #innodb_data_file_path = ibdata1:10M:autoextend
  114. #innodb_log_group_home_dir = /usr/local/mysql/data
  115. # You can set .._buffer_pool_size up to 50 - 80 %
  116. # of RAM but beware of setting memory usage too high
  117. #innodb_buffer_pool_size = 16M
  118. #innodb_additional_mem_pool_size = 2M
  119. # Set .._log_file_size to 25 % of buffer pool size
  120. #innodb_log_file_size = 5M
  121. #innodb_log_buffer_size = 8M
  122. #innodb_flush_log_at_trx_commit = 1
  123. #innodb_lock_wait_timeout = 50
  124. [mysqldump]
  125. quick
  126. max_allowed_packet = 16M
  127. [mysql]
  128. no-auto-rehash
  129. # Remove the next comment character if you are not familiar with SQL
  130. #safe-updates
  131. default-character-set=utf8
  132. [myisamchk]
  133. key_buffer_size = 20M
  134. sort_buffer_size = 20M
  135. read_buffer = 2M
  136. write_buffer = 2M
  137. [mysqlhotcopy]
  138. interactive-timeout

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/67960.html

相关文章

发表评论

0条评论

Flands

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<