IDEA鏈接MySQL資料庫遇到的部分問題及解決方案

合理懷疑MySQL是不是克我，上次配環境配到崩潰就是因為MySQL安裝失敗，這次是因為鏈接不上IDEA

閑話少敘，直接看代碼、報錯信息以及解決方式：

代碼：

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class bookstore {
    public static void main(String[] args) {
        //載入驅動
        try {
            //載入
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("載入成功");
            //鏈接資料庫,獲得鏈接對象
            String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/db3";
            String username = "root";
            String password = "123456";

            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            System.out.println("鏈接成功");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("失敗");
            throw new RuntimeException(e);
        }
    }


}

問題一：

報錯信息：Tue Dec 12 01:47:57 CST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

原因：JDK版本問題

解決方式：

建議使用

String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/db3?useSSL=false"

（也就是在末尾加個?useSSL=false）顯式地設置 useSSL 為 false，以禁用SSL連接，不然會拋出異常。

問題二：

報錯信息：Unable to load authentication plugin ‘caching_sha2_password‘.

原因：MySQL8之前的版本中加密規則是mysql_native_password，MySQL8之後,加密規則是caching_sha2_password。

解決方式：進入MySQL 8.0 Command Line Client，逐行輸入如下代碼：(123456換成你相應的密碼)。

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
alter user 'root'@'localhost' identified by '123456';

修改後的完整代碼：

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class bookstore {
    public static void main(String[] args) {
        //載入驅動
        try {
            //載入
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("載入成功");
            //鏈接資料庫,獲得鏈接對象
            String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/db3?useSSL=false";
            String username = "root";
            String password = "123456";

            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            System.out.println("鏈接成功");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("失敗");
            throw new RuntimeException(e);
        }
    }


}