+---------------------------------+ | 1 | +---------------------------------+ 

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 as CAST() , or CONVERT() , 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, see Section 10.3.8, “Character Set Introducers” . 

Character set conversion is also useful preceding lettercase conversion of binary strings. LOWER()  and UPPER()  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 utf8mb4)); +-------------+------------------------------------+ | LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) | +-------------+------------------------------------+ | New York | new york | +-------------+------------------------------------+ 

Be aware that if you convert an indexed column using BINARY , CAST() , or CONVERT() , MySQL may not be able to use the index efficiently. 

The cast functions are useful for creating a column with a specific type in a CREATE 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_table Create Table: CREATE TABLE ‘new_table‘ ( ‘c1‘ date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

The cast functions are useful for sorting ENUM  columns in lexical order. Normally, sorting of ENUM  columns occurs using the internal numeric values. Casting the values to CHAR  results in a lexical sort: 

SELECT enum_col  FROM tbl_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 as CONCAT(’Date: ’,CAST(NOW() AS DATE)) . 

For temporal values, there is little need to use CAST()  to extract data in different formats. Instead, use a function such as EXTRACT() , DATE_FORMAT() , or TIME_FORMAT() . See Section 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’, 65 mysql> SELECT b’1100001’, b’1100001’+0; -> ’a’, 97 

