MySQL Conversion Functions and operators

Table 12.14 Conversion Functions (cast function)

name	Description
BINARY	Converts a string to a binary string
CAST()	Convert a value to a specific type
CONVERT()	Convert a value to a specific type

• BINARY

BINARYThe string is converted to a binary string by the operator immediately thereafter. A character that is used primarily to force comparisons in bytes (byte by byte), rather than characters. This makes string comparisons case-sensitive, regardless of whether the original column definition is BINARY or BLOB . BINARYalso sensitive to whitespace at the end of a string.

SELECT ‘a‘ = ‘A‘;

1

SELECT BINARY ‘a‘ = ‘A‘;

0

SELECT ‘a‘ = ‘a    ‘;

1

SELECT BINARY ‘a‘ = ‘a   ‘;

0

In the above comparison, the BINARY effect is the entire comparison operation; Regardless of which operand is placed in front, the result is the same.

There is another situation where the BINARY equals sign does not work:

 SELECT ‘a‘ = BINARY ‘a    ‘;

1

BINARY stris actually CAST(str AS BINARY) the abbreviation.

Sometimes, if you convert an indexed column to a BINARY , MySQL may not use the index.

• CAST(expr AS type)

CAST()The function receives an expression of any type and returns the corresponding result value according to the specified type, CONVERT() similar to the syntax form used, so please refer to the following CONVERT() function.

• CONVERT(expr,type),CONVERT(expr USING transcoding_name)

CONVERT()and CAST() functions both receive an expression of any type and return the corresponding result value based on the specified type.

CAST()and CONVERT(... USING ...) both are standard SQL syntax. Instead of USING CONVERT() ODBC, the syntax is not.

USINGCONVERT()data conversion between different character sets in the same way. In MySQL, the name of the transcoding is the same as the corresponding character set name. For example, the following statement converts the string " abc from the default character set to a utf8 character set:

SELECT CONVERT(‘abc‘ USING utf8);

The result of the conversion function can be the following types:

• BINARY[(N)]

• CHAR[(N)]

• DATE

• DATETIME

• DECIMAL[(M[,D])]

• SIGNED [INTEGER]

• TIME

• UNSIGNED [INTEGER]

BINARYThe resulting string data type is in binary form. For more details, refer to section 11.4, 2, "The BINARY and VARBINARY Types". If an optional parameter Nis passed in, BINARY(N) the conversion result is a maximum of n bytes. If the result is less than n bytes, it is 0x00 populated with.

CHAR(N)The result is a maximum of n characters.

In general, it is not possible to compare values or other binary strings in a case-insensitive manner, since the binary BLOB string is not a character set and therefore has no concept of letters. If you are not case-sensitive, you can use a CONVERT() string that converts the value to a non-binary to compare. The results of the comparison are based on the character set ordering. For example, assuming that the character set is case-insensitive, the operation is case-insensitive like :

SELECT ‘A‘ LIKE CONVERT(blob_col USING latin1) FROM tbl_name;

To use a different character set, just replace the inside latin1 . Specifies a specific collation for the converted string, which can be followed by a CONVERT() clause following a function call COLLATE , as described in section 10.1.9.2, "CONVERT () and CAST ()". For example, use latin1_german1_ci sort:

SELECT ‘A‘ LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci  FROM tbl_name;

CONVERT()Comparisons can be made among different character sets.

LOWER()and UPPER() for binary strings is invalid (including BINARY , VARBINARY , BLOB ). To convert a case, you need to first convert the string to a non-binary form:

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                          |+-------------+-----------------------------------+

Conversion functions can be used to create columns of a specific type, such as in a CREATE TABLE ... SELECT statement:

CREATE TABLE new_table SELECT CAST(‘2000-01-01‘ AS DATE);

Conversion functions can also be used to sort columns by defined words ENUM . Normally, the enumeration columns are sorted according to the internal numeric representation. Results by alphabetical CHAR type:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY)and BINARY str equivalence. Treats an CAST(expr AS CHAR) expression as the default character set.

CAST()May change the results of complex expressions, for example CONCAT(‘Date: ‘,CAST(NOW() AS DATE)) .

This should not be used CAST() to extract data in different formats, but instead use string functions such as LEFT() or EXTRACT() . For more information, refer to section 12.7, "Date and Time Functions".

To convert a string to a numeric value for processing, it is generally not necessary to handle it manually, and MySQL will implicitly type-convert it:

SELECT 1+‘1‘;

2

In arithmetic operations, a string is converted to a floating-point number during the evaluation phase of an expression.

If you need to treat numbers as strings, MySQL will also automatically convert:

SELECT CONCAT(‘hello you ‘,2);

' Hello you 2 '

In the previous version of MySQL 5.6.4, CAST() TIMESTAMP when processing, if you do not select a value from a specific table, MySQL 5.6 treats the value first as a string before performing the conversion. This can cause truncation when converted to a number, as follows:

mysql> SELECT CAST(TIMESTAMP ‘2014-09-08 18:07:54‘ AS SIGNED);+-------------------------------------------------+| CAST(TIMESTAMP ‘2014-09-08 18:07:54‘ AS SIGNED) |+-------------------------------------------------+|                                            2014 |+-------------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+---------+------+----------------------------------------------------------+| Level   | Code | Message                                                  |+---------+------+----------------------------------------------------------+| Warning | 1292 | Truncated incorrect INTEGER value: ‘2014-09-08 18:07:54‘ |+---------+------+----------------------------------------------------------+1 row in set (0.00 sec)

However, if you select rows from a table, this is not the case, as follows:

USE test;

Database changed

CREATE TABLE c_test (col TIMESTAMP);

Query OK, 0 rows affected (0.07 sec)

INSERT INTO c_test VALUES (‘2014-09-08 18:07:54‘);

Query OK, 1 row affected (0.05 sec)

SELECT col, CAST(col AS UNSIGNED) AS c_col FROM c_test;

>
+ ——————— + —————-+
| Col | C_col |
+ ——————— + —————-+
| 2014-09-08 18:07:54 | 20140908180754 |
+ ——————— + —————-+
1 row in Set (0.00 sec)

After MySQL 5.6.4, this issue was fixed as follows:

SELECT CAST(TIMESTAMP ‘2014-09-08 18:07:54‘ AS SIGNED);

>
+ ————————————————-+
| CAST (TIMESTAMP ' 2014-09-08 18:05:07 ' as signed) |
+ ————————————————-+
| 20140908180754 |
+ ————————————————-+
1 row in Set (0.00 sec)

For an implicit conversion of numbers and strings, see section 12.2, "Type Conversion in Expression Evaluation".

MySQL supports signed and unsigned 64-bit arithmetic operations. If you are using a numeric operator (such as add + or subtract - ), one of the operands is an unsigned integer, which by default results in an unsigned number (see 12.6.1 arithmetic operator). Conversions can be SIGNED done by specifying or UNSIGNED .

SELECT CAST(1-2 AS UNSIGNED)

18446744073709551615

SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);

-1

If the operand is a floating-point value, the result is a floating-point value that is not affected by the preceding rule. (In this case, the DECIMAL column is treated as a floating-point value.) )

SELECT CAST(1 AS UNSIGNED) - 2.0;

-1.0

SQL mode affects the result of the conversion operation. For example:

• If a date string that converts a value of 0 is a date, convert () and CAST () will return and NULL generate a warning in no_zero_date mode.

• For the subtraction of integers, if mode is enabled NO_UNSIGNED_SUBTRACTION , the subtraction result is a signed number, even if one is an unsigned number.

For more information, see Section 5.1.7, "Server SQL Modes".

How to convert a BLOB to a char UTF8

First, see what encoded byte is stored in the BLOB. Is it a utf8 different character set?

The correct character set must be specified here, corresponding to the encoding stored in the BLOB. If the UTF8 encoding is stored in it, then this is the case. If you are storing a latin1 character set, you need to set it to latin1 .

Original link: Cast Functions and Operators

Translation Date: October 03, 2015

Translators: Anchor Http://blog.csdn.net/renfufei

MySQL Conversion Functions and operators