Data type conversion in case of MySQL

Data type conversion is an integral feature of all the programming languages, essential to ensure the correctness of the result of calculations like addition, subtraction, multiplication and even string concatenation operations. Without type convention, it will be extremely difficult to join two operands of different data types into any type of operation. It may result in errors and even the data loss. In fact, the reason behind one of the major space shuttle crashes was attributed to incorrect type conversion. Further comparing two operands of the same nature and values but of different data types would never lead to expected result.

So like most of the major programming languages, Transact SQL is also equipped with type conversion features. This is important because any minor error due to inefficient type conversion handling may lead to a catastrophe in a huge database driven information system, bringing everything to halt. There are two types of type conversions, implicit and explicit. In case of implicit type conversions, MySQL will do the type conversions by itself depending upon the nature of the operation. Explicit type conversion is performed by the programmers themselves.

Some of the examples of implicit and explicit type conversions in case of Transact SQL are as follows,

MySQL can implicitly convert string values to numbers and also numerical values to string types,

SELECT 3+'2';

Here string value ‘2’ will be converted to numerical value 2 by the MySQL, adding it to numerical value 3 and finally giving out the result of the sum, i.e., 5.

SELECT CONCAT(2,' test');

In this case MySQL converts numerical value 2 to the string value ‘2’, combines it with string value ‘test’ and gives out a concatenated string value, ‘2 test’.

Now if you want to convert a number explicitly into a string type then CAST() function is there to assist you,

SELECT 2, CAST(3 AS CHAR)

The above query will explicitly convert the numerical value 3 to string and gives out this result, 2, ‘3’

MySQL observes some stringent rules regarding types during comparison operations,

  • If in a comparison operation, one of the operands is NULL then the result is always going to be NULL. You can bypass this rule by using NULL safe equality operator, <=>.
  • If both the operands are strings then they will be compared as strings only.
  • Similarly if both the arguments are integers then the comparison will be of integer nature only.
Author: ucavik


»