java jdbc 連結mysql插入資料中文顯示亂碼

標籤：error usrsbinmysqld   jdbc 連結mysql插入資料中文亂碼   default-character-se   

1.讀取資料正常，插入中文亂碼查看連結處制定的字元集，這裡使用utf-8

String url = "jdbc:mysql://127.0.0.1:3306/website?useUnicode=true&characterEncoding=utf-8 ";

完整代碼如下：

public mysqlConnect() {String driver = "com.mysql.jdbc.Driver";// URL指向要訪問的資料庫名websiteString url = "jdbc:mysql://127.0.0.1:3306/website?useUnicode=true&characterEncoding=utf-8 ";// MySQL配置時的使用者名稱String user = "root";// Java串連MySQL配置時的密碼String password = "sanyue";try {// 載入驅動程式Class.forName(driver);conn = (Connection) DriverManager.getConnection(url,user, password);if (!conn.isClosed())System.out.println("Succeeded connecting to the Database!");// statement用來執行SQL語句statement = (Statement) conn.createStatement();} catch (Exception e) {e.printStackTrace();statement = null;}}

2.查看mysql和資料庫本身預設是否採用的utf8編碼

此處使用  set character_set_database="utf8";
修改後在重啟mysql後會不起作用，所以請找到您的 mysql的設定檔  my.ini或my.cnf  在[mysqld]的中添加character_set_server=utf8，注意有些是default-character-set=utf8，重新啟動伺服器時如果出錯可以查看錯誤記錄檔，最後會給出我在配置的過程中遇到的幾個錯誤

mysql> show variables like "character%";+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | utf8                       || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | utf8                       || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

3.看頁面本身是否是utf8編碼。

4.看頁面使用的編碼是否是utf8編碼

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%>

基本上這幾個步驟如果都正常就不會出錯，但是存在一些情況。

例如，本次配置時我是利用的set character_set_database="utf8";在資料庫中直接修改的字元編碼，但是一直沒有發現重啟之後或者退出再次連結之後會回複為原來的latin1，所以一直以為資料庫 本身編碼沒有問題。

另外，java 連結資料庫的連結

String url = "jdbc:mysql://127.0.0.1:3306/website?useUnicode=true&characterEncoding=utf-8 ";

不要寫錯，中間的&實際為  &  注意（此處由於編譯器變為了&）

String url = "jdbc:mysql://127.0.0.1:3306/website?useUnicode=true&characterEncoding=utf-8 ";

以下是遇到的一些錯誤：

mysql 錯誤記錄檔

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

解決辦法：

[email protected]:/var/lib/mysql# ps -ef | grep mysql
mysql    24477     1  0 16:35 ?        00:00:00 /usr/sbin/mysqld
root     24533  1908  0 16:35 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql    25382 24533  0 16:37 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
root     25476  1908  0 16:37 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql    25839 25476  0 16:37 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
root     26204 21859  0 16:38 pts/16   00:00:00 grep --color=auto mysql
[email protected]:/var/lib/mysql# kill -9 24477
[email protected]:/var/lib/mysql# kill -9 24533

....

錯誤記錄檔

[email protected]:/var/lib/mysql# tail ../../log/mysql/error.log
150616 16:40:19 InnoDB: Using Linux native AIO
150616 16:40:19 InnoDB: Initializing buffer pool, size = 128.0M
150616 16:40:19 InnoDB: Completed initialization of buffer pool
150616 16:40:19 InnoDB: highest supported file format is Barracuda.
150616 16:40:20  InnoDB: Waiting for the background threads to start
150616 16:40:21 InnoDB: 5.5.43 started; log sequence number 6583039
150616 16:40:21 [ERROR] /usr/sbin/mysqld: unknown variable ‘default-character-set=utf8‘
150616 16:40:21 [ERROR] Aborting

解決方案：

開啟mysql設定檔（這裡在 /etc/mysql/my.cnf）

找到  [mysqld]中

用character_set_server=utf8來取代default-character-set=utf8  

java jdbc 連結mysql插入資料中文顯示亂碼