Non-empty question in MySQL Query

MySQL query is an operation that we often use. We will analyze the non-null problem in MySQL query for you and hope to help you learn about MySQL query.

The Order Status field of the table is designed to be varchar type and has the following values: NULL, pending, pending refund, refund, and cancel. We know the order whose query status is cancel. The SQL statement can be written as follows:

 
 

  
  
SELECT o.oid,o.moneyreceipt,o.moneyget,o.thecurrency,o.status FROM qorder o WHERE o.status = 'cancel' 
 
 

The SQL statement can query the correct data, but when we want to query orders in a non-cancel status, it may be troublesome because the status field is NOT set to NOT NULL, therefore, the status value of most orders is NULL. In this case, the data queried using '<>' is incorrect, and only non-empty data except the status cancel is queried, NULL is not found. SELECT o. oid, o. moneyreceept, o. moneyget, o. thecurrency, o. status FROM qorder o WHERE o. status <> 'cancel' reason: NULL Value operation
The NULL value may be strange until you get used to it. In concept, NULL means "no value" or "unknown value", and it is regarded as a different value. To test NULL, you cannot use arithmetic comparison operators such as =, <or! =. To illustrate it, try the following query:

 
 

  
  
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;  
  
  
+----------+-----------+----------+----------+  
  
  
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |  
  
  
+----------+-----------+----------+----------+  
  
  
|     NULL |      NULL |     NULL |     NULL |  
  
  
+----------+-----------+----------+----------+  
 
 

Obviously, you cannot get meaningful results through these comparisons. Instead, use the is null and is not null operators:

 
 

  
  
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;  
  
  
+-----------+---------------+  
  
  
| 1 IS NULL | 1 IS NOT NULL |  
  
  
+-----------+---------------+  
  
  
|         0 |             1 |  
  
  
+-----------+---------------+  
 
 

Note that in MySQL, 0 or NULL means false while other values mean true. The default true value of Boolean operations is 1. Based on the above NULL value operation results, this method is used to solve the problem: SELECT o. oid, o. moneyreceip, o. moneyget, o. thecurrency, o. status FROM qorder o where ifnull (o. status, 'Pending') <> 'cancel' learning: IFNULL (expr1, expr2)
If expr1 is not NULL, IFNULL () returns expr1; otherwise, it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.

 
 

  
  
mysql> select IFNULL(1,0);  ->1  
  
  
mysql> select IFNULL(0,10);  ->0  
  
  
mysql> select IFNULL(1/0,10);  ->10.0000  
  
  
mysql> select IFNULL(1/0,'yes');          
  
  
 
  
  
->'yes'IF(expr1,expr2,expr3)  
  
  
 
 
 

IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF () returns expr2; otherwise, expr3 is returned. IF () returns a number or string value, depending on the context in which it is used.

 
 

  
  
mysql> select IF(1>2,2,3);  -> 3  
  
  
mysql> select IF(1<2,'yes','no'); -> 'yes'  
  
  
mysql> select IF(strcmp('test','test1'),'yes','no'); -> 'no'  
 
 

Expr1 is calculated as an integer, which means that if you are testing a floating point or string value, you should use a comparison operation.

 
 

  
  
mysql> select IF(0.1,1,0);  -> 0  
  
  
mysql> select IF(0.1<>0,1,0);  -> 1  
 
 

In the first case above, IF (0.1) returns 0, because 0.1 is converted to an integer, resulting in testing IF (0 ). This may not be what you expected. In the second case, compare and test the original floating point value to see if it is non-zero. The comparison result is used as an integer.

 
 

  
  
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END   
  
  
 
  
  
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END   
  
  
 
 
 

The first version returns result, where value = compare-value. In the second version, if the first condition is true, result is returned. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, NULL is returned.

 
 

  
  
mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END; -> "one"  
  
  
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;  -> "true"  
  
  
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END; -> NULL   
 
 

Troubleshooting of MySQL query timeout

Implementation of MySQL non-repeated Query

Detailed description of how to name a shard using the mysql Command Line

How MySQL query optimizer works

MySQL Date and Time Functions