MySQL statement experience

1. Field Auto-Subtraction

UPDATE 'table _ name' SET 'Total' = IF ('Total' <1, 0, 'Total'-1) WHERE 'id' = 1;

Generally, when performing field subtraction, we need to determine a value externally.

This can save a query for an external value judgment, or prevent the overflow of the value when the total value of the unsigned field is 0, which may be the maximum value or report an error.

2. The update set uses the subquery data.

UPDATE 'bz _ xs_chapter 'AS c, (select count (*) AS total FROM 'bz _ xs_chapter' WHERE 'bookid' = 22042) AS c1, (select count (*) AS total FROM 'bz _ xs_tome 'WHERE 'bookid' = 22372) AS c2SET 'C '. 'name' = 'c1 '. 'Total', 'c '. 'word' = 'c2 '. 'Total' WHERE 'C '. 'id' = 2889820

In this way, you can use the subquery to update multiple field values at a time, which is much more efficient.

3. High Efficiency of COUNT (*)

I have read many related posts before, and some say that the efficiency of using COUNT (id) is high. Each of them seems rational, but I will test it myself:

100 W data, mb data table, COUNT (*) faster than COUNT (id) at least 10 times or even times