Movatterモバイル変換

Table 12.15 Cast Functions and Operators

• Cast Function and Operator Descriptions

• Character Set Conversions

• Character Set Conversions for String Comparisons

• Other Uses for Cast Operations

CONVERT() with aUSING clause converts data between character sets:

CONVERT(expr USINGtranscoding_name)

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

Examples:

SELECT CONVERT('test' USING utf8);SELECT CONVERT(_latin1'Müller' USING utf8);INSERT INTO utf8_table (utf8_column)    SELECT CONVERT(latin1_column USING utf8) FROM latin1_table;

To convert strings between character sets, you can also useCONVERT(expr,type) syntax (withoutUSING), orCAST(expr AStype), which is equivalent:

CONVERT(string, CHAR[(N)] CHARACTER SETcharset_name)CAST(string AS CHAR[(N)] CHARACTER SETcharset_name)

Examples:

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

If you specifyCHARACTER SETcharset_name as just shown, the character set and collation of the result arecharset_name and the default collation ofcharset_name. If you omitCHARACTER SETcharset_name, the character set and collation of the result are defined by thecharacter_set_connection andcollation_connection system variables that determine the default connection character set and collation (seeSection 10.4, “Connection Character Sets and Collations”).

ACOLLATE clause is not permitted within aCONVERT() orCAST() call, but you can apply it to the function result. For example, these are legal:

SELECT CONVERT('test' USING utf8) COLLATE utf8_bin;SELECT CONVERT('test', CHAR CHARACTER SET utf8) COLLATE utf8_bin;SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

But these are illegal:

SELECT CONVERT('test' USING utf8 COLLATE utf8_bin);SELECT CONVERT('test', CHAR CHARACTER SET utf8 COLLATE utf8_bin);SELECT CAST('test' AS CHAR CHARACTER SET utf8 COLLATE utf8_bin);

For string literals, another way to specify the character set is to use a character set introducer._latin1 and_latin2 in the preceding example are instances of introducers. Unlike conversion functions such asCAST(), orCONVERT(), which convert a string from one character set to another, an introducer designates a string literal as having a particular character set, with no conversion involved. For more information, seeSection 10.3.8, “Character Set Introducers”.

Normally, you cannot compare aBLOB value or other binary string in case-insensitive fashion because binary strings use thebinary character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, first use theCONVERT() orCAST() function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the conversion result collation is not case-sensitive, aLIKE operation is not case-sensitive. That is true for the following operation because the defaultlatin1 collation (latin1_swedish_ci) is not case-sensitive:

SELECT 'A' LIKE CONVERT(blob_col USING latin1)  FROMtbl_name;

To specify a particular collation for the converted string, use aCOLLATE clause following theCONVERT() call:

SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci  FROMtbl_name;

To use a different character set, substitute its name forlatin1 in the preceding statements (and similarly to use a different collation).

CONVERT() andCAST() can be used more generally for comparing strings represented in different character sets. For example, a comparison of these strings results in an error because they have different character sets:

mysql> SET @s1 = _latin1 'abc', @s2 = _latin2 'abc';mysql> SELECT @s1 = @s2;ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)and (latin2_general_ci,IMPLICIT) for operation '='

Converting one of the strings to a character set compatible with the other enables the comparison to occur without error:

mysql> SELECT @s1 = CONVERT(@s2 USING latin1);+---------------------------------+| @s1 = CONVERT(@s2 USING latin1) |+---------------------------------+|                               1 |+---------------------------------+

Character set conversion is also useful preceding lettercase conversion of binary strings.LOWER() andUPPER() are ineffective when applied directly to binary strings because the concept of lettercase does not apply. To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string:

mysql> SET @str = BINARY 'New York';mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));+-------------+-----------------------------------+| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |+-------------+-----------------------------------+| New York    | new york                          |+-------------+-----------------------------------+

Be aware that if you applyBINARY,CAST(), orCONVERT() to an indexed column, MySQL may not be able to use the index efficiently.

The cast functions are useful for creating a column with a specific type in aCREATE TABLE ... SELECT statement:

mysql> CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE) AS c1;mysql> SHOW CREATE TABLE new_table\G*************************** 1. row ***************************       Table: new_tableCreate Table: CREATE TABLE `new_table` (  `c1` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

The cast functions are useful for sortingENUM columns in lexical order. Normally, sorting ofENUM columns occurs using the internal numeric values. Casting the values toCHAR results in a lexical sort:

SELECTenum_col FROMtbl_name ORDER BY CAST(enum_col AS CHAR);

CAST() also changes the result if you use it as part of a more complex expression such asCONCAT('Date: ',CAST(NOW() AS DATE)).

For temporal values, there is little need to useCAST() to extract data in different formats. Instead, use a function such asEXTRACT(),DATE_FORMAT(), orTIME_FORMAT(). SeeSection 12.7, “Date and Time Functions”.

To cast a string to a number, it normally suffices to use the string value in numeric context:

mysql> SELECT 1+'1';       -> 2

That is also true for hexadecimal and bit literals, which are binary strings by default:

mysql> SELECT X'41', X'41'+0;        -> 'A', 65mysql> SELECT b'1100001', b'1100001'+0;        -> 'a', 97

A string used in an arithmetic operation is converted to a floating-point number during expression evaluation.

A number used in string context is converted to a string:

mysql> SELECT CONCAT('hello you ',2);        -> 'hello you 2'

For information about implicit conversion of numbers to strings, seeSection 12.3, “Type Conversion in Expression Evaluation”.

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as+ or-) where one of the operands is an unsigned integer, the result is unsigned by default (seeSection 12.6.1, “Arithmetic Operators”). To override this, use theSIGNED orUNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT 1 - 2;        -> -1mysql> SELECT CAST(1 - 2 AS UNSIGNED);        -> 18446744073709551615mysql> SELECT CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED);        -> -1

If either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context,DECIMAL column values are regarded as floating-point values.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;        -> -1.0

The SQL mode affects the result of conversion operations (seeSection 5.1.10, “Server SQL Modes”). Examples: