UTF8 in MySQL != UTF8 in PHP?

Question

So I was having an issue with json_encode returning null that I found the solution for here, but I don't understand why it was an issue in the first place. The MySQL tables from which I was drawing the data are defined like

CREATE TABLE `super_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `values` text,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

so shouldn't values and name be utf8 encoded already when I pull them out in PHP? A simplified version of what I'm doing:

$sql = "SELECT * FROM super_table";
$query = $mysqli->query($sql);
$data = (object) $query->fetch_all(MYSQLI_ASSOC);
foreach ($data as $key => $val) {
    $data->$key = utf8_encode($val);
}
$result = array('success'=>$success, 'data'=>$data);
echo json_encode($result);

Why does not doing the extra utf8_encode step sometimes yield a null result when I try to json_encode it?

The `utf8` charset in mysql is a special UTF-8 charset with only 3 bytes instead of 4. `utf8mb4` is the true UTF-8 charset in mysql with 4 bytes. — Charlotte Dunois, Jan 27 '16 at 15:24
You should also check if the MySQLi connection in PHP is using UTF-8 for transmission between these two parties. http://php.net/mysqli_set_charset — Charlotte Dunois, Jan 27 '16 at 15:25
Also make sure your php file is in utf8 and that it outputs the data in utf8 (see the link that @Robert gives) — Niki van Stein, Jan 27 '16 at 15:31
If you need to use `utf8_encode()`, there is something wrong elsewhere. — Rick James, Jan 28 '16 at 04:29

score 1 · Accepted Answer · answered Jan 27 '16 at 15:30

Indeed, MySQL's utf8 is not universally understood UTF-8. Even though this is not your issue here... MySQL's utf8 is a subset of actual UTF-8 only covering the BMP and not supporting 4-byte characters. But this just means that high characters will get discarded; otherwise it's still UTF-8 compatible.
Your actual issue is that MySQL is just storing the data in utf8, but that says nothing about how you will receive the data in your database client. MySQL converts text on the fly from the stored encoding to the connection encoding (and vice versa). When connecting to the database in your PHP code, you can choose which encoding you prefer to receive your data in. Use $mysqli->set_charset('utf8') to retrieve your data in UTF-8.

UTF8 in MySQL != UTF8 in PHP?

1 Answers1