MySQL/MariaDB中的UTF-8并不是真正的UTF-8，其中的UTF8MB4才是真正的UTF-8。因此推荐使用UTF8MB4。
先查看自己数据库的默认字符集：
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";+--------------------------+----------------------------------------------------+| Variable_name | Value|+--------------------------+----------------------------------------------------+| character_set_client| gbk || character_set_connection | gbk || character_set_database | latin1|| character_set_filesystem | binary|| character_set_results | gbk || character_set_server| latin1|| character_set_system| utf8 || character_sets_dir | D:\download\mariadb-10.3.15-winx64\share\charsets\ |+--------------------------+----------------------------------------------------+8 rows in set (0.001 sec)+----------------------+-------------------+| Variable_name | Value |+----------------------+-------------------+| collation_connection | gbk_chinese_ci || collation_database | latin1_swedish_ci || collation_server| latin1_swedish_ci |+----------------------+-------------------+3 rows in set (0.001 sec)
看到了默认的基本都是GBK和latin1。
然后查看有哪些CHARSET可以使用：
MariaDB [(none)]> SHOW CHARSET;+----------+-----------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+-----------------------------+---------------------+--------+| big5| Big5 Traditional Chinese | big5_chinese_ci|2 || dec8| DEC West European | dec8_swedish_ci|1 || cp850 | DOS West European | cp850_general_ci |1 || hp8| HP West European | hp8_english_ci|1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci |1 || latin1 | cp1252 West European | latin1_swedish_ci |1 || latin2 | ISO 8859-2 Central European | latin2_general_ci |1 || swe7| 7bit Swedish| swe7_swedish_ci|1 || ascii | US ASCII| ascii_general_ci |1 || ujis| EUC-JP Japanese | ujis_japanese_ci |3 || sjis| Shift-JIS Japanese| sjis_japanese_ci |2 || hebrew | ISO 8859-8 Hebrew | hebrew_general_ci |1 || tis620 | TIS620 Thai | tis620_thai_ci|1 || euckr | EUC-KR Korean| euckr_korean_ci|2 || koi8u | KOI8-U Ukrainian | koi8u_general_ci |1 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci |2 || greek | ISO 8859-7 Greek | greek_general_ci |1 || cp1250 | Windows Central European | cp1250_general_ci |1 || gbk| GBK Simplified Chinese| gbk_chinese_ci|2 || latin5 | ISO 8859-9 Turkish| latin5_turkish_ci |1 || armscii8 | ARMSCII-8 Armenian| armscii8_general_ci |1 || utf8| UTF-8 Unicode| utf8_general_ci|3 || ucs2| UCS-2 Unicode| ucs2_general_ci|2 || cp866 | DOS Russian | cp866_general_ci |1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci |1 || macce | Mac Central European | macce_general_ci |1 || macroman | Mac West European | macroman_general_ci |1 || cp852 | DOS Central European | cp852_general_ci |1 || latin7 | ISO 8859-13 Baltic| latin7_general_ci |1 || utf8mb4 | UTF-8 Unicode| utf8mb4_general_ci |4 || cp1251 | Windows Cyrillic | cp1251_general_ci |1 || utf16 | UTF-16 Unicode | utf16_general_ci |4 || utf16le | UTF-16LE Unicode | utf16le_general_ci |4 || cp1256 | Windows Arabic | cp1256_general_ci |1 || cp1257 | Windows Baltic | cp1257_general_ci |1 || utf32 | UTF-32 Unicode | utf32_general_ci |4 || binary | Binary pseudo charset | binary |1 || geostd8 | GEOSTD8 Georgian | geostd8_general_ci |1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci |2 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |3 |+----------+-----------------------------+---------------------+--------+40 rows in set (0.000 sec)
看到了里面有utf8mb4 ，非常老的MySQL可能不存在utf8mb4 编码。
运行命令：
MariaDB [(none)]> SET NAMES 'utf8mb4';Query OK, 0 rows affected (0.000 sec)MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";+--------------------------+----------------------------------------------------+| Variable_name | Value|+--------------------------+----------------------------------------------------+| character_set_client| utf8mb4 || character_set_connection | utf8mb4 || character_set_database | latin1|| character_set_filesystem | binary|| character_set_results | utf8mb4 || character_set_server| latin1|| character_set_system| utf8 || character_sets_dir | D:\download\mariadb-10.3.15-winx64\share\charsets\ |+--------------------------+----------------------------------------------------+8 rows in set (0.001 sec)+----------------------+--------------------+| Variable_name | Value |+----------------------+--------------------+| collation_connection | utf8mb4_general_ci || collation_database | latin1_swedish_ci || collation_server| latin1_swedish_ci |+----------------------+--------------------+3 rows in set (0.001 sec)
可以看到collation_connection，character_set_client,character_set_connection,character_set_results都变成了utf8mb4了。
再运行命令：
MariaDB [(none)]> set character_set_server = utf8mb4;set character_set_database = utf8mb4;set collation_database = utf8mb4_general_ci ;set collation_server = utf8mb4_general_ci ;Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)Query OK, 0 rows affected (0.000 sec)MariaDB [(none)]> show variables like "%character%";show variables 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 | D:\download\mariadb-10.3.15-winx64\share\charsets\ |+--------------------------+----------------------------------------------------+8 rows in set (0.001 sec)+----------------------+--------------------+| Variable_name | Value |+----------------------+--------------------+| collation_connection | utf8mb4_general_ci || collation_database | utf8mb4_general_ci || collation_server| utf8mb4_general_ci |+----------------------+--------------------+3 rows in set (0.001 sec)
看到已经都变成了utf8mb4。
最坑的一点来了，如果MySQL/MariaDB重启了，上面的设置就无效了！！！
最坑的一点来了，如果MySQL/MariaDB重启了，上面的设置就无效了！！！
最坑的一点来了，如果MySQL/MariaDB重启了，上面的设置就无效了！！！
所以你需要把配置写到文件中去。运行命令查看你应该写到哪个文件：
PS C:\Users\peter> mysqld --help --verbose-06-24 10:01:13 0 [Note] Plugin 'FEEDBACK' is disabled.D:\download\mariadb-10.3.15-winx64\bin\mysqld.exe Ver 10.3.15-MariaDB for Win64 on AMD64 ( binary distribution)Copyright (c) 2000, , Oracle, MariaDB Corporation Ab and others.Starts the MariaDB database server.Usage: D:\download\mariadb-10.3.15-winx64\bin\mysqld.exe [OPTIONS]NT and Win32 specific options:--install Install the default service (NT).--install-manual Install the default service started manually (NT).--install service_name Install an optional service (NT).--install-manual service_name Install an optional service started manually (NT).--remove Remove the default service from the service list (NT).--remove service_name Remove the service_name from the service list (NT).--enable-named-pipe Only to be used for the default server (NT).--standalone Dummy option to start as a standalone server (NT).Default options are read from the following files in the given order:C:\Windows\my.ini C:\Windows\f C:\my.ini C:\f D:\download\mariadb-10.3.15-winx64\my.ini D:\download\mariadb-10.3.15-winx64\f D:\download\mariadb-10.3.15-winx64\data\my.ini D:\download\mariadb-10.3.15-winx64\data\fThe following groups are read: mysqld server mysqld-10.3 mariadb mariadb-10.3 client-server
里面提到了文件应该放在目录里D:\download\mariadb-10.3.15-winx64\data\f
新建文件f，内容如下：
[client]default-character-set=utf8mb4[mysql]default-character-set=utf8mb4[mysqld]collation-server = utf8mb4_unicode_ciinit-connect='SET NAMES utf8mb4'character-set-server = utf8mb4
然后重启数据库，发现字符集已经变成了utf8mb4：
MariaDB [(none)]> show variables like "%character%";show variables 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 | D:\download\mariadb-10.3.15-winx64\share\charsets\ |+--------------------------+----------------------------------------------------+8 rows in set (0.001 sec)+----------------------+--------------------+| Variable_name | Value |+----------------------+--------------------+| collation_connection | utf8mb4_general_ci || collation_database | utf8mb4_unicode_ci || collation_server| utf8mb4_unicode_ci |+----------------------+--------------------+3 rows in set (0.001 sec)
修改成功。
如果你的数据库中已经存在表了，建议进行修复一下
mysqlcheck -u root -p --auto-repair --optimize --all-databases
