MySQL does not process some utf8 chars

Question

Table:

CREATE TABLE `test` (
  `f` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Just after connection I send following SQL:

SET NAMES utf8;

Then SQL:

INSERT INTO `test` (`f`) VALUES ("с😊с-е в терновнике");

As a result in the table I have 1 record with value "с" just up to 😊 sign. Is there a chance to correctly handle all such symbols? All this done from PHP if this matters.

what does a select length(f) from test say? If it's 1, then your text really is truncated. If it's bigger, then your string is really in there, and you're doing something wrong upon retrieving it. — Marc B
– Marc B, Commented Apr 17, 2014 at 16:27
select f, length(f) from test gives length(f) = 2. 2 seems to be equal to 1 char 1 utf8 is multibyte — Index
– Index, Commented Apr 17, 2014 at 16:44

Bill Karwin · Accepted Answer · 2014-04-17 17:32:25Z

3

That character is Unicode Character 'SMILING FACE WITH SMILING EYES' (U+1F60A).

U+1F60A is in plane 1, the Supplementary Multilingual Plane, but MySQL's utf8 supports only plane 0, the Basic Multilingual Plane. So you'll have to use utf8mb4.

That symbol is also not widely supported in fonts, so you may have a hard time displaying it.

Re your comment, here's a demonstration of filtering out characters beyond the basic plane.

<?php

$str = "с😊с-е в терновнике";
$str = preg_replace('/[^\x{0000}-\x{ffff}]/u', '', $str);
echo $str . "\n";

But if it wasn't clear from my answer above, MySQL does support the supplemental plane in utf8mb4.

edited Apr 17, 2014 at 17:32

answered Apr 17, 2014 at 16:43

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Index Over a year ago

Is there a nice way to remove all chars that mysql does not support using PHP. Or perhaps you can give me a list of such chars?

Vatev Over a year ago

this question might be relevant.

Bill Karwin Over a year ago

@Vatev,thanks, both answers on that question are interesting.

Collectives™ on Stack Overflow

MySQL does not process some utf8 chars

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related