数据库的编码问题

1 问题

几天码代码的时候，发现往表中插入汉字的时候报如下错误

java.lang.RuntimeException: java.sql.SQLException: 
Incorrect string value: '\xE6\x88\x91\xE6\x98\xAF...' for column 'name' at row 1 Query: 
update account set name=?,money=? where id = ? Parameters: [我是A, 1500.0, 1]

报运行时异常，java的SQL异常：不正确的字符串值。
打开数据发现乱码

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | ??A  |   100 |
|  2 | ??B  |   100 |
|  3 | ccc  |   500 |
|  4 | ddd  |     0 |
|  5 | eee  |  1200 |
+----+------+-------+
5 rows in set (0.00 sec)

2 找出问题

查看数据库的编码格式

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

发现数据库test的默认编码格式为utf8，

mysql> show create table account;
+---------+------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                           |
+---------+------------------------------------------------------------------------------------------------+
| account | CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `money` float(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

发现表的编码格式为latin1
网上找了一个命令alter table 表名称 convert to character set utf8;
使用后发现，标的默认编码是改过来了。字段还是有问题。

mysql> alter table `account` convert to character set utf8;
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show create table account;
+---------+-------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                         |
+---------+-------------------------------------------------------------------------------------------------+
| account | CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `money` float(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

字段有问题

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | AAA  |  1500 |
|  2 | ??B  |   100 |
|  3 | ccc  |   500 |
|  4 | ddd  |     0 |
|  5 | eee  |  1200 |
+----+------+-------+
5 rows in set (0.00 sec)

再往表中插入汉字，发现可以了。

mysql> UPDATE account SET `name`='我是A', money=100 WHERE id=1

mysql> select * from account;
+----+-------+-------+
| id | name  | money |
+----+-------+-------+
|  1 | 我是A |   100 |
|  2 | ??B   |   100 |
|  3 | ccc   |   500 |
|  4 | ddd   |     0 |
|  5 | eee   |  1200 |
+----+-------+-------+
5 rows in set (0.00 sec)

可以发现：
修改表的编码格式时就可以了，但是第二个是已经插入进去了，已经是乱码了，无法改变了。