mysql conversion function

MySQL is a relational database management system with a wide range of application scenarios. When processing data, it is often necessary to convert between different formats. MySQL provides many conversion functions to convert data from one format to another. This article will introduce MySQL conversion functions and illustrate them with examples.

1. CAST function

The CAST function is used to convert one data type into another data type. Its basic syntax is:

CAST(expr AS type)

where expr is the converted expression and type is the target data type. The data types supported by MySQL are as follows:

1. Integer types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
2. Floating point types: FLOAT, DOUBLE, DECIMAL
3. Character String type: CHAR, VARCHAR
4. Date time type: DATE, TIME, DATETIME, TIMESTAMP

Here are some examples:

--Convert the string to Integer
SELECT CAST('123' AS SIGNED);

-- Convert integer to string
SELECT CAST(123 AS CHAR);

-- Convert date Into a string
SELECT CAST(NOW() AS CHAR);

--Convert the string into a date
SELECT CAST('2022-10-01' AS DATE);

2. CONVERT function

The CONVERT function is also used to convert one data type to another data type. Its syntax is similar to the CAST function:

CONVERT(expr, type)

Among them, expr is the expression to be converted, and type is the target data type. It should be noted that the CONVERT function can specify the character set and collation, which is very useful when processing multi-language data. For example:

-- Convert the string to a UTF8 encoded string
SELECT CONVERT('China', CHAR CHARACTER SET utf8);

-- Convert the string to GBK Encoded string
SELECT CONVERT('China', CHAR CHARACTER SET gbk);

-- Convert the string into binary form and specify the collation
SELECT CONVERT('China', BINARY) COLLATE utf8_bin;

3. DATE_FORMAT function

The DATE_FORMAT function is used to convert date and time types into strings in a specified format. The syntax is as follows:

DATE_FORMAT(date, format)

Among them, date is an expression of date and time type, and format is a string format. The following placeholders can be used in the format string:

1. %Y: four-digit year
2. %m: month, number represents
3. %d: The day of the month, represented by numbers
4. %H: hours, 24-hour system
5. %i: minutes
6. %s: seconds

Here are some examples:

--Convert date and time to year-month-day format
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

-- Convert date and time into month, day and year format
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y');

-- Convert date and time into hour and minute format
SELECT DATE_FORMAT(NOW(), '%H:%i');

4. STR_TO_DATE function

The STR_TO_DATE function is used to convert a string into a date and time type. It takes two parameters, the first is the string to be converted and the second is the target date format. The date format is similar to the format parameter of the DATE_FORMAT function. For example:

-- Convert string to date type
SELECT STR_TO_DATE('2022-10-01', '%Y-%m-%d');

- - Convert the string into date and time type
SELECT STR_TO_DATE('2022-10-01 08:30:00', '%Y-%m-%d %H:%i:%s');

5. IF function

IF function is often used to determine whether an expression is true or false, and returns different results. The basic syntax is:

IF(expr1, expr2, expr3)

Among them, expr1 is the expression to be judged. If the expression is true, expr2 is returned, otherwise expr3 is returned. Here are some examples:

-- Determine the size of the value and return different strings
SELECT IF(10 > 5, '10 is greater than 5', '10 is less than or equal to 5');

-- Determine whether strings are equal and return different strings
SELECT IF('abc' = 'abc', 'Strings are equal', 'Strings are not equal');

- - Determine the date size and return different strings
SELECT IF('2022-10-01' > '2022-01-01', 'October 1 is later than January 1', 'October 1 As early as January 1st');

Summary:

MySQL's conversion function is very powerful and can be used to complete the conversion of various data formats. This article introduces the basic usage of CAST, CONVERT, DATE_FORMAT, STR_TO_DATE and IF functions, and gives some examples. I hope readers can master these functions and better process data in MySQL.

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