mysqlvarcharutf8mb4sequelpro

MySql varchar length for emojis on Sequel Pro

I'm using MySql version 5.6.46 on Linux. I have a column name varchar(50) COLLATE utf8mb4_bin and ENGINE=InnoDB DEFAULT CHARSET=utf8mb4.

When I tried to insert some data into the table. I find that,

for a (1 byte in utf8), it can store 50 maximum.

for 爱 (3 bytes in utf8, Chinese character love), it can store 50 maximum.

for 😁 (4 bytes in utf8, hex F09F9881), it can store 25 maximum.

This confuses me. Why Mysql is not treating one emoji as one character? If Mysql does the byte-count limit and improperly uses 3-byte-per-character, I'm expecting it can store 50*3/4=37. How on earth Mysql do the restriction?

----------UPDATE-------------

Thanks to your response, I figure it out. I am on MacOS X and I was using Sequel Pro 1.1.2. When I edit table content in the UI of the software, the maximum is 25 emoji and it toasts warning maximum text length is set to 50

Then I tried the raw hex approach on the server set name = X'F09F9881...F09F9881' and it can hold 50 emojis perfectly.

So this is a Sequel Pro issue. I will add Sequel Pro tag to this question. Hope this will help people who met the same issue. Thanks 😁 😁

Solution

  • No arithmetic needed.

    varchar(50)

    Holds 50 characters of any type. This will occupy up to 202 bytes (4 * 50 + 2 for a hidden length field).

    To debug your situation, please provide:

    SELECT VARIABLES LIKE 'char%';

SELECT col, HEX(col) FROM ...  -- to show what was stored.