Featured Database Articles

Converting Between Data Types in MySQL

There are a lot of occasions for converting one data type to another. Examples include porting data from one database vendor to another, changing the data type of a column, and temporarily switching between data types for evaluation. All of these can be achieved using MySQL’s native CONVERT() and CAST() functions. By the time you’re done reading this article, you’ll know exactly how to use both.

Implicit Type Conversion

Mixing and matching data types within the same operation will inevitably lead to implicit conversion, whereby MySQL will convert one of the operands to make it the same as the other(s) – converting apples to apples as it were. Most commonly the expression combines chars or varchars with another data type, such as an int or double.

Letting MySQL handle the conversion is not such a bad thing when you know what the data values will be, but sometimes, it may lead to some unexpected results! For instance, adding “1” to 1 is not an issue because the number “1” will simply be converted to a numeric type. On the other hand, something like “r” + 1 becomes less of a sure thing. (For the record, “r” becomes 0, so that the result will evaluate to 1.) If we issue the SHOW WARNINGS command, we get the following information:

So far, all of these conversions seem logical and harmless enough, but there are many instances where implicit conversion can lead to some very erratic behavior. Especially problematic types include timestamps, dates, as well as mixed numeric types, such as integers and floats. There are even times where MySQL must convert multiple values but won’t know which to convert too. In those cases, you’ll get an error if you’re lucky. Otherwise, it just might convert to the wrong type.

Explicit Conversion

The reasons outlined above are why you should handle all of your conversions yourself rather than let MySQL do the dirty work for you. That’s where the CONVERT() and CAST() functions come in. Both take an expression of any type and produce a result of one of the following data types:

BINARY[(N)] (where (N) is the length of the binary data in bytes)

CHAR[(N)]

DATE

DATETIME

TIME

DECIMAL[(M[,D])] (where M is the maximum number of digits [the precision] and D is the number of digits to the right of the decimal point [the scale])

INTEGER [SIGNED or UNSIGNED]

While both the functions perform the same task, there are a few differences between them to be aware of. Each function is better suited for some conversions, thus yielding better performance. The CONVERT function tends to be better for converting date and time values, fractional numbers, and monetary values. On the other hand, the CAST function is more adept at converting decimals and numeric values, as the function can retain the decimals from the original expressions. Moreover, CAST() utilizes the ANSI standard and is more portable compared to CONVERT function.

The CAST() Function

The syntax of the CAST() function is quite simple:

CAST(expression AS TYPE)

Before performing the following calculation, MySQL would normally convert the `2` string into an integer. We can explicitly convert the `2` string into an integer ourselves using the CAST() function:

mysql> SELECT CONCAT('MySQL is number ',CAST(1 AS CHAR));
+---------------------------------------------+
| CONCAT('MySQL is number ',CAST(1 AS CHAR)) |
+---------------------------------------------+
| MySQL is number 1 |
+---------------------------------------------+

The CONVERT() Function

The CONVERT() function syntax is similar to CAST() but the expression and result type are supplied via two arguments:

Using the CAST() and CONVERT() Functions in Queries

The CAST() and CONVERT() functions are often used to return a value with a specified type for comparison in the WHERE, JOIN, and HAVING clauses. One such case involves stored procedures that accept date strings. Here’s one that fetches invoices between a given date range:

Unfortunately, the above code is doomed to fail, as chars are unsuitable for date range queries. Therefore, we need to apply the Convert() function on the date strings to transform them into proper date objects:

Converting Between Character Sets

One very particular use of both the Cast() the Convert() functions is to convert from one character set to another. The default character set in MySQL is latin1. If you want to store characters from multiple languages in a single column, you can use Unicode character sets, which is utf8 or ucs2.

The syntax for Cast() is:

CAST(character_string AS character_data_type CHARACTER SET charset_name)

Here’s an example:

mysql> SELECT CAST(_latin1'This is a test' AS CHAR CHARACTER SET utf8);

+----------------------------------------------------------+
| CAST(_latin1'This is a test' AS CHAR CHARACTER SET utf8) |
+----------------------------------------------------------+
| This is a test |
+----------------------------------------------------------+

The ''_latin1'' is the charset name (also known as an introducer). It tells the parser that the string that follows is in character set ''X''. The introducer does not cause any conversion, merely helps MySQL know what character set it’s converting from. Note that you may include a space between it and the value if you like.

The syntax for convert is:

CONVERT(expr USING transcoding_name)

As the following example shows, Convert() is a great way to convert data that was encoded in a given character set when written to disk into another:

Conclusion

You can never go wrong by taking charge of data type conversion using CAST and CONVERT, rather than letting MySQL handle it for you. Make CAST your go-to function for conversion and CONVERT for other tasks that are better suited for it.