- Character Sets and Collations in General
- Character Sets and Collations in MySQL
- Determining the Default Character Set and Collation
- Operations Affected by Character Set Support
- Unicode Support
- UTF8 for Metadata
- Compatibility with Other DBMSs
- New Character Set Configuration File Format
- National Character Set
- Upgrading Character Sets from MySQL 4.0
- Character Sets and Collations That MySQL Supports
This chapter is from the book
This chapter is from the book
This chapter is from the book
3.4 Operations Affected by Character Set Support
This section describes operations that take character set information into account as of MySQL 4.1.
3.4.1 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 string input and return a string result as output, the output's character set and collation are the same as those of the principal input value. 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(), and 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, the "aggregation rules" of standard SQL apply:
If an explicit COLLATE X occurs, then use X
If an 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 CASE, UNION, ||, CONCAT(), ELT(), GREATEST(), IF(), and LEAST().
For operations that convert to character data, the character set and collation of the strings that result from the operations are defined by the character_set_connection and collation_ connection system variables. This applies for CAST(), CHAR(), CONV(), FORMAT(), HEX(), and SPACE().
3.4.2 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'Müller' USING utf8); INSERT INTO utf8table (utf8column) SELECT CONVERT(latin1field USING utf8) FROM latin1table;
CONVERT(... USING ...) is implemented according to the standard SQL specification.
3.4.3 CAST()
You may also use CAST() to convert a string to a different character set. The syntax is:
CAST(character_string AS character_data_type CHARACTER SET charset_name)
Example:
SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);
If you use CAST() without specifying CHARACTER SET, the resulting character set and collation are defined by the character_set_connection and collation_connection system variables. If you use CAST() with CHARACTER SET X, then the resulting character set and collation are X and the default collation of X.
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;
3.4.4 SHOW Statements
Several SHOW statements are new or modified in MySQL 4.1 to provide additional character set information. SHOW CHARACTER SET, SHOW COLLATION, and SHOW CREATE DATABASE are new. SHOW CREATE TABLE and SHOW COLUMNS are modified.
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> SHOW CHARACTER SET LIKE 'latin%'; +---------+-----------------------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+-----------------------------+-------------------+--------+ | 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 | +---------+-----------------------------+-------------------+--------+
See Section 6.5.3.2, "SHOW CHARACTER SET Syntax."
The output from SHOW COLLATION includes all available character sets. It takes an optional LIKE clause that indicates which collation names to match. For example:
mysql> SHOW COLLATION LIKE 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | 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 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +-------------------+---------+----+---------+----------+---------+
See Section 6.5.3.3, "SHOW COLLATION Syntax."
SHOW CREATE DATABASE displays the CREATE DATABASE statement that will create a given database. The result includes all database options. DEFAULT CHARACTER SET and COLLATE are supported. All database options are stored in a text file named db.opt that can be found in the database directory.
mysql> SHOW CREATE DATABASE a\G
*************************** 1. row ***************************
Database: a
Create Database: CREATE DATABASE ´a´
/*!40100 DEFAULT CHARACTER SET macce */
See Section 6.5.3.5, "SHOW CREATE DATABASE Syntax."
SHOW CREATE TABLE is similar, but displays the CREATE TABLE statement to create a given table. The column definitions now indicate any character set specifications, and the table options include character set information.
See Section 6.5.3.6, "SHOW CREATE TABLE Syntax."
The SHOW COLUMNS statement displays the collations of a table's columns when invoked as SHOW FULL COLUMNS. Columns with CHAR, VARCHAR, or TEXT data types have non-NULL collations. Numeric and other non-character types have NULL collations. For example:
mysql> SHOW FULL COLUMNS FROM t; +-------+---------+------------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------+---------+------------+------+-----+---------+-------+ | a | char(1) | latin1_bin | YES | | NULL | | | b | int(11) | NULL | YES | | NULL | | +-------+---------+------------+------+-----+---------+-------+
The character set is not part of the display. (The character set name is implied by the collation name.)
See Section 6.5.3.4, "SHOW COLUMNS Syntax."