How to convert data type in mysql

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)".

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!