Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted January 13, 2014

Converting Between Data Types in MySQL

By Rob Gravelle

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:

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'r'   |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

In fact, MySQL will attempt to convert all non-numeric data into numeric ones whenever the expression in question utilizes arithmetic operators, which include DIV, /, -, %, MOD, + and *.

Conversely, other operations, such as concatenation, require that all data be converted into chars before the operation can be performed:

mysql> SELECT CONCAT(2,' test');
+-------------------+
| CONCAT(2,' test') |
+-------------------+
| '2 test'          |
+-------------------+

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 (2 + CAST('2' AS UNSIGNED))/2;
+--------------------------------+
| (2 + CAST('2' AS UNSIGNED))/2  |
+--------------------------------+
| 2.0000                         |
+--------------------------------+
The same goes for string data:
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:

CONVERT(expr,type)

Here are the previous two examples rewritten to utilize CONVERT ():

mysql> SELECT (2 + CONVERT('2', UNSIGNED))/2;
+----------------------------------+
| (2 + CONVERT('2', UNSIGNED))/2   |
+----------------------------------+
| 2.0000                           |
+----------------------------------+
mysql> SELECT CONCAT('MySQL is number ',CONVERT(1,CHAR));
+---------------------------------------------+
| CONCAT('MySQL is number ',CONVERT(1,CHAR))  |
+---------------------------------------------+
| MySQL is number 1                           |
+---------------------------------------------+

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:

CREATE PROCEDURE `fetch_invoices`(in_start_date char(11),
                                  in_end_date   char(11))
BEGIN
  SELECT invoice_number,
         order_date
  FROM   orders
  WHERE  order_date BETWEEN in_start_date AND in_end_date;
END

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:

SELECT invoice_number,
       order_date
FROM   orders
WHERE  order_date BETWEEN CONVERT (in_start_date, DATETIME) 
                  AND     CONVERT (in_end_date,   DATETIME);

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:

SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8_table (utf8_column)
    SELECT CONVERT(latin1_field USING utf8) FROM latin1_table;

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.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM