MySQL explicit type conversion example sharing

In the previous article, we mentioned the CAST function, which is used for explicit type conversion. There are many benefits when avoiding implicit type conversions. In fact, there are still many details here that need to be sorted out. This article mainly introduces the basic knowledge of MySQL explicit type conversion, and gives the analysis results through examples. Let's learn together.

First, let’s take a look at the following conversion:

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

in:

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

DATE is the converted type.

The standard syntax is like this:

CAST(expr AS type)

What needs to be noted here is that the type type does not support all data types, but supports specific data types, which is also the focus of today's article. (I have suffered this loss. I took it for granted that all data types are supported, but I was slapped in the face).

Unsupported error:

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 supported by the CAST function:

type	Remark
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:mm:ss
TIME	HH:mm:ss
DECIMAL	Usually used with decimal places
CHAR	Fixed length string
NCHAR	The type is consistent with CHAR
SIGNED	A signed 64-bit integer
UNSIGNED	An unsigned 64-bit integer
BINARY	Binary string
JSON	MySQL 5.7.8 and higher

Notice:

The supported range of DATE is: 1000-01-01 to 9999-12-31, (the 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 the CAST function can be converted to type, and the conversion result will be correct. Otherwise, the converted result will have a default value, such as Null, 0, etc.

For example: if a Char type is converted to a Demical type, the conversion result 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 commonly used type conversion examples.

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

mysql> select cast('-1024' as SIGNED);
+-------------------------+
| cast('-1024' as SIGNED) |
+-------------------------+
|          -1024 |
+-------------------------+
1 row in set (0.00 sec)

UNSIGNED type

mysql> select cast('-1024' as UNSIGNED);
+---------------------------+
| cast('-1024' as UNSIGNED) |
+---------------------------+
|   18446744073709550592 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

DECIMAL type

mysql> select cast('18.11' as DECIMAL(18,2));
+--------------------------------+
| cast('18.11' as DECIMAL(18,2)) |
+--------------------------------+
|             18.11 |
+--------------------------------+
1 row in set (0.00 sec)

Related recommendations:

The best way to upgrade MySQL instance sharing

Detailed explanation of PHP encapsulation Mysql operation class

How PHP solves the problem of Chinese garbled data stored in MySQL

The above is the detailed content of MySQL explicit type conversion example sharing. For more information, please follow other related articles on the PHP Chinese website!