1、查看字符集设置

mysql> show variables like 'collation_%';+----------------------+--------------------+| Variable_name        | Value              |+----------------------+--------------------+| collation_connection | utf8mb4_unicode_ci || collation_database   | utf8mb4_unicode_ci || collation_server     | utf8mb4_unicode_ci |+----------------------+--------------------+3 rows in set (0.04 sec)mysql> show variables like 'character_set_%';+--------------------------+----------------------------+| 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       | /app/mysql/share/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

　　或者:

mysql> 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                       || character_sets_dir       | /app/mysql/share/charsets/ || collation_connection     | utf8mb4_unicode_ci         || collation_database       | utf8mb4_unicode_ci         || collation_server         | utf8mb4_unicode_ci         |+--------------------------+----------------------------+11 rows in set (0.00 sec)　

查看库的字符集：

show create database db_name;

查看表的字符集：

use db_name;show create table table_name;

查看某个表中所有列的字符集：

use db_name;show full columns from table_name;

查看mysql支持的字符集：

show charset;

2、修改字符集设置

例如修改字符集为utf8mb4，以支持emoji表情

1、配置文件修改

[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'

2、通过MySQL命令行修改

mysql> set character_set_client=utf8mb4;mysql> set character_set_connection=utf8mb4;mysql> set character_set_database=utf8mb4;mysql> set character_set_results=utf8mb4;mysql> set character_set_server=utf8mb4;mysql> set character_set_system=utf8mb4;mysql> set collation_connection=utf8mb4;mysql> set collation_database=utf8mb4;mysql> set collation_server=utf8mb4;

3、修改库的字符集

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4;

4、修改表的字符集

ALTER TABLE table_name  CONVERT TO CHARACTER SET utf8mb4;

5、修改列的字符集

ALTER TABLE t_order CHANGE 列名 列名 列类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;