WL#1213: Implement 4-byte UTF8, UTF16 and UTF32

Affects: Server-5.5   —   Status: Complete

Pushed to 6.0.4 on Nov 27/2007.

Handle the rare characters which are outside the 
Unicode Basic Multilingual Plane (BMP). We will 
need to extend what we can do with the existing 
ucs2 and utf8 character sets, or add new Unicode 
character sets. We'll need up to four bytes per 

There will be UCS4 and full UTF8 support.
There will be UTF16 support.

WL#1213 is a step forward to have full support of Japanese encodings.
WL#1213 adds support for new characters, but does not provide proper
collations. In order to have collations, the following task should be
complete: WL#2673; fixed filesort.

MySQL supports only those Unicode characters in the   
Basic Multilingual Plane (BMP) -- the ones which we   
can store in 2 bytes with ucs2, or up to 3 bytes with   
utf8. Unicode 3.1 (2001) and subsequent Unicode
versions have 45,000 - 50,000 characters outside
the BMP.

With a UTF-16 character set, these supplementary   
characters require four bytes to store with   
"surrogate pairs" (a high 16-bit word and a low   
16-bit word).

With a UTF-32 (also known as UCS-4) character set,
all characters require 4 bytes to store.

With a UTF-8 extended character set,   
which we intend to call utf8mb4, these supplementary   
characters require four bytes to store.   

Additionally there is CESU-8, which is a UTF-8   
variant that takes 6 bytes to store a supplementary   
character: 3 bytes for the equivalent of the high   
part of a UTF-16 surrogate pair, 3 bytes for the   
equivalent of the low part of a UTF-16 surrogate   

Until now, we have regularly said that we intend to   
support supplementary characters someday, and CESU-8   

This task is necessary before MySQL can support
(Japanese) JIS 2004, or (Chinese) GB18030, which
contain characters that can't be converted to
Unicode BMP characters.

This task does not include any changes to handling
of combining characters, that's WL#3770.

Validity checking

There will still be illegal sequences. We should 
check for them.

If you allow anything at all, then how will you know how 
long a character is? You'd have to guess the length, and 
as soon as you make a wrong guess, everything that follows 
the bad character will be interpreted as garbage. (WL#3780
proposes to find and replace all 4-byte characters, but it
would be a temporary workaround.)

The Unicode Consortium publishes the sequences, e.g. in 

We have decided what Unicode version to support for
this purpose: "Unicode 5.0"
Formally, for this task, it is sufficient to say that
MySQL must support at least the version-3.1 supplementary
characters. But there was no objection to saying "Unicode 5.0".

Allow all characters which are acceptable in Unicode
5.0, including the so-called "non-characters". Do
validity checking on input.
Validity checks are: in utf16 there must not be a top
surrogate without a bottom surrogate (or a bottom
surrogate without a top surrogate), in any encoding
the code point values must not be greater than 0x10ffff.

Since the MySQL Reference Manual says we follow RFC 3629
(http://www.rfc-editor.org/rfc/rfc3629.txt), the only
valid characters are Unicode characters, and values
greater than 0x10ffff will never be possible.

There is another point here. Actually, we can parse the query several
times. For example, stored-program definition is parsed each time it is
loaded in the cache. We should perform the validity checking only once --
when the user submitted the query.


Character set name: utf32. Corresponds to: UCS-4, UTF-32.

Every character requires 32 bits. This is the simplest
new character set, but the least useful. Few people
want to use 4 bytes to store one character.

The original proposal said "character set name: ucs4",
but utf32 is the standard name. There will be no character
set named ucs4.


Character set name: utf16. Corresponds to: UTF-16.

Most characters require 16 bits. A BMP character looks
exactly the same in utf16 as in ucs2. A non-BMP character
will require a "surrogate pair", two 16-bit values in
sequence. For general description of surrogates, see:

Though utf16 was not part of the original task, it seems
to fit here. Originally there was thought about splitting
'utf16' into a separate task, but that won't happen.

Byte order in utf32 and utf16

Unicode allows for several variations of UTF-16 and UTF-32:
UTF-16       16-bit, big-endian unless string starts with BOM
UTF-16BE     16-bit, big-endian
UTF-16LE     16-bit, little-endian
UTF-32       32-bit, big-endian unless string starts with BOM
UTF-32BE     32-bit, big-endian
UTF-32LE     32-bit, little-endian

Big-endian means the high byte comes first.
BOM means Byte Order Mark, U+FEFF. Applications can
figure out whether a UTF-16 string is big-endian or
little-endian by checking whether U+FEFF looks like
0xFE 0xFF or 0xFF 0xFE.
A quick description is "Byte order encoding schemes" in a
Wikipedia article, http://en.wikipedia.org/wiki/UTF-16

Advantages of little-endian:
* ">" and "<" comparisons of utf32 strings can be more efficient
  when the processor favours little-endian, as Intel does
* SQL Server compatibility is enhanced because SQL Server,
  like most Microsoft products, favours little-endian

Advantages of big-endian:
* MySQL's old "ucs2" character set is always big-endian
  (please correct me if I'm wrong about the word "always")
* Unicode says that big-endian should be the default when
  there is no byte order mark, and Unicode recommends "Do
  not tag every string in a database or set of fields with a BOM".

Approved Decisions:
* MySQL ignores the byte order mark (BOM) or, more
  precisely, treats U+FEFF as ZERO WIDTH NON-BREAKING SPACE.
* MySQL utf32 and utf16 are big-endian on all platforms.
* Although MySQL's utf16 + utf32 correspond to Unicode's
  UTF-16BE and UTF-32BE, MySQL will not use the names
  utf16be or utf32be, utf16 and utf32 are SQL standard names.


Character set name: utf8 or utf8_4 or utf8mb4.
Corresponds to: UTF-8.

Three alternatives have been suggested:

1. Extend the old character set (utf8). Say that it's
still the old utf8 character set but it's possible to
use 4 bytes. Nobody else has seen a need for two
different character sets so utf8_4 or utf8mb4 would
be perceived as a defect or "gotcha" in MySQL.

2. Add a new character set named utf8mb4 (utf8 maximum
bytes 4) or utf8_4 (utf8 4-byte). It's necessary to know how
many bytes to reserve for CHAR(1) columns with
storage_engine=MyISAM, and for sort strings. The
decision long ago was: 3 bytes.
(The name "uf8mb4" is derived from suggestions in
the email thread "Valueclick UTF8".)
(The name "utf8_4" is derived from suggestions in
the email thread "Re: BUG#12600 and #9337".)

3. Rename the old character set (utf8) to something
else, for example utf8mb3 or utf8_3. Make a new
4-byte character set and call the new one utf8.

The meaning of the name "utf8" could be controlled by
a variable, for example "old_utf8=yes".
But we decided not to do this, it would cause sorrow, like sql_mode.

This task can't start until this utf8 matter is decided.

The choice will be "Alternative 2" and the new character
set will be named utf8mb4.

[ The above sentence is due to a decision change in February 2010. ]

utf8mb3 alias for utf8

In a future version it's possible that utf8 will become
the 4-byte utf8, and users who want to indicate 3-byte utf8
will have to say utf8mb3. To avoid some future problems which might
occur with replication when master and slave have different
versions, we will begin now to allow users to say 'utf8mb3'
in the CHARACTER SET clause, and 'utf8mb3_[collation_substring]'
in COLLATE clauses, where [collation_substring] is 'bin',
'czech_ci', 'danish_ci', 'esperanto_ci', 'estonian_ci', etc.
For example:

SELECT * FROM t WHERE s1 COLLATE utf8mb3_general_ci = 'x';
DECLARE x VARCHAR(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_danish_ci;

MySQL immediately converts the 'utf8mb3' in an alias to 'utf8',
FROM INFORMATION_SCHEMA.COLUMNS/etc. users will see the true
name, "utf8" or "utf8_[collation_substring]".

The "utf8mb3" alias is valid only in CHARACTER SET clauses,
and in certain other places. For example these are legal:
mysqld --character-set-server=utf8mb3
SET NAMES utf8mb3; /* and other SET statements which have similar effect */
SELECT _utf8mb3 'a';

No CESU-8, No Java Modified UTF-8

Character set name: cesu8. Corresponds to: CESU-8.

MySQL will not support CESU-8 or Java Modified UTF-8.

Essentially the difference between UTF-8 and CESU-8
lies in the starting point for supplementary characters.
In UTF-8 one encodes the code points, in CESU-8 one
encodes the surrogate-pair values that exist for UTF16.
See Unicode Technical Report #26
"Compatibility Encoding Scheme for UTF-16: 8-Bit (CESU-8)"

Sun's "Modified UTF-8" is apparently the same as CESU-8,
along with a special two-byte coding for U+0000:
c0 80. See "Modified UTF-8"
Since c0 80 is longer than 00, it is a "nonshortest
code value" and therefore illegal according to
unicode.org "Corrigendum #1: UTF-8 Shortest Form":

"Inheriting" repertoires and collations

The new character sets have the same collations as
the old (ucs2 and utf8) character sets, the only
changed things are the names and the code ranges.

Until WL#2673 "Unicode Collation Algorithm new version"
is complete, supplementary characters will all have the
same value for collating weights. For "general"
collations they'll be treated uas U+FFFD REPLACEMENT
CHARACTER. For "uca" collations their value for
weighting will be 0xfffd. Thus supplementary characters
will sort higher than BMP characters, and  equal to
other supplementary characters.

For example, the utf16 character set will have
utf16_general_ci, utf16_bin, utf16_unicode_ci,
utf16_romanian_ci, etc. And its default collation
will be utf16_general_ci because ucs2's default
collation is ucs2_general_ci.

For example, for valid BMP characters, the repertoire
of ucs2 and utf16 will be the same.

The utf16_bin flaw

The order of characters in utf16_bin (binary collation for utf16)
will differ from the order in utf8_bin. For example:

Here is a chart showing two rare characters. The first character is
in the range E000-FFFF, so it is greater than a surrogate but
less than a supplementary. The second character is a supplementary.

 Code point  Character                    utf16        utf8
 ----------  ---------                    -----        ----
 10384       UGARITIC LETTER DELTA        D8 00 DF 84  F0 90 8E 84

The two characters in the chart are in order by code point value,
and they are in order by utf8 value, but they are not in order
by utf16 value, because 0xff > 0xd8.

Tentative approved decision: MySQL accepts this flaw.

[ Note added 2010-11-26 ]
The order of characters will be according to code point,
not byte by byte. See also BUG#55980.


Functionality that is available for ucs2 / utf8 will be
available for the new character sets.

Limitations that apply to ucs2 / utf8 will apply
for the new character sets. For example:
Since ucs2 is not a legal client character set,
utf32 and utf16 are not legal client character sets,
until completion of WL#897 "Accept SQL statements
written with UCS-2".

Although utf32 and utf16 are supersets of ucs2, and
utf8_4 (if it exists) is a superset of utf8, there
is no compatibility for comparisons. For example,
WHERE _ucs2_column_value = _ucs4_column_value
will cause:
ERROR 1267 (HY000): Illegal mix of collations
(ucs2_general_ci,IMPLICIT) and (ucs4_general_ci,IMPLICIT)
for operation '='
The implementor will decide whether to keep or remove this test.
(See next section.)

Functionality: Decision of the implementor

The decision of the implementor is:
utf8mb4 should be a superset for 3-byte utf8.
When a character set is a superset of another character
set, MySQL already has rules for conversion (for example
one can concatenate latin1 with utf8 and get utf8).
We will apply the same rules when mixing utf8mb4 and utf8.


SELECT CONCAT(utf8mb4_column, utf8_column) FROM t1;
Instead of an "Illegal mix of collations" error,
this should return a utf8mb4 result, with collation of

SELECT * FROM utf8mb4_table, utf8_table
WHERE utf8mb4_table.utf8mb4_column = utf8mb3_table.utf8mb3_column;
Instead of an "Illegal mix of collations" error,
this should work according to the collation of utf8mb4_column.

We will not do something similar for similar new character set
pairs, for example we will not say that utf16 is a superset of ucs2.


Supplementary characters will be illegal in identifiers because:
(1) The character set for all metadata is UTF8, and the
    supplementary characters are in utf8mb4.
(2) Perhaps new difficulty will arise with file names
    (this statement is unchecked speculation).
(3) The new characters may be ignored for collation,
    for a while.


People involved with PHP / Java / ODBC / Merlin / etc.
were asked to comment on the amount of work that
this task would cause them. In summary, the response
was "very little". In detail, responses were:

Mark Matthews: "Java supports both  UTF-16, UTF-16 big
endian, UTF-16 little endian, and the same with UTF-32.
We won't have to do anything for Connector/J other than
add the mapping into the configuration that maps from
"java" names to "mysql" names for the character encodings."

Georg Richter: "PHP6 uses UTF-16."

Reggie Burnett: "MSDN indicates that the following are
valid encodings under .NET (utf-7, utf-8, utf-16, utf-32,
utf-32BE, unicodeFFEE) so it should be no problem for
.NET.  Since .NET supports it, I assume that also means
that Windows can handle utf-32 so Connector/ODBC on
Windows should work."

Mike Zinner: "The GUI Tools have full UTF8 support internally
via glib. On the front end we are limited to UTF-16/UCS-2
on Windows because afaik Windows does not support
UTF-32/UCS-4. I will check and get back to you."

[Notice the opinion difference about utf-32 Windows support.]

Tim Smith: "MySQL command-line clients should not have
serious problems adapting."


Marko Mäkelä wrote, in a comment for BUG#9337:  
"... InnoDB assumes in many places that UTF-8 sequences  
are at most 3 bytes. I am afraid that making MySQL  
accept wider than 3-byte UTF-8 characters will require  
an incompatible modification to the InnoDB data file  
format, at least in row_format=compact."   

Marko Mäkelä wrote, in a comment for BUG#12600:
I strongly recommend that new charset-collation
codes are introduced in WL#2829 for the encodings of
the full Unicode.  This would avoid incompatible changes
to data files. If WL#2829 does not already mention
this, please add it to the specification. Also, please notify
dev@innodb.com when the implementation starts, as we will need to
adjust some things inside InnoDB. [WL#2829 was cancelled
and its description is now part of WL#1213.]

[ Another Company ]

A section here about another company has been edited.
To see the section, click the "Progress" bar and look
for the changes on 2009-11-18.

The section mentions BUG#12600, BUG#9337, and JVER tests.

Other DBMSs

DB2 supports supplementary characters, and supports CESU-8.   

SQL Server 2005 does not support supplementary characters,   
its NCHAR and NVARCHAR data types are always two bytes per   
character and are defined as "character data types that ...   
use the UNICODE UCS-2 character set". There are a few   
surrogate-aware functions (len_s, left_s, right_s, sub_s,   
replace_s). A slide presentation by a Microsoft employee
says SQL Server is currently "surrogate safe" and in future
will be "surrogate aware".

Oracle10g supports supplementary characters with AL16UTF16,
UTF8, and AL32UTF8 character sets.

Oracle documentation makes it clear that Oracle "AL16UTF16" is
really UTF-16:

"Oracle's AL16UTF16 character set supports surrogate pairs."

So MySQL's UTF16 will be the same as Oracle's AL16UTF16.

Oracle documentation makes it clear that Oracle "UTF8" is
really CESU-8, Oracle "AL32UTF8" is really UTF-8:

"UTF8: Unicode 3.0 UTF-8 Universal character set, CESU-8 compliant
AL32UTF8: Unicode 3.1 UTF-8 Universal character set"

"Because UTF8 can require up to three bytes for each character, one
supplementary character is represented in two code points, totalling
six bytes. In AL32UTF8, one supplementary character is represented in
one code point, totalling four bytes."

"When the character set is determined to be UTF-8, the Oracle character
set UTF8 is returned by default unless 4-byte characters (supplementary
characters) are detected within the text. If 4-byte characters are
detected, then the character set is reported as AL32UTF8."

So MySQL's UTF8MB4 will not be the same as Oracle's UTF8.
It will be the same as Oracle's AL32UTF8.

SQL standard

For better compliance with SQL standard requirements:

1. Support the three names that the standard has for
"Universal Character Sets": UTF8, UTF16, UTF32.
So the name UTF32 should be preferred instead of UCS4.

2. All the Universal Character Sets must have the same
repertoire. That's only possible with a 4-byte UTF8.
(That is, it's only possible with what we call UTF8MB4.)

3. All the Universal Character Sets must be mutually
comparable. So "WHERE utf32_column = utf16_column"
shouldn't always cause an error. That's impossible
unless utf32 and utf16 can have the same collation.
(The standard suggests elsewhere that one collation
can apply to two character sets provided they have
the same repertoire, or that a collation that's
applicable for a superset can be applicable for a
subset.) MySQL already allows and prefers a superset
collation for expressions like "utf8_column = latin1_column"
but for "utf8_column = ucs2_column" there's an error.
Bar says: "It [for example comparison of a utf32_unicode_ci
column and a utf16_unicode_ci] should be not so hard
to implement."

4. Support a more recent version of the SQL standard.
The April 2006 draft of the SQL:200x standard no
longer mentions Unicode 3.0, it mentions "Unicode
4.0.1". (Elsewhere it mentions Unicode 3.1 but that
is possibly an error in the draft.) Of course the
real target for MySQL should be 5.0.

5. The name of the default collation should be
UCS_BASIC (for code point order) or UNICODE (for UCA
order). MySQL will be close enough by saying
that the collation is xxx_unicode_xxx. But
xxx_general_xxx (the current default) is not


Since some changes to existing behaviour are contemplated,
there are upgrade considerations.

1. If UTF8 character set name changes, or if default name
changes for identifiers. (Due to a February 2010 decision,
this will not happen.)

2. If xxx_general_ci ceases to be the default collation
(because UCS_BASIC or UNICODE or xxx_unicode_ci is chosen
as the default). Bar's comments about this consideration:

> To make upgrade easier, we should probably start
> always print character set and collation in "SHOW CREATE TABLE",
> even if character set and collation for a column are the default ones -
> currently they are not displayed:
> create table t1 (a char(1), b char(1) collate latin1_bin);
> show create table t1;
>    `a` char(1) default NULL, -- no character set and collate clauses
>    `b` char(1) character set latin1 collate latin1_bin default NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -- no collate clause
> Another way to simplify upgrade could be allowing users to reassign
> the default collation for a character set.
> Something like:

3. If a length is already 2/3 of the maximum length. For example,
these statements currently cause no errors or warnings:
CREATE TABLE t (s1 VARCHAR(16000) CHARACTER SET UTF8) engine=myisam;
CREATE INDEX i ON t (s1(333));
but with 4-byte UTF8, that is, if one says CHARACTER SET UTF8MB4
rather than CHARACTER SET UTF8, the CREATE TABLE will cause a warning
"Converting column 's1' from VARCHAR to TEXT" and the
CREATE INDEX will cause a warning "Specified key was too long;
max key length is 999 bytes". See BUG#4541 for a taste of possible reactions.

(I am talking here about the case, where the user manually creates a
new table and on the 3-bytes-utf8-version the statement works, but on the
4-bytes-utf8-version it does not.)


"Unicode implementation in DB2 Universal Database"   

"Oracle Globalization"   

"SQL Server 2005 Books Online"   

A handy online converter from code points to UTF-16 and UTF-8 values:

Related Bugs/Requests

"BUG#9337 misleading warning when inserting invalid utf8 data"

"BUG#12600 Inserting into LONGTEXT gives strange error message"   

"BUG#14052 No UTF-16 charset encoding available (surrogate pairs)"

"BUG#25666 UTF-8 support beyong the BMP"

"BUG#38885 a japanese character make sql bad"

"Valueclick UTF8" email thread
[ mysql intranet ] /secure/mailarchive/mail.php?folder=4&mail=8297

"UTF-16 or UTF-32 support?"

"supplementary unicode characters"

"Surrogate pairs? Non-BMP characters?"

Design details are included in the HLS.