MySQL57查看与修改数据库编码为utf8

查看MySQL编码

show variables like 'char%';

修改 my.cnf 设置 utf8 编码

utf8

[mysqld]
# 解决中文问号乱码问题
# show variables like 'char%';
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server=utf8

[client]
default-character-set=utf8

utf8mb4

[mysqld]
# 解决中文问号乱码问题
# show variables like 'char%';
collation-server = utf8mb4_bin
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

完了重启即可。

效果

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

PS：更完整的显示字符编码信息：

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8mb4
character_set_system	utf8
collation_connection	utf8mb4_bin
collation_database	utf8mb4_bin
collation_server	utf8mb4_bin

单独修改某个数据库的编码规则

参考：

https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-744326173.html

# To check database collation:
use jira;
SELECT @@character_set_database, @@collation_database;

# To check Table collation:
SELECT TABLE_SCHEMA
    , TABLE_NAME
    , TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES;

# To check Column collation:
SELECT TABLE_NAME 
    , COLUMN_NAME 
    , COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS;

show create database jira;
ALTER DATABASE jira CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

mysql> use jira;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

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

mysql> ALTER DATABASE jira CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_bin          |
+--------------------------+----------------------+
1 row in set (0.00 sec)

如果不想修改数据库也可以修改连接串，jira在 jira-home/dbconfig.xml 里面，加上

&ampcharacterEncoding=utf8&connectionCollation=utf8mb4_bin

修改表的排序规则

SELECT CONCAT('ALTER TABLE `',  table_name, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'jira'
AND
(
    C.CHARACTER_SET_NAME != 'utf8mb4'
    OR
    C.COLLATION_NAME != 'utf8mb4_bin'
);

然后执行生成的sql，下面的同理。

修改数据行的排序规则

varchar行

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'jira'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_bin'
);

非varchar行

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'jira'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8mb4'
    OR
    COLLATION_NAME != 'utf8mb4_bin'
);

参考

关于 utf8 的详细讨论可参考

谈谈MySQL中的utf8和utf8mb4