MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.6.48


CREATE TABLE a 
(
  i INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b VARCHAR (255)
) ENGINE = InnoDB DEFAULT CHARSET utf8mb4;
INSERT INTO a (b) VALUES ('xxx'), ('yyyyy'), ('zzzzzzzzzz');
--
-- AS expected
--
SELECT LENGTH(b) AS a_len_1, CHAR_LENGTH(b) AS ca_len_1

FROM a;


a_len_1
ca_len_1


3
3

5
5

10
10


INSERT INTO a (b) VALUES 
('あ A い I う U え E お O.'),  -- 20 characters AFAICS
('想看什頭清只傷精，中美觀的接委主們認可易畫等助海因'),
(_utf8mb4 'あ A い I う U え E お O.'),  -- 20 characters AFAICS
(_utf8mb4 0xE38182204120E38184204920E38186205520E38188204520E3818A204F2E),
(_utf8mb3 '想看什頭清只傷精，中美觀的接委主們認可易畫等助海因');  -- 25 characters AFAICS
--
-- NOT as expected
--
SELECT b AS b_str,
       LENGTH(b) AS b_byte_len,
       CHAR_LENGTH(b) AS b_char_len,
       HEX(b) AS 'bytes'
FROM a;


b_str
b_byte_len
b_char_len
bytes


xxx
3
3
787878

yyyyy
5
5
7979797979

zzzzzzzzzz
10
10
7A7A7A7A7A7A7A7A7A7A

あ A い I う U え E お O.
48
30
C3A3C281E2809A204120C3A3C281E2809E204920C3A3C281E280A0205520C3A3C281CB86204520C3A3C281C5A0204F2E

想看什頭清只傷精，中美觀的接委主們認可易畫等助海因
163
75
C3A6C692C2B3C3A7C593E280B9C3A4C2BBE282ACC3A9C2A0C2ADC3A6C2B8E280A6C3A5C28FC2AAC3A5E2809AC2B7C3A7C2B2C2BEC3AFC2BCC592C3A4C2B8C2ADC3A7C2BEC5BDC3A8C2A7E282ACC3A7C5A1E2809EC3A6C5BDC2A5C3A5C2A7E2809DC3A4C2B8C2BBC3A5E282ACE28098C3A8C2AAC28DC3A5C28FC2AFC3A6CB9CE2809CC3A7E280A2C2ABC3A7C2ADE280B0C3A5C5A0C2A9C3A6C2B5C2B7C3A5E280BAC2A0

? A ? I ? U ? E ? O.
30
20
E38182204120E38184204920E38186205520E38188204520E3818A204F2E

? A ? I ? U ? E ? O.
30
20
E38182204120E38184204920E38186205520E38188204520E3818A204F2E

?????????????????????????
75
25
E683B3E79C8BE4BB80E9A0ADE6B885E58FAAE582B7E7B2BEEFBC8CE4B8ADE7BE8EE8A780E79A84E68EA5E5A794E4B8BBE58091E8AA8DE58FAFE69893E795ABE7AD89E58AA9E6B5B7E59BA0


--
-- NOT as expected (there's an encoding bug)
--
SELECT HEX(_utf8mb4 'あ A') AS 'UTF-8',
       HEX('あ A'),
       _utf8 0xE381822041 AS 'UTF-8 bytes don\'t work in UTF-8',
       _latin1 0xE381822041 AS 'Latin-1 charset using UTF-8 bytes?'


UTF-8
HEX('あ A')
UTF-8 bytes don't work in UTF-8
Latin-1 charset using UTF-8 bytes?


E381822041
E381822041
? A
あ A

b_str	b_byte_len	b_char_len	bytes
xxx	3	3	787878
yyyyy	5	5	7979797979
zzzzzzzzzz	10	10	7A7A7A7A7A7A7A7A7A7A
あ A い I う U え E お O.	48	30	C3A3C281E2809A204120C3A3C281E2809E204920C3A3C281E280A0205520C3A3C281CB86204520C3A3C281C5A0204F2E
想看什頭清只傷精，中美觀的接委主們認可易畫等助海因	163	75	C3A6C692C2B3C3A7C593E280B9C3A4C2BBE282ACC3A9C2A0C2ADC3A6C2B8E280A6C3A5C28FC2AAC3A5E2809AC2B7C3A7C2B2C2BEC3AFC2BCC592C3A4C2B8C2ADC3A7C2BEC5BDC3A8C2A7E282ACC3A7C5A1E2809EC3A6C5BDC2A5C3A5C2A7E2809DC3A4C2B8C2BBC3A5E282ACE28098C3A8C2AAC28DC3A5C28FC2AFC3A6CB9CE2809CC3A7E280A2C2ABC3A7C2ADE280B0C3A5C5A0C2A9C3A6C2B5C2B7C3A5E280BAC2A0
? A ? I ? U ? E ? O.	30	20	E38182204120E38184204920E38186205520E38188204520E3818A204F2E
? A ? I ? U ? E ? O.	30	20	E38182204120E38184204920E38186205520E38188204520E3818A204F2E
?????????????????????????	75	25	E683B3E79C8BE4BB80E9A0ADE6B885E58FAAE582B7E7B2BEEFBC8CE4B8ADE7BE8EE8A780E79A84E68EA5E5A794E4B8BBE58091E8AA8DE58FAFE69893E795ABE7AD89E58AA9E6B5B7E59BA0