java jdbc 链接mysql插入数据中文显示乱码

最新推荐文章于 2024-07-21 18:54:39 发布

三月软件----王耀峰

最新推荐文章于 2024-07-21 18:54:39 发布

阅读量1.5w

点赞数 2

分类专栏： jsp 文章标签： ERROR usrsbinmysqld jdbc 链接mysql插入数据中文乱码 default-character-se

本文链接：https://blog.csdn.net/wyfhist/article/details/46520223

版权

jsp 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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指向要访问的数据库名website
		String 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.

解决办法：

root@wangyaofeng-NJ091AA-AB2-g3721cx:/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
root@wangyaofeng-NJ091AA-AB2-g3721cx:/var/lib/mysql# kill -9 24477
root@wangyaofeng-NJ091AA-AB2-g3721cx:/var/lib/mysql# kill -9 24533

....

错误日志

root@wangyaofeng-NJ091AA-AB2-g3721cx:/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