mysql 不支持 U+0000..U+FFFF 范围内的所有符号

Question

考虑下表:

CREATE TABLE t1 (f1 VARCHAR(255));

然后，就是ruby:

#!/usr/bin/env ruby
require 'json'
require 'sequel'
require 'mysql2'
DB = Sequel.connect(
    :adapter => 'mysql2',
    :database => 'd1',
    :user => '<user>',
    :password => '<password>',
    :encoding => 'utf8')
v1 = '{"a":"b\ud83c\udf4ec"}'
v2 = JSON.parse(v1)
p v2['a']
DB[:t1].truncate
DB[:t1].insert(f1: v2['a']);
p DB[:t1].first[:f1]

或php:

#!/usr/bin/env php
<?php
$dbh = new PDO('mysql:dbname=d1', '<user>', '<password>', [
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$dbh->exec('TRUNCATE TABLE t1');
$v1 = '{"a":"b\ud83c\udf4ec"}';
$v2 = json_decode($v1);
var_dump($v2->a);
$sth = $dbh->prepare("INSERT INTO t1 VALUES (?)");
$sth->execute([$v2->a]);
$sth = $dbh->query("SELECT * FROM t1");
var_dump($sth->fetch()['f1']);

数据库中获取的是b。我正在运行 mysql-5.1 和文档 says :

MySQL 5.1 supports two character sets for storing Unicode data:

• ucs2, the UCS-2 encoding of the Unicode character set using 16 bits per character.

• utf8, a UTF-8 encoding of the Unicode character set using one to three bytes per character.

These two character sets support the characters from the Basic Multilingual Plane (BMP) of Unicode Version 3.0. BMP characters have these characteristics:

• Their code values are between 0 and 65535 (or U+0000 .. U+FFFF).

我做错了什么？

UPD

$ mysql -BNe 'SHOW CREATE TABLE t1' d1
t1  CREATE TABLE `t1` (\n  `f1` varchar(255) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

看起来这两个转义序列只代表一个字符:RED APPLE (U+1F34E) 。第一个是代理人。和surrogates are :

The UCS uses surrogates to address characters outside the initial Basic Multilingual Plane without resorting to more than 16 bit byte representations.

所以一定是这样，生成的字符位于 BMP 之外。并且 mysql 的 utf8 字符集本身也不支持。