Data Type Conversion in MySQL 8

Jul 9, 2021 by Robert Gravelle

Any time that you categorize data into different types, the need to convert from one data type to another is inevitable. Off the top of my head, a common use case is to process variables that were passed in from a web form via a query parameter or POST request body. Serializing data in order to send it across the network tends to coerce all variables into strings. As such, they often need to be converted into a more appropriate data type, such as a number, date, or what-have-you.

In relational databases, reasons for converting one data type to another include porting data from one database type to another, changing the data type of a column, or temporarily switching between data types for evaluation. In MySQL, we can convert between data types using the CAST() and CONVERT() functions. In today’s blog, we’ll learn how to employ both functions using examples to illustrate their usage.

Both CAST() and CONVERT() can change data types in MySQL. Since the two are so similar, many SQL newbies (and some more experienced users!) wonder what the difference is. The main difference is that CONVERT() can also convert the character set of data into another character set. CAST() cannot be used to change character sets. Hence, CAST() should be your goto conversion function, unless you need to convert a character set.

MySQL CAST() accepts two inputs:

• the data to be typecasted
• the data type (decimal, char, etc.) to which you want to convert this data. You can cast data into BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED data types.

Here’s the syntax:

CAST(data as data_type)

An Almost Real-life Example

One useful application of the CAST() function is to make a very large data type less unwieldy (more wieldy?). The following query returns information about a particular film in the MySQL Sakila Sample Database. One of the columns – description – is a text field. That means that it can store a huge amount of text! We can use CAST() to truncate the description to 100 characters, so that we don’t get a whole book about the movie:

Speaking of the Sakila Sample Database, did you know that it’s named after MySQL’s dolphin mascot? It was chosen from a huge list of names suggested by users in a “Name the Dolphin” contest. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Eswatini (formerly Swaziland), Africa.

The CONVERT() function’s syntax is similar to CAST(), but the expression and result type are supplied in a slightly different format. One way is to supply two separate arguments:

CONVERT(expr, data_type)

Other than that, the data_type parameter can be any of the same types that are supported by the CAST() function.

An Not Quite Real-life Example

Since the major difference between CAST() and CONVERT() is that the latter can the character set of a column into a different one, let’s show that in action.

The first thing to be aware of is that the syntax is a little different for converting character sets. In that case, we need to add the USING keyword between the expression and character set:

CONVERT(expr USING charset);

In Navicat for MySQL (or Navicat Premium), we can see a table’s character set and collation on the Info Pane:

With that in mind, we could apply the CONVERT() function to the previous query to convert the description field from UTF-8 to Latin1. In case you’re curious, the difference between the two is that, in latin1, each character is exactly one byte long, while, in utf8, a character can consist of more than one byte. Consequently utf8 has more characters than latin1. Moreover, the characters they do have in common aren’t necessarily represented by the same byte/bytesequence.

In today’s blog, we saw how to use CAST() to convert data into a different type and how to convert between character sets using CONVERT(). To reiterate, CAST() should be your goto conversion function. CONVERT() is better suited for switching between character sets.

Interested in Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes.

---

Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.