Character Sets

Character Sets

Character Sets, Collations and Binary Data

The connection has its own character set, and it must match what PHP sends: UTF-8. Name charset=utf8mb4 in the DSN, never the three-byte utf8 (Subsection 3.3.8), and never switch later with SET NAMES alone, because PDO's quoting would not know. The DSN setting has a catch, though:

The collation the DSN gives you, and the fixPHP

<?php
$check = "SELECT @@collation_connection, 'ß' = 'ss', LENGTH('\u{1F418}')";
$pdo = require 'db.php';
echo implode(' | ', $pdo->query($check)->fetch(PDO::FETCH_NUM)), "\n";
$fixed = new Pdo\Mysql('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_app',
  getenv('SHOP_DB_PASSWORD'),
  [Pdo\Mysql::ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci']);
echo implode(' | ', $fixed->query($check)->fetch(PDO::FETCH_NUM)), "\n";

Output

utf8mb4_general_ci | 0 | 4
utf8mb4_0900_ai_ci | 1 | 4

The client announces utf8mb4 with its legacy default collation, utf8mb4_general_ci, while the shop tables use utf8mb4_0900_ai_ci. Comparisons with a column use the column's collation, but literals and parameters compared with each other use the connection's, where ß and ss differ; ATTR_INIT_COMMAND aligns the two, and the four-byte emoji survives either way. Store images and other binary data in VARBINARY or BLOB columns, bound as a string or as a stream with PDO::PARAM_LOB: binary columns have no character set, so 1,008 bytes starting with the PNG signature, bound from php://memory, came back intact.

MENU