Operations Affected by Character Set Support
============================================

This section describes operations that take character set information
into account now.

Result Strings
--------------

MySQL has many operators and functions that return a string.  This
section answers the question: What is the character set and collation
of such a string?

For simple functions that take a string input and return a string
result as output, the output's character set and collation are the same
as the principal input's. For example, `UPPER(X)' returns a string
whose character string and collation are the same as that of `X'.  The
same applies for: `INSTR()', `LCASE()', `LOWER()', `LTRIM()', `MID()',
`REPEAT()', `REPLACE()', `REVERSE()', `RIGHT()', `RPAD()', `RTRIM()',
`SOUNDEX()', `SUBSTRING()', `TRIM()', `UCASE()', `UPPER()'.  (Also
note: the `REPLACE()' function, unlike all other functions, ignores the
collation of the string input and performs a case-insensitive
comparison every time.)

For operations that combine multiple string inputs and return a single
string output, SQL-99's "aggregation rules" apply.  They are:

   * If explicit `COLLATE X' occurs, then use `X'

   * If explicit `COLLATE X' and `COLLATE Y' occur, then error

   * Otherwise, if all collations are `X', then use `X'

   * Otherwise, the result has no collation


For example, with `CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END',
the resultant collation is `X'. The same applies for: `CONCAT()',
`GREATEST()', `IF()', `LEAST()', `CASE', `UNION', `||', `ELT()'.

For operations that convert to character data, the result string's
character set and collation are in the connection/literals character
set and have the connection/literals collation.  This applies for:
`CHAR()', `CAST()', `CONV()', `FORMAT()'.  `HEX()', `SPACE()'.

`CONVERT()'
-----------

`CONVERT()' provides a way to convert data between different character
sets. The syntax is:

     CONVERT(expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character
set names.

Examples:

     SELECT CONVERT(_latin1'Mu"ller' USING utf8);
     INSERT INTO utf8table (utf8column)
        SELECT CONVERT(latin1field USING utf8) FROM latin1table;

`CONVERT(... USING ...)' is implemented according to the SQL-99
specification.

`CAST()'
--------

You may also use `CAST()' to convert a string to a different character
set. The new format is:

     CAST ( character_string AS character_data_type
         CHARACTER SET character_set_name )

Example:

     SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

You may not use a `COLLATE' clause inside a `CAST()', but you may use
it outside, that is, `CAST(... COLLATE ...)' is illegal but `CAST(...)
COLLATE ...' is legal.

Example:

     SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

 SET X', then the resulting character set is `X' and the
resulting collation is `X''s default collation.

`SHOW CHARACTER SET'
--------------------

The `SHOW CHARACTER SET' command shows all available character sets.
It takes an optional `LIKE' clause that indicates which character set
names to match.

For example:

     mysql>
     | latin1  | ISO 8859-1 West European    | latin1_swedish_ci |      1 |
     | latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
     | latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
     | latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
     +---------+-----------------------------+-------------------+--------+
     4 rows in set (0.00 sec)

Notes about the preceding listing:

   * The `Maxlen' column shows the maximum number of bytes used to
     store one character.


`SHOW COLLATION'
----------------

The output from `SHOW COLLATION' includes all available character sets.
It takes an optional `LIKE' clause that indicates which collation names
to match.

     mysql>
     | latin1_german1_ci | latin1  |  5 |         |          |       0 |
     | latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |
     | latin1_danish_ci  | latin1  | 15 |         |          |       0 |
     | latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
     | latin1_bin        | latin1  | 47 |         | Yes      |       0 |
     | latin1_general_ci | latin1  | 48 |         |          |       0 |
     | latin1_general_cs | latin1  | 49 |         |          |       0 |
     +-------------------+---------+----+---------+----------+---------+
     7 rows in set (0.00 sec)

 amount of
memory required to sort strings expressed in the character set.

`SHOW CREATE DATABASE'
----------------------

The following query shows a `CREATE DATABASE' statement that will
create the given database. The result includes all database options.
`DEFAULT CHARACTER SET' and `COLLATE' are supported. All database
options are stored in a text file that can be found in the database
directory.

     mysql>
     | a        | CREATE DATABASE `a` /*!40100 DEFAULT CHARACTER SET macce
     COLLATE macce_ci_ai */ |
     +----------+---------------------------------------------------------------------------------+
     1 row in set (0.00 sec)

`SHOW FULL COLUMNS'
-------------------

The `SHOW COLUMNS' statement now displays the collations of a table's
columns, when invoked as `SHOW FULL COLUMNS'.  Columns with `CHAR',
`VARCHAR', or `TEXT' datatypes have non-`NULL' collations. Numeric and
other non-character types have `NULL' collations. For example:

     mysql> SHOW FULL COLUMNS FROM a;
     +-------+---------+-------------------+------+-----+---------+-------+
     | Field | Type    | Collation         | Null | Key | Default | Extra |
     +-------+---------+-------------------+------+-----+---------+-------+
     | a     | char(1) | latin1_swedish_ci | YES  |     | NULL    |       |
     | b     | int(11) | NULL              | YES  |     | NULL    |       |
     +-------+---------+-------------------+------+-----+---------+-------+
     2 rows in set (0.02 sec)

The character set is not part of the display.

[Назад] [Содержание] [Вперед]