mysql默认的配置sql_mode包含ONLY_FULL_GROUP_BY,我们要去掉,网上找了好多帖子,大多数copy来copy去,说的乱七八糟,很无奈,最后折腾了半天才搞定,主要是注意2点
1、先明白你的mysql版本,mysql8是没有 NO_AUTO_CREATE_USER,所有Sql_mode中不能包含这一项
2、因为我是用docker-compose,最好不要修改原来镜像文件
一、增加用户配置文件
vi custom.cnf
[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
二、增加数据库目录
mkdir db
三、编辑compose文件
vi docker-compose.yml
version: '3'
services:
mysql:
container_name: "mysql"
network_mode: "host"
environment:
MYSQL_ROOT_PASSWORD: "setYourPass"
image: "mysql:latest"
restart: always
volumes:
- "./db:/var/lib/mysql"
- "./custom.cnf:/etc/mysql/conf.d/custom.cnf"
ports:
- "3306:33060"
四、启动mysql
docker-compose up -d
五、查看日志
docker logs mysql -f
就能看到mysql启动的输出了,如下:
2020-05-19 05:39:16+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
2020-05-19 05:39:17+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-05-19 05:39:17+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
2020-05-19T05:39:17.476672Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-05-19T05:39:17.476830Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 1
2020-05-19T05:39:17.999178Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-05-19T05:39:18.005713Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-05-19T05:39:18.029171Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-05-19T05:39:18.182854Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
六、验证sql_mode是否生效
直接查询分析器执行下面任一命令,随意:
show variables like '%sql_mode';
show variables like '%sql_mode';
@@GLOBAL.sql_mode
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
可以看到输出的结果中已经没有only_full_group_by
网友评论