Mysql field self-reducing SQL statement Example introduction _mysql

Source: Internet
Author: User
MySQL's self-add statement everyone should be familiar and very simple
Copy Code code as follows:

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

That's OK, but sometimes we'll be involved in subtraction,

For example: The number of comments on the article, after deleting or locking a comment, requires a total comment on the article minus one

Comments smallint (5) Unsigned article Comments total Statistics field no symbol that's the value between 0 ~ 65535

1. Usually, it can be similar to the above method to change the + number to-number on the line, but the problem is if the current comments statistic value of 0 o'clock and then do subtraction will become the field type of the maximum number 65535
Copy Code code as follows:

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

2. In order to avoid this problem the general idea can only be based on the ID primary key query out the article Comments statistics field values, and then through PHP subtraction, and then update again, before and after the total need to execute two SQL command

Google looked up today did not find this information, looked at the MySQL syntax functions and so on ... Try the following statement to complete a statement directly, that is, add an if judgment, the following example:
Copy Code code as follows:

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

The default comments is 0 o'clock, comments-1 = 65535, but the test if the direct Judge comments-1=65535 seems not, do not know what the reason for this is not very familiar with this is not know is not here if not supported = number, but comments-1 >= 65535 can be set up, so when comments is 0 o'clock, if (' Comments ' -1>=65535,0, ' comments '-1) will return 0 prompts: Maximum number 65535 is the maximum value in the smallint unsigned state, other words Segment Type Please adjust accordingly

--------------------------------------------------------------------------------------------------------------- -------

2014/02/03 supplements: This is the beginning of the writing, and later found too stupid, slightly changed:
Copy Code code as follows:

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

To subtract x, determine if it is less than X
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.