报错是因为utf8最多支持3个字节(中文),表情符是4个字节所以报错,这里要换成utf8的超集utf8mb4格式。解决步骤如下:
step1:查询mysql的字符集编码格式:
SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | E:\1tianque\software\Mysql\share\charsets\ |
| collation_connection | utf8 |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--------------------------+--------------------------------------------+
step2:修改mysql安装包下的 my.cnf(linux)或者my.ini(windows)。
[client]
#utf8mb4是utf8的超集,支持表情符的存储
default-character-set = utf8mb4[mysql]
default-character-set = utf8mb4[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
step3:再次查询mysql的字符集编码格式(如果没有改变,或只改变了部分,可以尝试重启电脑后再查询):
+--------------------------+--------------------------------------------+
| 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 | E:\1tianque\software\Mysql\share\charsets\ |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------------------------------+
这时server、client、connection都已改成utf8mb4。
如果用mysql -uname -ppassword 登陆后查询的字符集编码格式是修改后的,但用navicate连接查询显示的还是修改前的utf8,则需要把navicate连接的编码‘自动’改为utf8:
执行完后,重新打开连接,就正常显示utf8mb4了。
step4:测试,执行插入语句
insert into tablename values(id,'🙏🏻🙏🏻🙏🏻');
插入成功!