mysql conversion function

As the amount of data continues to grow and business needs continue to become more complex, the data types in the database are becoming more and more diverse. In practical applications, we often need to convert between different data types, which inevitably requires the use of some conversion functions.

MySQL is a commonly used relational database management system that provides many built-in data types and conversion functions. This article will introduce the commonly used conversion functions in MySQL to facilitate readers to convert data types in practical applications.

1. Conversion function definition

The conversion function in MySQL is a set of functions that can convert different data types to each other. These functions convert one data type to another.

2. Classification of conversion functions

Conversion functions in MySQL can be divided into the following categories:

1. Numerical functions
   Commonly used numerical functions in MySQL are ROUND(), TRUNCATE(), CEILING() and FLOOR() etc. These functions convert one numeric data type to another numeric data type.
2. Character functions
   Commonly used character functions in MySQL include CONCAT(), SUBSTRING(), REPLACE() and UPPER(), etc. These functions convert one character data type to another character data type.
3. Date functions
   Commonly used date functions in MySQL include DATE_ADD(), DATE_SUB(), TO_DAYS() and FROM_DAYS(), etc. These functions convert one date data type to another date data type.
4. Other functions
   There are other conversion functions in MySQL, such as IFNULL(), CAST() and CONVERT(), etc. These functions convert one data type to another.

3. Conversion function usage

1. ROUND() function: Retain a numeric data with a specified number of decimal places.

SELECT ROUND(123.456,2);

The result is: 123.46

2. TRUNCATE() function: Reserve a specified number of digits for a numerical data of decimals without rounding.

SELECT TRUNCATE(123.456,2);

The result is: 123.45

3. CEILING() function: Round up a numeric data.

SELECT CEILING(123.456);

The result is: 124

4. FLOOR() function: Round down a numeric data.

SELECT FLOOR(123.456);

The result is: 123

5. CONCAT() function: combine multiple character data into one string .

SELECT CONCAT('Hello', 'world');

The result is: Helloworld

6. SUBSTRING() function: intercept a string part.

SELECT SUBSTRING('Hello world', 1, 5);

The result is: Hello

7. REPLACE() function: Replace a string Replaces the specified substring in with another string.

SELECT REPLACE('Hello world', 'world', 'MySQL');

The result is: Hello MySQL

8. UPPER() function : Converts all characters in a string to uppercase.

SELECT UPPER('Hello world');

The result is: HELLO WORLD

9. DATE_ADD() function: Add to a date type data a time interval.

SELECT DATE_ADD('2021-01-01', INTERVAL 1 YEAR);

The result is: 2022-01-01

10. DATE_SUB( ) Function: Subtract a time interval from a date type data.

SELECT DATE_SUB('2021-01-01', INTERVAL 1 YEAR);

The result is: 2020-01-01

11. TO_DAYS( ) Function: Convert a date type data into a number of days.

SELECT TO_DAYS('2021-01-01');

The result is: 737791

12. FROM_DAYS() function: Convert a number of days to Date type data.

SELECT FROM_DAYS(737791);

The result is: 2021-01-01

13. IFNULL() function: If a data value is NULL, then returns another data value.

SELECT IFNULL(NULL, 'MySQL');

The result is: MySQL

14. CAST() function: Convert one data type to another A data type.

SELECT CAST('123' AS SIGNED);

The result is: 123

15. CONVERT() function: Convert one data type to another A data type.

SELECT CONVERT('123.456', DECIMAL(6,2));

The result is: 123.46

4. Summary

MySQL It provides many built-in conversion functions, which can convert different data types to each other, making it convenient for readers to convert data types in practical applications. Proficient in the usage of these functions can improve our data processing efficiency and improve application performance.

The above is the detailed content of mysql conversion function. For more information, please follow other related articles on the PHP Chinese website!