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 install WSL2 Ubuntu20.04 on Windows 10 and set up the docker environment

Enable WSL Make sure the system is Windows 10 200...

The space is displayed differently in IE, Firefox, and Chrome browsers

&nbsp;&nbsp;The display effects on IE, Fir...

How to disable ads in the terminal welcome message in Ubuntu Server

If you are using the latest Ubuntu Server version...

Detailed explanation of various HTTP return status codes

When a request is sent to your server to display ...

MySQL 8.0.13 installation and configuration tutorial under CentOS7.3

1. Basic Environment 1. Operating system: CentOS ...

After the click event of html hyperlink a, jump to the address pointed to by href

Sometimes, we need to use the hyperlink <a> ...

How to monitor array changes in JavaScript

Preface When introducing defineProperty before, I...

Web page comments cause text overflow in IE

The experimental code is as follows: </head>...

How to solve the problem that scroll-view of WeChat applet cannot slide left and right

I'm currently working on my own small program...

How to add automatic completion commands for docker and kubectl on Mac

Introduction to kubectl kubectl is a command line...

Nginx server adds Systemd custom service process analysis

1. Take nginx as an example Nginx installed using...

Nodejs makes a document synchronization tool to automatically synchronize to gitee implementation code

Original Intention The reason for making this too...

Detailed explanation of Linux host name modification command

Linux change hostname command 1. If you only need...

The latest virtual machine VMware 14 installation tutorial

First, I will give you the VMware 14 activation c...

Vue implements simple slider verification

This article example shares the implementation of...