mysql 8.0 近期使用问题汇总

公司服务器近期将mysql全部从之前的5.7 升级到了新的8.0版本，但是遇到了好多的坑，近期吭哧吭哧的改了两个礼拜的存储过程了，全部都是血泪啊。

下面说说几个大坑

1. mysql 8.0 使用其他客户端登陆的权限问题，登陆时一直提示

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: ÕÒ²»µ½Ö¸¶¨µÄÄ£¿é¡£

mysql 8.0 默认使用 caching_sha2_password 身份验证机制 —— 从原来的 mysql_native_password 更改为 caching_sha2_password。 
从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法，但新用户会默认使用新的 caching_sha2_password 。

客户端不支持新的加密方式。

方法之一，修改用户的密码和加密方式

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

 

mysql8.*的新特性 caching_sha2_password 密码加密方式

以前版本的mysql密码加密使用的是 mysql_native_password
新添加的用户密码默认使用的 caching_sha2_password
如果在以前mysql基础上升级的 就得用户使用的密码加密使用的是 mysql_native_password
如果使用以前的密码加密方式，就修改文件 /etc/my.cnf

[mysqld]
default_authentication_plugin=mysql_native_password

 

2. 老版本的存储过程在运行时会提示：

Access denied for user 'root'@'IP地址' 

1、mysql -u root -p 登陆进MYSQL；

2、执行以下命令：

GRANT ALL PRIVILEGES ON *.* TO 'your name'@'%' IDENTIFIED BY 'your password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3、exit;

3. 在格式化日期插入数据时经常提示错误 “-”，在之前5.7版本如下的规范格式是没问题的， 但是8.0不行。

 STR_TO_DATE(b.mdleqp_prop,'%Y.%m')+1 as md_date

最后的解决办法是如下 

DATE_ADD(STR_TO_DATE((CONCAT(IF(b.mdleqp_prop="-",NULL,b.mdleqp_prop),'.01')),'%Y.%m.%d'),INTERVAL 0 DAY) as md_date

 

4. 在update A表 join 子查询B表时，5.7也是没问题的， 速度非常快，但是到了8.0版本上，速度简直如蜗牛一般，A表大概是180万数据，B表子查询出来的结果是5万的结果，两个互相匹配，一个小时还没有更新完成，

用explain  看了下  update 的时候type 是ALL， KEY是空，

最后的解决办法是把子查询做成了临时表，并加上了索引，之后在update这个临时表 速度瞬间快多了。