Difference between CAST and CONVERT in MySQL

Question:

To solve a problem I ended up coming across two solutions where in one theCAST() function was used to convert a number into text, in the other the CONVERT() function was used 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() come in handy:

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 as to why we have two functions that do exactly the same thing.

Question

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

Answer:

Considering the examples you cited, there is no difference beyond the syntax . The two functions are even described on the same page in the MySql documentation .

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

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

Reflecting:

What I find interesting is that a database engine does not need to support ODBC syntax in order 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 it 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 for the MySql engine supporting both functions is that CAST is limited to the ANSI SQL standard, while CONVERT can assume variations for the benefit of the MySql developer. For now, its only variation seems to be the USING syntax for defining the conversion from one character set to another (although this syntax is provided for in the obscure SQL-99).

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

In other words, in addition to the common and multi-compatible CAST , DBMSs also support CONVERT variations to grace their exclusive developers.

Scroll to Top