Implementation of mysql data type conversion

1. Problem

There is a table as shown below, we need to find the data whose result value is greater than the reference_high value

Then we wrote the following SQL query

SELECT i.result,i.reference_high FROM report_item i
LEFT JOIN report r ON r.id=i.report_id 
WHERE r.org_id=54 AND r.report_status=1
AND r.add_date BETWEEN '2020-12-01' AND '2020-12-28' 
AND i.reference_high<>'' AND i.result<>''
AND i.result > i.reference_high ;

Then execute to view the results

The query results are not as expected. There are a lot of data that do not meet the query condition i.result > i.reference_high

2. Positioning

Looking at the SQL statement for creating the table, we found that result and reference_high are of varchar type, not numeric type, so the query results did not match expectations.

3. Solution

Method 1: Change the table field data type to numeric

However, considering the business usage scenario, both fields may have non-numeric input, so it is not feasible.

Method 2: Modify the query statement and convert the data type to numeric type

Take the above data as an example

1. Implicit conversion: add 0 to the string to be compared and then compare it. The data is successfully filtered.

2. Display conversion

(1) Use the convert function: convert the string to a floating point number, then compare it, and the data is successfully filtered

(2) Use the cast function: Almost the same as the convert function

This is the end of this article about the implementation of MySQL data type conversion. For more relevant MySQL data type conversion content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!