A brief analysis of MySQL explicit type conversion

A brief analysis of MySQL explicit type conversion

CAST function

In the previous article, we mentioned the CAST function, which is used to display type conversion. There are many benefits when it comes to avoiding implicit type conversions. In fact, there are still many details here that need to be sorted out.

First, let’s 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 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 also the focus of today's article. (I have suffered this loss before. I took it for granted that all data types were 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 that the CAST function supports conversion:

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 later

Notice:

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

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

If it is: 01-01-01 then 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 result of the conversion is correct. Otherwise, the result after conversion is the default value, such as Null, 0, etc.

For example: when a Char type is converted to a 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 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 Types

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 of this compilation. You can enter the command to try it when testing. If there is anything you don’t understand, you can discuss it in the comment area below.

You may also be interested in:
  • Resolving MySQL implicit conversion issues
  • Summary of MySQL's implicit type conversion
  • MySQL implicit type conversion traps and rules
  • MySQL time difference functions (TIMESTAMPDIFF, DATEDIFF), date conversion calculation functions (date_add, day, date_format, str_to_date)
  • How to convert MySQL database data into JSON data using Python
  • Summary of MySQL commonly used type conversion functions (recommended)
  • MySQL date functions and date conversion and formatting functions
  • MySQL NULL data conversion method (must read)
  • Talk about implicit conversion in MySQL

<<:  Detailed steps to download Tomcat and put it on Linux

>>:  Solve the Linux Tensorflow2.0 installation problem

Recommend

React new version life cycle hook function and usage detailed explanation

Compared with the old life cycle Three hooks are ...

A brief discussion on the role of the docker --privileged=true parameter

Around version 0.6, privileged was introduced to ...

How to output Chinese characters in Linux kernel

You can easily input Chinese and get Chinese outp...

Complete steps to implement location punch-in using MySQL spatial functions

Preface The project requirement is to determine w...

MySQL Optimization Solution Reference

Problems that may arise from optimization Optimiz...

Mysql updates certain fields of another table based on data from one table (sql statement)

The following code introduces MySQL to update som...

JavaScript implementation of verification code case

This article shares the specific code for JavaScr...

MySQL 8.0.21.0 Community Edition Installation Tutorial (Detailed Illustrations)

1. Download MySQL Log in to the MySQL official we...

How to expand the disk size of a virtual machine

After Vmvare sets the disk size of the virtual ma...

How to install and configure the Apache Web server

Learn how to host your own website on Apache, a r...

How to use mysqladmin to get the current TPS and QPS of a MySQL instance

mysqladmin is an official mysql client program th...

Summary of four ways to loop through an array in JS

This article compares and summarizes four ways of...

Linux implements automatic and scheduled backup of MySQL database every day

Overview Backup is the basis of disaster recovery...

Install centos7 virtual machine on win10

1. Download VMware Workstation 64 version https:/...

How to publish a locally built docker image to dockerhub

Today we will introduce how to publish the local ...