Analysis of MySQL explicit type conversion, analysis of mysql explicit

Analysis of MySQL explicit type conversion, analysis of mysql explicit

CAST Function

In the previous article, we mentioned the CAST function for display type conversion. There are many benefits to avoid implicit type conversion. In fact, there are still a lot of details to be sorted out.

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)

Where:

Is the data to be converted.

DATE is the converted type.

The standard syntax is as follows:

CAST(expr AS type)

Note that the type does not support all data types, but supports specific data types, which is also the focus of this article. (I have suffered this loss. I assume that all data types are supported, and the result is blurred ).

Error not supported:

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:

Type	Remarks
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	Same type as CHAR
SIGNED	A signed 64-bit integer
UNSIGNED	An unsigned 64-bit integer
BINARY	Binary string
JSON	MySQL 5.7.8 and later

Note:

The range supported by DATE is 1000-01-01 to 9999-12-31 (experimental version :)

If the value is 999-01-01, the result is 0999-01-01.

If it is: 01-001, 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 is correct. Otherwise, the default value of the converted result, such as Null and 0, is returned.

For example, if a Char type is converted to the 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 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

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)

The above is all the content. During the test, you can enter a command to try it out. If you still have any questions, you can discuss it in the comment area below.