项目里一直用的是mysql5.7，这次立了个新项目，我果断换上mysql8.0，心想肯定新版的性能更好更强大啊，其实无形间也踩了不少坑，mysql8.0和mysql5.0的小改动（升级）引发的兼容性问题。。
一、mysql8.0搭建
首先是搭建mysql8.0环境，这里当然使用时下最方便的docker-compose方式了
my.cnf如下
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL #gtid: server_id = 1 #服务器id gtid_mode = on #开启gtid模式 enforce_gtid_consistency = on #强制gtid一致性，开启后对于特定create table不被支持 #binlog log_bin = mysql-binlog log_slave_updates = on binlog_format = row #强烈建议，其他格式可能造成数据不一致 #relay log skip_slave_start = 1 [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION init_connect = 'SET collation_connection=utf8mb4_general_ci' # Custom config should go here !includedir /etc/mysql/conf.d/
这里sql_mode必须要设置
原因是什么呢？
mysql 5.7+中 默认启用了 ONLY_FULL_GROUP_BY
这个表示什么呢，从字面上看，大概就是一种严谨的SQL模式，类似于Oracel那些Group by语句，就是你查询那些字段，group by的时候也要写上哪些字段。知道原因了，那把这个模式去掉就可以了。
如果是mysql8.0，则需要去掉NO_AUTO_CREATE_USER，因为在mysql8.0中已经没有这个模式了。
报错方式： Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY
参考文档：https://www.jianshu.com/p/9534a6878f65
#查看mysql是否启用ONLY_FULL_GROUP_BY select @@global.sql_mode
docker-compose.yml文件如下
# 使用说明 V3.2.0 # docker-compose up version: '3.1' services: mysql8-01: container_name: mysql8-01 hostname: mysql8-01 image: mysql:latest restart: always ports: - 13306:3306 environment: TZ: Asia/Shanghai MYSQL_ROOT_HOST: '%' MYSQL_ROOT_PASSWORD: root密码 MYSQL_USER: 普通账号 MYSQL_PASSWORD: 普通账号密码 command: --default-authentication-plugin=mysql_native_password --max_connections=1000 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci --default-time-zone='+8:00' --explicit_defaults_for_timestamp=true --lower_case_table_names=1 --expire-logs-days=7 # privileged: true logging: driver: "json-file" options: max-size: "100m" volumes: - ./data:/var/lib/mysql - ./logs:/var/log/mysql - ./conf/my.cnf:/etc/mysql/my.cnf - ./init:/docker-entrypoint-initdb.d
参数详解：
参数1：
--default-authentication-plugin=mysql_native_password
这就是mysql8.0和mysql5.0的兼容问题：mysql5.0 加密方式默认是mysql_native_password，mysql8.0默认是 caching_sha2_password
如果想让mysql8.0用的数据可以兼容5.0，只能妥协，用mysql_native_password的方式
报错方式：2059
参考文档：https://blog.csdn.net/weixin_60764989/article/details/131020609
参数2：
--collation-server=utf8mb4_general_ci
编码也是mysql8.0升级的地方之一，mysql8.0默认用 utf8mb4_0900_ai_ci 编码
如果想让mysql8.0用的数据可以兼容5.0，只能妥协，用 utf8mb4_general_ci
二、查看并设置数据库编码
由于mysql8.0默认的编码为 utf8mb4_0900_ai_ci，而我们如果想兼容mysql5.0就得确保用的都是 utf8mb4_general_ci，如果编码没设置对，会造成里面表编码不一致导致的报错
#查看当前编码
mysql> SHOW VARIABLES WHERE variable_name LIKE '%character%' OR variable_name LIKE '%collation%' ;
+-------------------------------+--------------------------------------+
| Variable_name | Value |
+-------------------------------+--------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /opt/idc/mysql8.0.23/share/charsets/ |
| collation_connection | utf8mb4_bin |
| collation_database | utf8mb4_bin |
| collation_server | utf8mb4_bin |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------------------------+
#从上面client, connection,database，results层层环节扣着，任何一个环节的字符集不兼容都会出现乱码问题。
#查看全局编码
show global variables like '%coll%'
show global variables like '%cha%'
#设置全局编码
#设置自定义列变量编码
set global default_collation_for_utf8mb4 = 'utf8mb4_general_ci'
set global collation_connection = utf8mb4_general_ci
后来发现用上面语句，重启数据库后会还原，得用以下语句设置
SET PERSIST default_collation_for_utf8mb4='utf8mb4_general_ci';
参考文档：https://blog.csdn.net/weixin_42835568/article/details/131383651
设置成如图这样即可
关于 default_collation_for_utf8mb4参数的用途：
| System Variable | default_collation_for_utf8mb4 |
|---|---|
| Scope | Global, Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Enumeration |
| Valid Values |
|
mysql 8.0字符集uft8mb4默认的collate 是utf8mb4_0900_ai_ci ，
这个值是参数default_collation_for_utf8mb4控制，如果创建表时只给了字符集utf8mb4,没指定collate话，就会是默认的 utf8mb4_0900_ai_ci
也就是这个参数控制新建表的默认编码，如果未设置，新建表又没指定编码，默认赋值 utf8mb4_0900_ai_ci
这个时候如果表里的其他表编码为utf8mb4_general_ci，当进行查询视图等操作时会报错
报错为：java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='；
解决方法
#给没有设置编码的表重新设置一下： mysql> alter table table_name default character set utf8mb4 collate=utf8mb4_general_ci; #这样设置只针对表的，但是表中字段未修改： mysql> ALTER TABLE table_name convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 使用show table status语句查看某个库下面全部表的字符集编码
show table status from 库名;
# 但只想获取指定表的编码信息，就可利用like进行限制：
show table status from 库名 like "表名";
参考文档：
https://www.cnblogs.com/minutes/p/10808026.html
https://blog.csdn.net/majc111111/article/details/129855511
所以提前设置好全局变量就能避免这个坑
字符集知识扩展：
数据库服务端的字符集具体要看存储什么字符
以上这些参数如何起作用：
1.库、表、列字符集的由来
①建库时，若未明确指定字符集，则采用character_set_server指定的字符集。
②建表时，若未明确指定字符集，则采用当前库所采用的字符集。
③新增时，修改表字段时，若未明确指定字符集，则采用当前表所采用的字符集。
2.更新、查询涉及到得字符集变量
更新流程字符集转换过程：character_set_client-->character_set_connection-->表字符集。
查询流程字符集转换过程：表字符集-->character_set_result
3.character_set_database
当前默认数据库的字符集，比如执行use xxx后，当前数据库变为xxx，若xxx的字符集为utf8，那么此变量值就变为utf8(供系统设置，无需人工设置)。
mysql8.0参数官方参考文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_collation_connection
关于编码问题的参考文档：
https://blog.csdn.net/loongshawn/article/details/120781125
https://www.cnblogs.com/qcloud1001/p/10033364.html
https://www.modb.pro/db/377587
https://blog.51cto.com/lhrbest/2698445
https://www.cnblogs.com/hdwang/p/16739147.html
https://blog.csdn.net/dreamyuzhou/article/details/117483059
https://blog.csdn.net/sinat_36757755/article/details/126322755
https://blog.csdn.net/zdw19861127/article/details/78480926
https://blog.csdn.net/feiying0canglang/article/details/124953729
https://www.bilibili.com/read/cv10964527/
三、mysql数据迁移，初始化
1.转储下载并压缩数据库sql文件
#转储并下载数据库文件
cd /data/bak/
mysqldump 原始库名 -h 原始库ip -P端口（如果是3306可省略） -uroot -p密码 --add-drop-table >/data/bak/123.sql
#压缩数据库文件
tar -zcvf 123.tar.gz *.sql
2.把压缩文件上传新数据库docker容器 init目录，并解压缩
3.在新数据库上新建数据库
按照原数据库编码格式新建一个数据库
#进入mysql容器
docker exec -it mysql8-01 /bin/bash
#登陆数据库
mysql -uroot -p密码
#新建数据库并设置编码
CREATE DATABASE 数据库名 CHARACTER SET utf8mb4 collate utf8mb4_general_ci;
也可以在navicat等工具上新建
4.导入数据库
方法一：
（1）进入容器，登陆数据库
#进入mysql容器 docker exec -it mysql8-01 /bin/bash #登陆数据库 mysql -uroot -P密码
（2）选择数据库
mysql>use abc;
（3）导入数据（注意sql文件的路径）
mysql>source /docker-entrypoint-initdb.d/abc/abc.sql;
方法二：
mysql -u用户名 -p 参数(可选) 数据库名 < 数据库名.sql
mysql -uroot -p密码 数据库名 < abc.sql
参考文档：
https://www.jb51.net/article/95964.htm?timer=tc
https://www.cnblogs.com/Xinenhui/p/16357091.html
https://www.cnblogs.com/zhuhaofeng/p/15713640.html
https://blog.csdn.net/Anastasia_li/article/details/125029916
四、数据库账号权限设置
1.限制账号登陆ip
#1.登陆mysql
mysql -u root -p #2. 切换数据库到mysql
show databases;
use mysql;
#3.查询现有账号的host权限
select host,user from user where user='root'; #4.修改现有账号的host权限，%代表无限制，可以设置成IP地址，或者IP+%的方式：192.168.1.% ，user可以设置已有的任何账号，localhost表示只能本机登陆，不能远程登陆
update user set host = '%' where user='root' and host='localhost'; #4.确认登陆IP已修改
select host, user from user where user='root';
#5.使修改生效
flush privileges;
2.给普通账号赋权
这里要说一个概念性问题
（1）在mysql中 'root'@'%'被认为是一个完整的账号
（2）在高版本mysql（mysql8）中已不支持通过grand命令新建账号
这样就会导致一个问题，就是mysql8中想通过grand 修改账号的登陆IP是行不通的，mysql会报错
报错为：MySQL8.0 ERROR 1410 (42000): You are not allowed to create a user with GRANT
实质问题原因是：授权语法不严谨导致所授权的用户及其所在主机名，要与实际存在的用户及其主机名一致。
如果已建用户是:testuser@localhost,你要授权给testuser@%,那肯定不行，必须一样才行。
所以主机名不一致的需要先修改主机名或者修改授权SQL语句。
也就是说此时在mysql的逻辑里，修改账号的登陆IP等于新建账号，而grand语法已不支持新建账号
这个时候要么就按第一步修改登陆IP再赋权，要么就修改赋权语句给现有账号和IP
参考文档：
https://blog.csdn.net/gmaaa123/article/details/127690504
https://blog.csdn.net/weixin_44548432/article/details/116104696
以下是赋权操作
#1.登陆mysql，并切换到mysql数据库 mysql -u root -p
use mysql;
#2.创建账号（有普通账号这步可以省略） create user 'aaa'@'192.168.1.1' identified by 'password'; #3.给账号赋权 #给账号aaa赋指定库里所有表的所有操作权限,并带有赋权权限 grant all privileges on 数据库名.* to 'aaa'@'192.168.1.1' with grant option; #给账号aaa赋指定库里所有表的增删改查权限 grant select,insert,update,delete on 数据库名.* to 'aaa'@'192.168.1.1'; #给账号root赋予所有库里所有表的所有权限 grant all privileges on *.* to 'root'@'%' with grant option; #4.使修改生效 flush privileges; #5.查看账号权限
show grants for 'aaa'@'192.168.1.1'
创建只读账号示例：
#查看现有用户
SELECT User,Host FROM mysql.user;
#创建只读用户，10.122.201.%是ip段
create user 'rouser'@'10.122.201.%' identified by '123456';
#赋权相关库只读权限
GRANT SELECT ON 库名.* TO 'rouser'@'10.122.201.%';
#刷新缓存，使用户生效
flush privileges;
参考：https://blog.csdn.net/Xixi0864/article/details/132893271
https://www.yzktw.com.cn/post/982778.html
https://www.yzktw.com.cn/post/1010441.html
扩展：
撤销已经赋予给 MySQL 用户权限的权限。
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
```sql
grant all on *.* to dba@localhost;
```
```sql
revoke all on *.* from dba@localhost;
```
更多参考：https://zhuanlan.zhihu.com/p/148525190
参考文档：
https://blog.csdn.net/wngpenghao/article/details/126626960
https://www.cnblogs.com/lemon-flm/p/7597879.html
https://www.cnblogs.com/pengpengzhang/p/10101919.html
另外，mysql5升到mysql8还会遇到的报错为
java.sql.SQLException: Unknown system variable 'query_cache_size'
这个报错是驱动问题，原因是mysql-connector-java的版本过低，需要升级版本到8.0以上
参考文档：
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南