How to use CONVERT function in MySQL for data type conversion

How to use the CONVERT function in MySQL for data type conversion

In the MySQL database, the CONVERT function is a very practical function, which can be used to convert data types. By using CONVERT function, we can convert one data type to another data type, which is very useful when dealing with data of different data types. This article will explain how to use the CONVERT function for data type conversion and provide some practical code examples.

1. Requirements for data type conversion

In the actual data processing process, we often encounter the need for data type conversion. For example, convert a string type data to an integer type, convert a date type data to a string type, etc. At this time, the CONVERT function can help us implement these conversion operations.

2. The syntax of the CONVERT function

The syntax of the CONVERT function is as follows:

CONVERT(expr, type)

Among them, expr represents the object to be converted An expression or field; type represents the target data type to be converted to. In MySQL, supported data types include integer types (INT, BIGINT, etc.), decimal types (DECIMAL, FLOAT, etc.), character types (CHAR, VARCHAR, etc.), etc.

3. Examples of data type conversion

The following are some common examples of data type conversion:

1. Convert string type to integer type

Suppose we have a table t1, which contains a field s1 of string type, and we want to convert s1 to an integer type. This can be achieved using the following statement:

SELECT CONVERT(s1, INT) FROM t1;

2. Convert the integer type to the string type

Assume we There is a table t2, which contains a field n1 of integer type, and we want to convert n1 to string type. This can be achieved using the following statement:

SELECT CONVERT(n1, CHAR) FROM t2;

3. Convert date type to string type

Assume we There is a table t3, which contains a field d1 of date type, and we want to convert d1 to string type. This can be achieved using the following statement:

SELECT CONVERT(d1, CHAR) FROM t3;

4. Convert floating point number type to integer type

Assume we There is a table t4, which contains a field f1 of floating point type, and we want to convert f1 to an integer type. This can be achieved using the following statement:

SELECT CONVERT(f1, INT) FROM t4;

4. Sample code

The following is a complete sample code that demonstrates how to use The CONVERT function performs data type conversion:

-- Create a data table
CREATE TABLE t5 (
id INT,
name VARCHAR(50),
age VARCHAR(10)
);

--Insert some data
INSERT INTO t5 (id, name, age) VALUES (1, 'Alice', '25');
INSERT INTO t5 (id, name, age) VALUES (2, 'Bob', '32');
INSERT INTO t5 (id, name, age) VALUES (3, 'Charlie', '18');

- - Query data and perform data type conversion
SELECT
id,
name,
CONVERT(age, INT) AS age_int
FROM
t5;

above In the example, we first create a data table named t5 and insert some data into it. Then, we used the SELECT statement to query the t5 table, and used the CONVERT function in the query results to convert the age field from the string type to the integer type.

Summary:

This article introduces how to use the CONVERT function in MySQL for data type conversion and provides some practical code examples. By using CONVERT function, we can easily convert between different data types in MySQL database. In the actual data processing process, understanding and mastering the use of the CONVERT function will greatly improve our data processing efficiency.

The above is the detailed content of How to use CONVERT function in MySQL for data type conversion. For more information, please follow other related articles on the PHP Chinese website!