Difference between CAST and CONVERT in Mysql

Question

To solve a problem I came across two solutions where a function was used CAST() to convert a number into text, the function was used in another CONVERT() for the same purpose.

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 the CAST() and the CONVERT() become useful:

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

But if we want to combine 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 doubt remains as to why we have two functions that do exactly the same.

Question

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

Answer 1

Considering the examples you quoted, there is no difference beyond the syntax. The two functions are described in the same page in Mysql documentation.

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

When used with a different syntax, CONVERT can convert a Character set in another (CONVERT... USING...). The command CAST you do 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 to support both functions is that the CAST is limited to the ANSI SQL standard, while the CONVERT can assume variations for the benefit of developer Mysql. For now, its only variation seems to be the syntax USING to define the conversion of a Character set in another (although this syntax is predicted 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 receive a third parameter to specify a date format or style, and can be used for the same purpose as CAST. And in Oracle CONVERT serves exclusively to convert a Character set in another (separating the parameters by comma without the word using in syntax.

That is, beyond the ordinary and multicompatible CAST, Sgbds still support variations of CONVERT to grace its exclusive developers.