CODEWITHSUNDEEP
mysqlvarcharutf8mb4sequelpro
matrix
matrix

Reputation: 349

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 😁 😁

Upvotes: 2

Views: 1085

Answers (1)

Rick James
Rick James

Reputation: 142278

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.

Upvotes: 1

Related Questions