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)
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)
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
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)
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 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)
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 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)
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!