SQL statement example of MySQL field auto-increment and auto-increment _ MySQL

The SQL statement example of MySQL field auto-increment and auto-increment introduces bitsCN. comMySQL's auto-increment statement, which should be very familiar and simple.

Update 'info' set 'comments' = 'comments' + 1 WHERE 'id' = 32

This is fine, but sometimes we will involve subtraction,

For example, the number of comments of an article. after deleting or locking a comment, you must subtract one from the total number of comments of the article.

Comments smallint (5) unsigned total number of comments statistical field unsigned is 0 ~ Value between 65535

1. generally, you can change the "+" to "-" like the auto-increment method above, but the problem is that if the current comments statistical value is 0 and then subtraction is performed, the maximum value of this field type is 65535.

Update 'info' set 'comments' = 'comments'-1 WHERE 'id' = 32

2. to avoid this problem, the general idea is to first query the comments Statistics field value based on the id primary key, then perform subtraction using PHP, and then update again. a total of two SQL commands need to be executed before and after

Today, google did not find this information and looked at MySQL syntax functions... After trying the following statement, you can directly complete the statement, that is, add an if judgment, as shown in the following example:

Update 'info' set 'comments' = IF ('comments' <, 'comments'-1) WHERE 'id' = 32

When the default value of comments is 0, comments-1 = 65535. However, if comments-1 = 65535 is determined directly after testing, it does not seem to work. I do not know why, I am not very familiar with this. I don't know if the if clause does not support the = sign, but comments-1> = 65535 can be set. Therefore, when comments is 0, IF ('comments'-1> = 65535,0, 'comments'-1), the system returns 0. The maximum value 65535 is the maximum value in the smallint unsigned state, adjust other field types accordingly

Bytes ----------------------------------------------------------------------------------------------------------------------

Supplement: it was written in this way at the beginning. later I found it too stupid. I just changed it:

Update 'info' set 'comments' = IF ('comments' <1, 0, 'comments'-1) WHERE 'id' = 32

To subtract x, determine whether it is smaller than xbitsCN.com