Analysis of MySQL explicit type conversion

CAST function

In the previous article, we mentioned the CAST function, which is used to show that type conversion is performed. There are many advantages when avoiding implicit type conversion. In fact, there are still many details, which need to be sorted out.

First, let's look at the following transformation:

mysql> SELECT CAST('2017-12-14' AS DATE);
+----------------------------+
| CAST('2017-12-14' AS DATE) |
+----------------------------+
| 2017-12-14         |
+----------------------------+
1 row in set (0.00 sec)

Among them:

2017-12-14 is the data to be converted.

DATE is the converted type.

The standard syntax is this:

CAST(expr AS type)

It should be noted here that the type type does not support all data types, but supports specific data types, which is the focus of today's article. (I suffered this loss, taking it for granted that I supported all data types, and I was beaten in the face).

Unsupported error reporting:

mysql> SELECT CAST('1024' AS int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 1

Supported types

The following is a list of data types that the CAST function supports conversion:

类型	备注
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:mm:ss
TIME	HH:mm:ss
DECIMAL	通常用于带小数位
CHAR	固定长度字符串
NCHAR	类型于CHAR1致
SIGNED	1个有符号的64整数位
UNSIGNED	1个无符号的64整数位
BINARY	2进制字符串
JSON	MySQL 5.7.8 及更高版本

Note:

Among them, DATE supports the range from 1000-01-01 to 9999-12-31 (experimental version is:)

If it is: 999-01-01, the result will be 0999-01-01.

If it is: 01-01-01, it will be: 2001-01-01.

mysql> select cast('999-11-11' as DATE);
+---------------------------+
| cast('999-11-11' as DATE) |
+---------------------------+
| 0999-11-11        |
+---------------------------+
1 row in set (0.00 sec)

mysql> select cast('01-11-11' as DATE);
+--------------------------+
| cast('01-11-11' as DATE) |
+--------------------------+
| 2001-11-11        |
+--------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20  |
+-----------+
1 row in set (0.00 sec)

2. The value of expr in CAST function can be converted to type, and the conversion result is correct, otherwise the default value after conversion, such as Null, 0, etc.

For example, if an Char type is converted to an Demical type, the result of the conversion is 0.

mysql> SELECT CAST('ANDYQIAN' AS DECIMAL);
+-----------------------------+
| CAST('ANDYQIAN' AS DECIMAL) |
+-----------------------------+
|              0 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

Conversion case

Here are some common examples of type conversion.

DATE type

mysql> select cast('2017-12-14' as DATE);
+----------------------------+
| cast('2017-12-14' as DATE) |
+----------------------------+
| 2017-12-14         |
+----------------------------+
1 row in set (0.00 sec)

TIME type

mysql> select cast('12:00:00' as TIME);
+--------------------------+
| cast('12:00:00' as TIME) |
+--------------------------+
| 12:00:00         |
+--------------------------+
1 row in set (0.00 sec)

DATETIM type

mysql> select cast('2017-12-14 00:11:11' as DATETIME);
+-----------------------------------------+
| cast('2017-12-14 00:11:11' as DATETIME) |
+-----------------------------------------+
| 2017-12-14 00:11:11           |
+-----------------------------------------+
1 row in set (0.00 sec)

SIGNED type

CAST(expr AS type)

0

UNSIGNED Type

CAST(expr AS type)

1

DECIMAL type

CAST(expr AS type)

2

These are all the contents of this arrangement. When you test, you can enter commands to try. If there is anything you don't understand, you can discuss it in the message area below.