Solve the problems arising from the mysql statement MAX () function

     In the database, when some of our data is not yet clear about the specific type, it is not a problem to use the varchar type to represent the field type, (this is a good habit!) Because the varchar type data stores numeric types or charactersString types can be handled, and it is relatively simple to perform switching calculations in the back-end code, just do a little processing.
     And this blog post aims to record the problems that occurred in the MAX() function in the mysql statement of the project. Although the problem is small, it is quite a headache if you don't know where the problem is. After all, I haveIt was very confusing before.
     is shown below, the current_data type is varchar when designing the table structure:

     According to the idiomatic way of writing in our mysql statement, the MAX() and MIN() functions should be written as follows:

SELECTMAX(current_data) AS maxCurrentData,MIN(current_data) AS minCurrentDataFROM tunnel_detect_dataWHERE create_time >= '2022-06-01'AND '2022-06-02' >= create_time

     The result of the query is as follows:

     The query result is compared with the actual data in the table below, and it is found that the result is incorrect. In the comparison, several data are greater than the value of 993.07. Why does this happen?

     Reason: the data is varchar type, then the MAX function getsThe maximum value will be compared from the beginning, that is to say, the maximum value obtained by the MAX function are all values ​​starting with 9, and the number with more than 9 at the beginning is considered to be the maximum value, which leads to deviations in the results we query.
    Solution: If the data type in the table is not a varchar type but a specific numerical type, there is no problem with the above sql statement, but if it is a varchar type of data, the query field data must be "+0" processing, otherwise it does not match the actual result, as shown below:

SELECTMAX(current_data + 0) AS maxCurrentData,MIN(current_data + 0) AS minCurrentDataFROM tunnel_detect_dataWHERE create_time >= '2022-06-01'AND '2022-06-02' >= create_time

     The query result is as follows:

    The actual comparison of the data in the following table shows that the query result after the "+0" operation is the correct result.

    To summarize: Judge the mysql statement MAX() function China needs to do "+0" processing, specific problems are analyzed in detail, bugs are solved, a lot of fun!
     Passing friends, if the blog post helps you solve your problem, you can like + bookmark + follow a wave~ I will continue to update blog posts related to project learning, thank you for your support!!!Wuhu take off ️️️