Home > Database > Mysql Tutorial > body text

MySQL explicit type conversion example sharing

小云云
Release: 2018-01-05 16:36:27
Original
1506 people have browsed it

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)
Copy after login

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)
Copy after login

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
Copy after login

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)
Copy after login
mysql> select cast('01-11-11' as DATE);
+--------------------------+
| cast('01-11-11' as DATE) |
+--------------------------+
| 2001-11-11        |
+--------------------------+
1 row in set (0.00 sec)
Copy after login
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20  |
+-----------+
1 row in set (0.00 sec)
Copy after login

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)
Copy after login

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)
Copy after login

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)
Copy after login

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)
Copy after login

SIGNED type

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

UNSIGNED type

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

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)
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!