An example of an SQL statement in which MySQL fields are added and subtracted
- 2020-06-07 05:27:05
- OfStack
The MySQL autoincrement statement should be familiar and simple
That's fine, but sometimes we have to do subtraction,
For example, the number of comments on an article should be subtracted by 1 after deleting or locking a comment
The total number of unsigned article comments statistics field is unsigned, that is, the value between 0 and 65535
1. In general, the + sign can be changed to the -sign in a similar way as above, but the problem is that if the current comments statistic value is 0 and then subtracted, the maximum value of the field type will be 65535
2. To avoid this problem, the first idea is to query the comments statistical field value according to the id primary key, then subtract it by PHP, and then execute SQL command twice in total
Today, google checked and found no information in this field, looked at the grammar function of MySQL and so on... Try the following statement can be directly completed by 1 statement, that is, add if judgment, as shown in the following example:
When comments is 0 by default, comments-1 = 65535; But if I test it, if I directly judge es32EN-1 =65535, it doesn't seem to work, I don't know why, I'm not familiar with this, I don't know if if here doesn't support the = sign, but comments-1 > = 65535 is ok, so when comments is 0, IF(' comments '-1 > =65535,0, 'comments' -1) will return 0 prompt: the maximum value 65535 is the maximum value in the unsigned state of smallint. Please adjust other field types accordingly
----------------------------------------------------------------------------------------------------------------------
2014/02/03 added: at first I wrote it like this, but later I found it was too stupid, so I changed it slightly:
If we want to subtract x, we want to know if it's less than x
update `info` set `comments` = `comments`+1 WHERE `id` = 32
That's fine, but sometimes we have to do subtraction,
For example, the number of comments on an article should be subtracted by 1 after deleting or locking a comment
The total number of unsigned article comments statistics field is unsigned, that is, the value between 0 and 65535
1. In general, the + sign can be changed to the -sign in a similar way as above, but the problem is that if the current comments statistic value is 0 and then subtracted, the maximum value of the field type will be 65535
update `info` set `comments` = `comments`-1 WHERE `id` = 32
2. To avoid this problem, the first idea is to query the comments statistical field value according to the id primary key, then subtract it by PHP, and then execute SQL command twice in total
Today, google checked and found no information in this field, looked at the grammar function of MySQL and so on... Try the following statement can be directly completed by 1 statement, that is, add if judgment, as shown in the following example:
update `info` set `comments` = IF(`comments`< 1,0,`comments`-1) WHERE `id` = 32
When comments is 0 by default, comments-1 = 65535; But if I test it, if I directly judge es32EN-1 =65535, it doesn't seem to work, I don't know why, I'm not familiar with this, I don't know if if here doesn't support the = sign, but comments-1 > = 65535 is ok, so when comments is 0, IF(' comments '-1 > =65535,0, 'comments' -1) will return 0 prompt: the maximum value 65535 is the maximum value in the unsigned state of smallint. Please adjust other field types accordingly
----------------------------------------------------------------------------------------------------------------------
2014/02/03 added: at first I wrote it like this, but later I found it was too stupid, so I changed it slightly:
update `info` set `comments` = IF(`comments`<1, 0, `comments`-1) WHERE `id` = 32
If we want to subtract x, we want to know if it's less than x