RE: Subj: MySQl and Unicode support

From: Philippe Verdy (
Date: Thu Feb 22 2007 - 07:59:45 CST

  • Next message: Henrik Theiling: "Re: Subj: MySQl and Unicode support"

    MySQL car perfectly support Unicode texts, using UTF-8 as the base encoding
    for the strings; however some SQL operators will not work as expected,
    because they use byte positions, instead of character positions (in UTF-8,
    Uniode characters are encoded with a variable number of bytes, from 1 byte
    for ASCII characters up to U+007F, to 4).

    If you don't use the SQL string functions that compute:
    * substrings based on character indices instead of byte indices
    * case conversion,
    * uncased string matching,
    * sorting the results,
    then you an perfectly use it. Some newer versions of MySQL have the support
    of "nvarchar" to support Unicode strings with the Unicode character
    semantics, but be careful: the data type maximum length will still be based
    on the internal code units, not really on the Unicode characters (i.e. code
    points here). To allow this support, you need to install MySQL with extra
    support libraries, notably a database of character properties (used for
    proper case conversion and uncased/case-folded string matching).

    If your MySQL does not have such support, you can still use MySQL, but all
    string handling that require Unicode semantics should be performed on the
    SQL client side (for example in JSP, ASP, PHP, C, C++, #, ... provided that
    the client side language is also installed with the Unicode support).

    In fact, ANY database engine that supports storing and retrieving vectors of
    8-bit bytes without altering them (for example a database that fully
    supports ISO-8859-1) can be used to store UTF-8 encoded text. If you
    carefully use the good practice of CASE design for your database, all
    queries to the SQL engine will not require any Unicode-dependant features.

    Note that sorting (SELECT... ORDER BY...) may be an issue if you expect the
    results from the database to be in the appropriate order; but sorting also
    has a cost on the database engine and should not influence the results (if
    you follow the good practices of CASE design, using unique IDs for table
    rows rather than matching them with complex joins with strings), so you may
    as well accumulate the results on the client-side, and realize the sort in
    the client, rather than in the database engine.

    If you still want to use strings for table row unique identifiers used as
    joining keys, make sure that your system will accept variable length keys;
    if the key represents a person name, make sure that the string length
    restriction in the table declaration will be sufficient to store all
    possible encoded key lengths after computing the effective key string, if
    the key string can contain any Unicode character.

    Note also that if you need a particular sort to be still performed by the
    SQL engine, one solution is to include in your tables a column for storing
    the sort keys using a binary encoding of collation keys (see the Unicode UCA
    algorithm about how to compute collation keys); then store the collation key
    in the table in addition to the effective string (this will of course have
    an impact on the table storage size, and on the performance due to extra
    I/O, as less rows will fit in the same page on disk).

    Note that database engines that have the full support for Unicode use
    UCA-computed collation keys when they create an index on columns declared to
    use an Unicode-compatible string data type, for example NVARCHAR(n). You
    don't need to declare an additional column for the collation key, as the
    database engine performs itself the computation of collation keys (used for
    sorts, sub-selections, range-selection, and joining) within its indexes.

    There are many applications, notably for the web where MySQL is used with
    PHP, that DO support Unicode strings which are stored in a MySQL-driven
    database. If you know the limitations of MySQL before designing the
    database, then it's not complicate to design the client-side code so that it
    will make SQL queries without requiring Unicode-support in the MySQL
    database, when you can provide this support directly with the client-side
    libraries (for example in PHP, there are tons of modules that do accept
    Unicode strings, perform encoding conversions, ...).

    Note that PHP itself is quite similar to MySQL: it also has no native
    support of Unicode for its strings, which are just vectors of 8-bit units.
    But despite this limitation (where a "char" or "string" in PHP have not the
    same meaning as in Unicode), the supported data type is enough to allow
    handling UTF-8 strings correctly. There are constraints to respect in your
    code to avoid breaking Unicode-compatibility: the language will not prevent
    you to break those rules (notably not breaking a UTF-8 encoded character by
    splitting it in the middle of a sequence), but it is perfectly possible.

    The situation is almost identical in MOST other languages:
    * JavaScript/ECMA-Script for example uses characters and strings that are
    effectively vectors of 16-bit code units, and not Unicode characters (i.e.
    full code points); the difference is that code points above U+FFFF must be
    represented by two "characters" in JavaScript, each one corresponding to
    "surrogates", and the language does not enforce the rule of not breaking a
    character in the middle of a surrogate pair.
    * C/C++ may use 8-bit "char" or 8/16/32-bit "wchar_t"; you have to study the
    platform specification of the supported data types to find which format of
    code units are supported. Then you need to use appropriate code or libraries
    to use vectors of code units according to the expected Unicode semantics and
    requirement). The language itself will not enforce the Unicode rules, but it
    perfectly has all what it needed to support it

    In fact you could as well program in assembly language with the same
    capabilities and lacks of native support: it's up to you to add this support
    or to use libraries that will help you in this task.

    Note that if your MySQL instance only supports Unicode characters encoded
    with up to 3 bytes per characters, it is enough to support UTF-32 (after
    dropping the high byte which is always null and not stored, but will be
    regenerated implicitly at retrieval time). What this means is that it will
    use fixed-length encoding for fixed-length Unicode strings, so the storage
    space can be easily sized to a safe maximum. It will however probably not be
    very efficient in terms of I/O because most text will then be stored with 1
    or possibly 2 null bytes per characters, so there will be less rows per disk

    Really, for compact database storage, a SQL engine should propose some
    standard compaction algorithm (SCSU being a good candidate); what it needs
    to enforce is the effective Unicode string length, but not the internal
    storage length, but it must offer a way for database designers to estimate
    the average size and maximum size for the storage of tables. The average
    size will be data-dependant, but not the maximum which is very strict and
    can be enforced by the SQL engine as a simple measure in addition to the
    maximum Unicode string length. The effective internal representation used in
    the database storage should not affect the semantics of the strings you are
    storing and retrieving, if your database says it supports Unicode.

    But no database should say it supports Unicode if it cannot accept strings
    containing EVERY character of the 17 planes (including NULL and other
    control characters, but possibly excluding non-characters whose exclusion
    should better be enforced by the database engine).

    Full support of Unicode is illusory: new algorithms get developed everyday,
    as well as new characters and new properties. A SQL engine really conforms
    only to a set of Unicode algorithms specified in a precise version, which
    should be documented. The behaviour of the database with Unicode characters
    that were still not standardized in that Unicode version is not specified:

    Some database will adopt a "safe" behaviour and will reject strings
    containing them, others will accept them using default properties, but this
    may affect future evolutions when the engine gets updated to support a new
    version of Unicode: indices may need to be rebuilt for correct sorting or
    range sub-selections according to newer UCA collation rules including the
    newer characters. But this reordering of indices and queries may affect
    applications and that's why some database engines offer you the choice,
    either in the table store format (where the strict enforcement to the known
    standardized Unicode version may be encoded), or in the client connection
    handshakes, or in the stored user properties (that determine the client
    compatibility, but this may produce inconsistent results if there are
    different user profiles for the same database).

    I personally think that the best place for such Unicode version enforcement
    is in the application, not in the database itself, and not in the user
    profiles; but this is just an opinion, which may be wrong in other
    environments where there are multiple applications with different needs
    connecting to the same database.

    The per-user profile is for me a bad choice because it easily produces
    inconstant results (where the queries do not reflect the actual content of
    the database, which may produce sometimes inconsistent results, like
    rejecting a row creation due to a unique key constraint, despite that a row
    with that key could not be retrieved by a simple SELECT, even within the
    same SQL transaction). However, an application may still be built safely
    with such per-user profiles (regarding handshaked Unicode versions), if it
    is prepared to handle row creation exceptions, and do not assume that a ROW
    NOT FOUND indication (or COUNT(*)==0) after a simple SELECT (or a
    range-SELECT) means that the row will be creatable in the same transaction.

    > -----Message d'origine-----
    > De : [] De la
    > part de Magda Danish (Unicode)
    > Envoyé : mercredi 21 février 2007 23:34
    > À :
    > Cc :
    > Objet : FW: Subj: MySQl and Unicode support
    > Sinead --
    > I am forwarding your question to the Unicode mailing list.
    > Unicode subscribers --
    > Does anyone have an answer to Sinead's email?
    > -----Original Message-----
    > Date/Time: Tue Feb 20 07:22:21 CST 2007
    > Contact:
    > Name: Sinead Martin
    > Report Type: Other Question, Problem, or Feedback Opt Subject: MySQl and
    > Unicode support
    > Hi there,
    > I notice that MySQl is not listed on your website as a database that
    > supports Unicode.
    > Would you recommend mySQL as a database that needs to support 10 languages
    > incuding Traditional / simplified Chinese and Japanese?
    > I wondered whether the fact that MySQL support only 3 bytes instead of 4
    > has any significance?
    > Thanks in advance,
    > Sinead Martin

    This archive was generated by hypermail 2.1.5 : Thu Feb 22 2007 - 08:01:56 CST