How to convert numeric types in MySQL

MySQL is a popular relational database management system that is often used to store structured data. In MySQL, numeric types are one of the frequently used data types, but due to format differences that may exist between different systems and data sources, you may encounter some problems when converting numeric types. This article will introduce how to convert numeric types in MySQL.

First of all, we need to understand the common numeric data types and their related properties in MySQL. The following table lists the numeric data types supported by MySQL:

Data Type	Description
TINYINT	1 Byte signed integer
SMALLINT	2 Byte signed integer
MEDIUMINT	3 Byte signed integer
#INT	4 Byte signed integer
BIGINT	8 byte signed integer
FLOAT	Single precision floating point number
DOUBLE	Double precision floating point number
DECIMAL	Fixed precision decimal

In the above table, the integer type uses 1, 2, 3, 4, and 8 bytes respectively to store data, the floating point type uses 4 and 8 bytes respectively to store data, and the DECIMAL type uses 4 to 65 words. Sections vary in storage space to store data. The specific storage space depends on the number of decimal points set in the column definition.

Next, we will discuss common scenarios for numeric type conversion in MySQL:

1. Convert a string to a numeric type

In many cases Next, we need to convert the string stored in the database into a numeric type, such as converting the string entered by the user into a number and then performing calculations. In MySQL, you can convert a string to a numeric type by using CAST, CONVERT, or the operator.

First, let’s take a look at the usage of the CAST function. The CAST function accepts two parameters. The first parameter is the value that needs to be type converted, and the second parameter is the target data type that needs to be converted. For example, to convert the string '123' to the INT type, you can use the following statement:

SELECT CAST('123' AS INT);

If the string cannot be converted to the target data type, a NULL value will be returned.

Next, let’s look at the usage of the CONVERT function. The CONVERT function also accepts two parameters. The first parameter is the value that needs to be type converted, and the second parameter is the target data type that needs to be converted. For example, to convert the string '123' to the DOUBLE type, you can use the following statement:

SELECT CONVERT('123', DOUBLE);

Here, the target data type is not enclosed in quotation marks, but is written directly in the SQL statement. If the string cannot be converted to the target data type, 0 will be returned.

Finally, let’s look at the usage of type conversion using operator. When converting a string to a numeric type, you can add the string and the data type, for example:

SELECT '123' + 0;

Here, we add the string '123' to the number 0, and MySQL will automatically add the string '123' is converted to the numeric type 123.

2. Convert numeric type to string type

In many cases, we need to convert numeric type to string type, such as passing numbers as parameters to storage procedure or function. In MySQL, numeric types can be converted to string types by using the CAST or CONVERT functions.

First, let’s take a look at the usage of the CAST function. The CAST function accepts two parameters. The first parameter is the value that needs to be type converted, and the second parameter is the target data type that needs to be converted. For example, to convert the number 123 to a string type, you can use the following statement:

SELECT CAST(123 AS CHAR);

Here, we convert the number 123 to a CHAR type string.

Next, let’s look at the usage of the CONVERT function. The CONVERT function also accepts two parameters. The first parameter is the value that needs to be type converted, and the second parameter is the target data type that needs to be converted. For example, to convert the number 123 to a string type, you can use the following statement:

SELECT CONVERT(123, CHAR);

Here, we convert the number 123 to a CHAR type string.

3. Number type conversion when importing data

When importing data, the source data is often stored in CSV files or Excel files, and the numbers in these files Data is often stored in the form of strings. When importing data into MySQL, these string data need to be converted into numeric data types supported by MySQL.

For example, when using MySQL's LOAD DATA INFILE command to import data, we can use the SET clause to specify the data type of each column. For example, suppose we have a file named sample.csv that contains the following data:

"1001","John","Doe","30"
"1002","Jane","Doe","25"

We can use the following command to import the data into a MySQL database named sales table:

LOAD DATA INFILE 'sample.csv'
INTO TABLE sales
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@col1, @col2, @col3, @col4)
SET
    id = CAST(@col1 AS INT),
    first_name = @col2,
    last_name = @col3,
    age = CAST(@col4 AS INT)
;

Here, we use the CAST function to convert @col1 and @col4 into numbers of type INT, and then insert the values ​​into the id and age columns. Since first_name and last_name are columns of string type, we don't need to do type conversion.

Of course, in addition to the CAST function, we can also use the CONVERT function or operator to perform numeric type conversion when importing data.

To sum up, when we need to perform numeric type conversion in MySQL, we can use CAST, CONVERT or operator. When importing data, we can also use the SET clause to specify the data type of each column. By mastering these methods, we can be more flexible with numeric type data and adapt it to our business needs.

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