Difference between CAST and CONVERT in MySQL

Navigation
12

To solve a problem I ended up with two solutions where in an era the CAST() to convert a number to text, in another era the CONVERT() for the same effect.

The two functions, the same practical effect: 

SELECT (CAST(10 AS DECIMAL)) + 10; # resulta em: 20

SELECT (CONVERT(10,DECIMAL)) + 10; # resulta em: 20

A simple example where CAST() and CONVERT() become useful: 

SELECT CONCAT(10, 10); # resulta em: 31303130

But if we want to join the two values as if they were text: 

SELECT CONCAT(CAST(10 AS CHAR), 10);  # resulta em: 1010

SELECT CONCAT(CONVERT(10,CHAR), 10);  # resulta em: 1010

However, the question remains why we have two functions that do exactly the same thing.

Question

In MySQL what is the difference between CAST() and CONVERT() ?

    
asked by anonymous 05.11.2014 / 21:13

1 answer

9

Given the examples you cited, there is no difference other than syntax . The two functions are described in the same page in the MySql documentation .

The explanation of the documentation for the existence of both is that CONVERT is specified by default ODBC while CAST is specified by default ANSI SQL . There is no evidence of performance differences or anything like that.

When used with a different syntax, CONVERT can convert a character set into another ( CONVERT... USING... ). The CAST command does not have this option.

Reflecting:

What I find interesting is that a database engine does not have to support ODBC syntax to be accessed via ODBC.

See, for example, the ODBC documentation for the CONVERT function itself . It shows the following example:

If an application specifies the following command: 

SELECT EMPNO FROM EMPLOYEES WHERE {fn CONVERT(EMPNO,SQL_CHAR)} LIKE '1%'

An Oracle driver translates to: 

SELECT EMPNO FROM EMPLOYEES WHERE to_char(EMPNO) LIKE '1%'

Now, if the driver needs to translate the command, CONVERT would not need to be supported by the MySql engine; only ODBC Driver implementations for MySql are required to handle this function.

My conclusion to the MySql engine supporting both functions is that CAST is limited to the ANSI SQL standard, while CONVERT can take variations for the benefit of the MySql developer. For now, your only variation seems to be the USING syntax to set the conversion of a character set to another (even though this syntax is provided in obscure SQL-99).

To support this conclusion, we can also study the CONVERT function of MS SQL Server and Oracle. In MS SQL Server , CONVERT may receive a third parameter to specify a format or date style , and can be used for the same purpose as CAST. And in Oracle CONVERT is exclusively for converting a character set in another (separating the parameters with a comma without the using in the syntax.

In other words, in addition to the common and multicompatible CAST , DBMSs also support variations of CONVERT to grace their unique developers.

>     
06.11.2014 / 16:43
Use ResultSet without knowing the column type What is the advantage of using a database for reading and another for writing?