1

I make a query using the mysql workbench to create a table and insert 1 record that contains a 4 byte emoji using: 

SET NAMES utf8mb4; CREATE TABLE IF NOT EXISTS tbl_message message_message 
    VARCHAR(1000) DEFAULT CHARSET=utf8mb4;
INSERT INTO 'tbl_message message_message VALUES ('Hello ');

When I query the data it is correct and shows a data length of 10 bytes. When I close mysql workbench and go back the record shows 'Hello ?' and a data length of 7 bytes.

My my.cnf shows:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE

character_set_server=utf8mb4

collation_server=utf8mb4_unicode_ci

What is causing the 4 byte emoji to change to a 3 byte ?

June7
  • 19,874
  • 8
  • 24
  • 34
glennf
  • 53
  • 5
  • "When I close mysql workbench and go back the record shows 'Hello ?' and a data length of 7 bytes." ... where does it show this? – eis Jan 21 '18 at 21:36
  • 1
    how do you check byte size? if you are saying that correct size is 10 that means you expect the emoji character to take 4 bytes, which looks like utf-16 but not utf-8. please share the code where you check string byte size please for both cases. when it is 10 and when it is 7? – Alex Jan 21 '18 at 21:36
  • @Alex 4 bytes should be correct, and 10 for the whole string: https://mothereff.in/byte-counter – eis Jan 21 '18 at 21:39
  • you've tagged this with mysql-python, so is it in python where you have the 7-byte data issue? – eis Jan 21 '18 at 21:42
  • ok. I can agree with 10 byte estimation of original string. But how do you measure the size in the code? before insert? how do you measure it after? after select? – Alex Jan 21 '18 at 21:46
  • Possible duplicate of [How to store Emoji Character in My SQL Database](https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-my-sql-database) – Alex Jan 21 '18 at 22:03
  • To see the length in bytes I used the Mysql workbench - opened the table, selected the row, and selected show value in editor - it displays the binary values, text value and data length --- As for the Python question, I am trying to write this data and retrieve and re-display using Python 2.7. I was trying to sort out if the data is stored correctly then I'll try to retrieve it via code - I'll need to get it from Mysql and then pass to HTML when rendering if you have any ideas. – glennf Jan 21 '18 at 23:20
  • Alex - thanks for the pointer to the other article but I made those changes prior to posting my question (details in my original post include those details) – glennf Jan 21 '18 at 23:26
  • SOLVED - it turns out the mysql view is just that, a view, the actual data values were not changed - to see the correct vales (actual emoji) in the database via mysql I run 'SET NAMES utf8mb4;' from a query within mysql and then it shows correctly until I restart mysql, then the query needs to be run again – glennf Jan 22 '18 at 18:43

0 Answers0