character_set_client, _connection, and _results must all be utf8mb4 for that shortcake to be eatable.

Something, somewhere, is setting a subset of those individually. Rummage through my.cnf and phpmyadmin's settings -- something is not setting all three.

If SET NAMES utf8mb4 is executed, all three set correctly.

The sun shone because it is only 3-bytes - E2 98 80; utf8 is sufficient for 3-byte utf8 encodings of Unicode characters.

It is likely that your service/application is connecting with "utf8" instead of "utf8mb4" for the client character set. That's up to the client application.

For a PHP application see http://php.net/manual/en/function.mysql-set-charset.php or http://php.net/manual/en/mysqli.set-charset.php

For a Python application see https://github.com/PyMySQL/PyMySQL#example or http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-unicode

Also, check that your columns really are utf8mb4. One direct way is like this:

mysql> SELECT character_set_name FROM information_schema.`COLUMNS`  WHERE table_name = "user"   AND column_name = "displayname";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)

For me, it turned out that the problem lied in mysql client.

mysql client updates my.cnf's char setting on a server, and resulted in unintended character setting.

So, What I needed to do is just to add character-set-client-handshake = FALSE. It disables client setting from disturbing my char setting.

my.cnf would be like this.

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
...

Hope it helps.

I'm not proud of this answer, because it uses brute-force to clean the input. It's brutal, but it works

function cleanWord($string, $debug = false) {
$new_string = "";


for ($i=0;$i<strlen($string);$i++) {
$letter = substr($string, $i, 1);
if ($debug) {
echo "Letter: " . $letter . "<BR>";
echo "Code: " . ord($letter) . "<BR><BR>";
}
$blnSkip = false;
if (ord($letter)=="146") {
$letter = "&acute;";
$blnSkip = true;
}
if (ord($letter)=="233") {
$letter = "&eacute;";
$blnSkip = true;
}
if (ord($letter)=="147" || ord($letter)=="148") {
$letter = "&quot;";
$blnSkip = true;
}
if (ord($letter)=="151") {
$letter = "&#8211;";
$blnSkip = true;
}
if ($blnSkip) {
$new_string .= $letter;
break;
}


if (ord($letter) > 127) {
$letter = "&#0" . ord($letter) . ";";
}


$new_string .= $letter;
}
if ($new_string!="") {
$string = $new_string;
}
//optional
$string = str_replace("\r\n", "<BR>", $string);


return $string;
}


//clean up the input
$message = cleanWord($message);


//now you can insert it as part of SQL statement
$sql = "INSERT INTO tbl_message (`message`)
VALUES ('" . addslashes($message) . "')";

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

example query :

ALTER TABLE `reactions` CHANGE `emoji` `emoji` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

after that , successful able to store emoji in table :

Consider adding

init_connect = 'SET NAMES utf8mb4'

to all of your your db-servers' my.cnf-s.

(still, clients can (so will) overrule it)

Symfony 5 answer

Although this is not what was asked, people can land up here after searching the web for the same problem in Symfony.

1. Configure MySQL properly

☝️ See (and upvote if helpful) top answers here.

2. Change your Doctrine configuration

/config/packages/doctrine.yaml

doctrine:
dbal:
...
charset: utf8mb4

I was importing data via command:

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

This didnt work for me:

SET NAMES utf8mb4;

I had to add the CHARACTER SET to make it working:

LOAD DATA LOCAL INFILE
'E:\\wamp\\tmp\\customer.csv' INTO TABLE `customer`
CHARACTER SET 'utf8mb4'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Note, the target column must be also utf8mb4 not utf8, or the import will save (without errors thought) the question marks like "?????".

For codeigniter user, ensure your character set and collate setting in database.php is set properly, which is worked for me.

$db['default']['char_set'] = 'utf8mb4';
$db['default']['dbcollat'] = 'utf8mb4_unicode_ci';