2

I want to store emojis (android or iphone) in my mysql database, I tried many tutorials and SO posts, on the internet like :

How to insert utf-8 mb4 character(emoji in ios5) in mysql?

http://andy-carter.com/blog/saving-emoticons-unicode-from-twitter-to-a-mysql-database

MySQL utf8mb4, Errors when saving Emojis

I changed all things those tuts require. I have now the following mysql configuration :

character_set_client : utf8mb4

character_set_connection : utf8mb4

character_set_database : utf8mb4

character_set_filesystem : binary

character_set_results : utf8mb4

character_set_server : latin1

character_set_system :utf8

collation_connection : utf8mb4_unicode_ci

collation_database : utf8mb4_unicode_ci

collation_server : latin1_swedish_ci

But I can't still store 4 bits emojis on my mysql database, I have "????", which is pretty annoying.

Please help.

Community
  • 1
  • 1
kabrice
  • 1,475
  • 6
  • 27
  • 51
  • Your assumption is wrong. MySQL definitely saved it. You're pulling the info out and displaying it **wrong**. Since you didn't tell us what you're using to view those characters - all that's left is guessing around. – Mjh Aug 01 '16 at 12:35
  • Thank you very much for answering me. Please, I use html tags and php tools (like echo) to display it on my website. Do you need please other informations ? – kabrice Aug 01 '16 at 12:40
  • Your HTML page must be set to display `utf8` correctly. Connection between PHP and MySQL must be set to `utf8`. You can find more info by googling about these topics, it's much better than me posting links here (there's also so many answers here at SO). – Mjh Aug 01 '16 at 12:43
  • Thank you very much. I changed the meta tag to 

 in my html page, but the problem is still there, I can't display emojis encoded in utf8mb4. Thx – kabrice Aug 01 '16 at 12:51

1 Answers1

2

"4 bytes", not "4 bits".

The column/table needs to be CHARACTER SET utf8mb4.

<meta charset="UTF8">, not utf8mb4 -- This is talking to HTML, not MySQL. Outside of MySQL, the proper name is "UTF-8" or "UTF8"; utf8mb4 is a MySQL kludge.

See "question marks" in https://stackoverflow.com/a/38363567/1766831

Exactly 4 question marks implies that the column was not changed. Use ALTER TABLE ... CONVERT TO ...;

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thank you very much for answering, and excuse me for the delay. But after several attempts, I get the same problem. I looked into your link and tried all the advise, especially the one talking about Question Marks issue (which is very similar to my issue) but nothing is working. – kabrice Aug 01 '16 at 17:13
  • Let's check something else -- please do the `SELECT .. HEX ...` in that link. If you get `3F3F3F3F`, then question marks were stored, and the data is lost. – Rick James Aug 01 '16 at 20:41
  • Let's see the connection parameters in mysqli_* or PDO, plus actual statements that are failing. – Rick James Aug 01 '16 at 20:51
  • Thank you again for your support. When I type SELECT .. HEX .., I get this 0000003F0000003F0000003F0000003F0000003F0000003F00, and I think my data is not lost – kabrice Aug 02 '16 at 07:49
  • I only have 4 connection parameters : hostname, login, password, and the database name – kabrice Aug 02 '16 at 07:52
  • Yikes... `0000003F`, not `3F`? Sounds like "utf32" -- don't use that unless that is what the _client_ has. Can you dump the hex in the client _before_ storing into MySQL? – Rick James Aug 02 '16 at 16:47
  • Thx for your support and sorry for the delay. Please, how can I do that ? Thx. – kabrice Aug 02 '16 at 23:12
  • If you are using PHP, do something like `echo bin2hex($text);` – Rick James Aug 02 '16 at 23:58
  • OK, but please, $text represent what ? What is its content ? Thx – kabrice Aug 03 '16 at 00:07
  • The string you are about to `INSERT` into the MySQL table. – Rick James Aug 03 '16 at 00:07
  • Which PHP API are you using? PDO or mysqli? – Rick James Aug 03 '16 at 00:08
  • I want to insert emojis, and my IDE doesn't support that. – kabrice Aug 03 '16 at 00:10
  • My IDE (Intellij) supports 3 bytes emoji like ✌ but not the 4 bytes (like ). Thx – kabrice Aug 03 '16 at 00:17
  • If the limitation is in Intellig, then it must be fixed there. The fix for the MySQL side is to use utf8mb4 instead of utf8 and use it throughout (connection, column definition, etc). – Rick James Aug 03 '16 at 00:31
  • Yes I thought I've already did it, but when I retry, I got an error. In fact, I've a table called "comment", and the emojis should be store in the column called "comment_content" (whose the type is longtext). But when I type this command line : ALTER TABLE comment CHANGE comment_content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, I got this error : "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' at line 1" – kabrice Aug 03 '16 at 00:41
  • If you _already_ stored a 4-byte Emoji into the table when it was `utf8`, it would not get stored correctly. After the `ALTER`, the store should work. – Rick James Aug 03 '16 at 00:44
  • Yes, but it seems that my table is not configured to store 4-byte Emoji, I got an error ("#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' at line 1") when I want to alter the configuration (ALTER TABLE comment CHANGE comment_content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci), – kabrice Aug 03 '16 at 00:48
  • Change `CHANGE` to `MODIFY`. – Rick James Aug 03 '16 at 00:56
  • Thx sir, it's what I did, and I got this message : " MySQL returned an empty result set (i.e. zero rows). (Query took 0.0022 seconds.)", there wasn't error anymore. But when I type INSERT INTO comment (NUM_CONTRIBUTION, COMMENT_CONTENT, NUM_USER) VALUES (248, , 123), I got this error : "#1300 - Invalid utf8mb4 character string: '\xF0\x9F\x98\x82'". Thx in advance. – kabrice Aug 03 '16 at 01:03
  • Please provide `SHOW CREATE TABLE comment` – Rick James Aug 03 '16 at 01:11
  • SHOW CREATE TABLE comment gave me something like : "Table :comment | Create Table : CREATE TABLE `comment` ( `NUM_COMMENT` i...". Thx – kabrice Aug 03 '16 at 01:20
  • Thx for answering me, please what do you want to see ? As I said, when I execute "SHOW CREATE TABLE comment", I get something like : "Table :comment | Create Table : CREATE TABLE comment ( NUM_COMMENT i..." in my phpmyadmin. Thx again – kabrice Aug 03 '16 at 04:03
  • Hi guys, I just solved my issue. I changed the columns property containing the emojis from LongText into LongBlob. Many many thanks for your time. – kabrice Oct 30 '16 at 20:56
  • Yes, that will get you going, but it is the "wrong" way. – Rick James Oct 30 '16 at 22:54
  • Thx for the feedback. Please what is the "right" way? And what's the problem with this way ? – kabrice Oct 31 '16 at 00:18