mysql遇到的问题

20210829

1. 在 IDEA 中 Spring Boot 配置 MySQL 信息链接docker中的 MySQL，报错如下：

   The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
   	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_291]
   	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_291]
   	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_291]
   	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_291]
   	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:361) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.negotiateSSLConnection(NativeAuthenticationProvider.java:777) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:486) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:202) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1449) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.NativeSession.connect(NativeSession.java:165) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:955) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	... 38 common frames omitted
   Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
   	at sun.security.ssl.HandshakeContext.<init>(HandshakeContext.java:171) ~[na:1.8.0_291]
   	at sun.security.ssl.ClientHandshakeContext.<init>(ClientHandshakeContext.java:101) ~[na:1.8.0_291]
   	at sun.security.ssl.TransportContext.kickstart(TransportContext.java:238) ~[na:1.8.0_291]
   	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:394) ~[na:1.8.0_291]
   	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:373) ~[na:1.8.0_291]
   	at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:315) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:188) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:99) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:352) ~[mysql-connector-java-8.0.15.jar:8.0.15]
   	... 45 common frames omitted

   解决：

在 url 中添加 &useSSL=false 解决。
例如：

spring.datasource.url=jdbc:mysql://localhost:33060/mall?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
--这里 33060 是本机映射 docker 中的 3306 端口

Java 中使用MySql 8.0 注意事项

1. maven 依赖中，com.mysql.jdbc.Driver 更换为 com.mysql.cj.jdbc.Driver;
2. MySQL 8.0 以上版本不需要建立 SSL 连接的，需要显示关闭；
   • 使用参数 useSSL=false;
3. allowPublicKeyRetrieval=true 允许客户端从服务器获取公钥；
4. 需要设置 CST，即时区

样例如下：

Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai","root","password");

关于 left join 后结果集顺序问题

左连接后，mysql 对结果集重新进行了排序，并不是按照左表的顺序展示，与预想的不一样，如何 left join 后，使结果集的顺序按照左表的顺序展示？

解决方法：

给左表添加自增 ID，然后 left join 后排序。