Home  >  Article  >  Database  >  How to convert data type in mysql

How to convert data type in mysql

WBOY
WBOYOriginal
2021-12-27 10:55:3011174browse

Mysql method of converting data types: 1. Use the cast() function to convert the data type. The syntax is "cast (field name as converted type)"; 2. Use the convert() function to convert the data type. The syntax is "convert(field name, converted type)".

How to convert data type in mysql

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How does mysql convert data types?

MySQL's CAST() and CONVERT() functions can be used to obtain a value of one type and generate another type. A type of value. That is, convert the data type.

The specific syntax of the two is as follows:

CAST(value as type);
CONVERT(value, type);

is CAST (xxx AS type), CONVERT (xxx, type).

The types that can be converted are limited. This type can be one of the following values:

Binary, with the effect of the binary prefix: BINARY Character type, can take parameters: CHAR() Date: DATE Time: TIME Date and time type: DATETIME Floating point number: DECIMAL Integer: SIGNED Unsigned integer: UNSIGNED

Here are a few examples:

mysql> SELECT CONVERT('23',SIGNED);
+----------------------+
| CONVERT('23',SIGNED) |
+----------------------+
|                   23 |
+----------------------+
1 row in set

Example 2

mysql> SELECT CAST('125e342.83' AS signed);
+------------------------------+
| CAST('125e342.83' AS signed) |
+------------------------------+
|                          125 |
+------------------------------+
1 row in set

Like the above example, convert varchar to int using cast( a as signed), where a is a string of type varchar.

In SQL Server, the following code demonstrates the hexadecimal storage result of date storage when the datetime variable contains only a simple date and a simple time.

DECLARE @dt datetime
  
--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--结果: 0x0000000100000000
  
--单纯的时间
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--结果: 0x000000000000012C

MySQL's type conversion is the same as that of SQL Server, except that the type parameters are a little different: CAST (xxx AS type), CONTVER (xxx, type).

Recommended learning: mysql video tutorial

The above is the detailed content of How to convert data type in mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement:
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