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

How to create your own image using Dockerfile

1. Create an empty directory $ cd /home/xm6f/dev ...

Practice of multi-layer nested display of element table

There is a requirement for a list containing mult...

Navicat connects to MySQL8.0.11 and an error 2059 occurs

mistake The following error occurs when connectin...

The difference between useEffect and useLayoutEffect in React

Table of contents Prerequisites useEffect commitB...

jQuery implements the practice of changing the position and size of div by dragging the mouse

To achieve an effect similar to Windows forms, dr...

Why is it slow when using limit and offset paging scenarios?

Let’s start with a question Five years ago when I...

Detailed tutorial on installing VirtualBox and Ubuntu 16.04 under Windows system

1. Software Introduction VirtualBox VirtualBox is...

Does MySql need to commit?

Whether MySQL needs to commit when performing ope...

CSS scroll-snap scroll event stop and element position detection implementation

1. Scroll Snap is a must-have skill for front-end...

Detailed explanation of MySQL EXPLAIN output columns

1. Introduction The EXPLAIN statement provides in...

Detailed example of remotely connecting to Docker using TLS encrypted communication

By default, Docker runs over a non-networked UNIX...

Simple example of adding and removing HTML nodes

<br />Simple example of adding and removing ...

Common problems and solutions during MySQL MGR construction

Table of contents 01 Common Faults 1 02 Common Fa...

Complete steps for Nginx proxy front-end and back-end separation projects with the same domain name

The front-end and back-end projects are separated...

Detailed installation process of MySQL 8.0 Windows zip package version

The installation process of MySQL 8.0 Windows zip...