Mysql 字符集

背景：

最近在一个项目，在往数据库写入数据时报错报错如下：

Cause: java.sql.SQLException: Incorrect string value: ‘\xF0\xA3\xBD\x8A’ for column ‘PERSON_NAME’ at row 1604

java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\xA3\xBD\x8A' for column 'PERSON_NAME' at row 1604
### The error may exist in URL [jar:file:/do1cloud/znmh/dqsf-share-datasync/dqsf-share-datasync.jar!/BOOT-INF/lib/dqsf-share-datasync-core-2.1.2-SNAPSHOT.jar!/mapper/dqsfdatasync/TbQyUserInfoDao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO tb_qy_user_info(`ID`, `USER_ID`, `ORG_ID`, `USER_NAME`, `person_name`, `PINYIN`, `mark`,         `address`, `MOBILE`, `SHOR_MOBILE`, `EMAIL`, `birthday`, `SEX`, `company`, `DEPT_ID`, `POSITION`, `UPDATE_TIME`,         `USER_STATUS`, `head_pic`, `weixin_num`, `qq_num`, `create_person`, `create_time`, `is_concerned`, `follow_time`,         `cancel_time`, `wx_user_id`, `corp_id`, `lunar_calendar`, `nick_name`, `phone`, `remind_type`, `entry_time`,         `leave_time`, `leave_remark`, `is_leader`, `IS_TOP`, `CERTIFICATE_TYPE`, `CERTIFICATE_CONTENT`, `identity`, `attribute`, `has_child`, `user_no` ,`sync_wx`) VALUES                        (

原因是由于插入数据的字符集与表结构的默认字符集不匹配导致的这个问题。

简介：

MySQL提供了不同级别的设置，包括server级、database级、table级、column级，可以提供非常精准的设置。
什么是字符集、字符序？简单的来说：

• 字符集（character set）：定义了字符以及字符的编码。
• 字符序（collation）：定义了字符的比较规则。

1、server级（mysql设置）

用途：当你创建数据库，且没有指定字符集、字符序时，server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

1.1、查看server字符集、字符序

分别对应character_set_server、collation_server两个系统变量。

mysql> SHOW VARIABLES LIKE "character_set_server";
mysql> SHOW VARIABLES LIKE "collation_server";

1.2、修改字符集

1.2.1、配置文件指定

在mysql配置文件中设定，修改后需要重启

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

1.2.2、运行时修改

例子：运行时修改（重启后会失效，如果想要重启后保持不变，需要写进配置文件里）

mysql> SET character_set_server = utf8 ;

2、database级（数据库）

用途：指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库，可以分别指定不同的字符集/字符序。

2.1、查看数据库的字符集/字符序

查看数据库的字符集/字符序

例子一：查看test_schema的字符集、排序规则。（需要切换默认数据库）

mysql> use test_schema;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8   | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)

2.2、设置数据的字符集/字符序

可以在创建、修改数据库的时候，通过CHARACTER SET、COLLATE指定数据库的字符集、排序规则。

创建数据库：

CREATE DATABASE db_name
 [[DEFAULT] CHARACTER SET charset_name]
 [[DEFAULT] COLLATE collation_name]

修改数据库：

ALTER DATABASE db_name
 [[DEFAULT] CHARACTER SET charset_name]
 [[DEFAULT] COLLATE collation_name]

3、table级（表设置）

若明确了建表的字符集和字符序则使用自定义的字符集，若建表时没有明确则使用数据库的字符集，若数据库未明确则使用server级字符集以此类推。

3.1、查看表字符集

通过SHOW CREATE TABLE确认。

mysql> SHOW CREATE TABLE test_table;
+------------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table             |
+------------+----------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
 `id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.2、创建表、修改表的语法如下，可通过CHARACTER SET、COLLATE设置字符集、字符序。

CREATE TABLE tbl_name (column_list)
 [[DEFAULT] CHARACTER SET charset_name]
 [COLLATE collation_name]]

ALTER TABLE tbl_name
 [[DEFAULT] CHARACTER SET charset_name]
 [COLLATE collation_name]

4、column级（列设置）

类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，语法如下：

4.1、查看表column字符集

show full columns from {tablename};

4.2、修改表column字符集

mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;