Shulou(Shulou.com)05/31 Report--

Editor to share with you an example of MySQL explicit type conversion, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Undefined

In the previous article, we mentioned the CAST function, which is used to display for type conversion. There are many benefits when avoiding implicit type conversions. In fact, there are still many details that need to be sorted out. This paper mainly introduces the basic knowledge of MySQL explicit type conversion, and gives the analysis results through examples. Let's learn it together.

First, take a look at the following transformation:

Mysql > SELECT CAST ('2017-12-14' AS DATE) +-- + | CAST ('2017-12-14' AS DATE) | +-+ | 2017-12-14 | +-+ 1 row in set (2017 sec)

Where:

2017-12-14 is the data to be converted.

DATE is the converted type.

The standard syntax goes like this:

CAST (expr AS type)

It is important to note that the type type does not support all data types, but supports specific data types, which is the focus of today's article. (I suffered this loss and took it for granted that I supported all data types, but I was hit 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 Typ

The following is a list of data types that the CAST function supports conversion:

Type remarks DATEYYYY-MM-DDDATETIMEYYYY-MM-DD HH:mm:ssTIMEHH:mm:ssDECIMAL is commonly used with decimal places CHAR fixed length string NCHAR type consistent with CHAR a signed 64 integer bit UNSIGNED an unsigned 64 integer bit BINARY binary string JSONMySQL 5.7.8 and later

Note:

DATE supports the range from 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, 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 is correct, otherwise the default value of the converted result, such as Null,0, etc.

For example, if 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 sec)

Conversion case

Here are some examples of commonly used type conversions.

DATE Typ

Mysql > select cast ('2017-12-14' as DATE) +-- + | cast ('2017-12-14' as DATE) | +-+ | 2017-12-14 | +-+ 1 row in set (2017 sec)

TIME Typ

Mysql > select cast ('12 as TIME 00 as TIME); +-+ | cast ('12 mysql 00 as TIME) | +-+ | 12:00:00 | +-+ 1 row in set (0.00 sec)

DATETIM Typ

Mysql > select cast ('2017-12-1400 as DATETIME 11 as DATETIME) +-- + | cast ('2017-12-14 00 11' as DATETIME) | +-+ | 2017-12-14 00:11:11 | +- -- + 1 row in set (0.00 sec)

SIGNED Typ

Mysql > select cast ('- 1024' as SIGNED); +-+ | cast ('- 1024' as SIGNED) | +-+ |-1024 | +-+ 1 row in set (sec)

UNSIGNED Typ

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

DECIMAL Typ

Mysql > select cast ('18.11' as DECIMAL (18penny 2)) +-- + | cast ('18.11' as DECIMAL (18Magne2)) | +-- + | 18.11 | +-- -+ 1 row in set (0.00 sec) above is all the content of the article "MySQL explicit type conversion instance" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.