First, check database CHARSET:
MariaDB [outdoors]> show create database outdoors;
| outdoors | CREATE DATABASE `outdoors` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
Then check the table CHARSET:
MariaDB [outdoors]> show create table backend_comment;
| backend_comment | CREATE TABLE `backend_comment` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`geo` varchar(10) DEFAULT NULL,
`content` varchar(250) NOT NULL,
`img` varchar(100) DEFAULT NULL,
`comment_id` int(11) DEFAULT NULL,
`create_time` datetime(6) NOT NULL,
`notify_id` bigint(20) NOT NULL,
`to_id` int(11) DEFAULT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `backend_comment_notify_id_61aef760_fk_backend_notify_id` (`notify_id`),
KEY `backend_comment_to_id_4dd23479_fk_auth_user_id` (`to_id`),
KEY `backend_comment_user_id_1ab394ea_fk_auth_user_id` (`user_id`),
CONSTRAINT `backend_comment_notify_id_61aef760_fk_backend_notify_id` FOREIGN KEY (`notify_id`) REFERENCES `backend_notify` (`id`),
CONSTRAINT `backend_comment_to_id_4dd23479_fk_auth_user_id` FOREIGN KEY (`to_id`) REFERENCES `auth_user` (`id`),
CONSTRAINT `backend_comment_user_id_1ab394ea_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
Test insert emoji content:
MariaDB [outdoors]> insert into backend_comment
(id, content, notify_id, user_id, create_time)
values (1, 'insert emoji test 😊', 1, 1, NOW());
ERROR 1366 (22007): Incorrect string value: '\xF0\x9F\x98\x8A' for column
outdoors
.backend_comment
.content
at row 1
**So what's the problem? help me please 😂**
----------
More information:
Server version: 10.11.8-MariaDB-0ubuntu0.24.04.1 Ubuntu 24.04
MariaDB and Mysql do not transform between different encodings automatically. When you try to insert data it will attempt to pass bytes from the client to the server and store as is. When there are no difference it works well, when there is a mismatch you can end up either with corrupted data, or an error.
It looks like by default mariadb
runs with utf8mb3
, and it does not support all the emojis, and therefore when server interprets utf8mb3
codes as utf8mb4
it results in invalid ones.
You could:
Set charset per connection, for example in CLI:
mariadb -u{user} --default-character-set=utf8mb4 -p
or
Set charset in the server configuration (see docs). Tho be ware what charset other connections can expect.