php - 如何 "one column of table minus 1"？

Question

我有一个名为 FLY 的表，其中有一列名为 FLY_NUM.im 试图在 mysql 查询(在 PHP 中)中减少该列的值。像这样:

mysql_query("UPDATE fly SET  `fly_Num` = `fly_Num` -1 WHERE fly_ID ='1' ");

这是错误的!每次运行此查询时，FLY_NUM 列都设置为零。我该怎么做？

Answer 1

您的查询有效且正确。 - 和 1 之间也不需要空格。

测试用例:

CREATE TABLE fly (fly_id int, fly_num int);

INSERT INTO fly VALUES (1, 1);
INSERT INTO fly VALUES (1, 2);
INSERT INTO fly VALUES (1, 3);
INSERT INTO fly VALUES (1, 4);
INSERT INTO fly VALUES (1, 5);
INSERT INTO fly VALUES (1, 6);
INSERT INTO fly VALUES (1, 7);

更新查询:

UPDATE fly SET `fly_Num` = `fly_Num` -1 WHERE fly_id ='1';

Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

新表格内容:

SELECT * FROM fly;

+--------+---------+
| fly_id | fly_num |
+--------+---------+
|      1 |       0 |
|      1 |       1 |
|      1 |       2 |
|      1 |       3 |
|      1 |       4 |
|      1 |       5 |
|      1 |       6 |
+--------+---------+
7 rows in set (0.00 sec)

即使您对 fly_num 列使用 varchar 也能正常工作:

CREATE TABLE fly (fly_id int, fly_num varchar(10));

INSERT INTO fly VALUES (1, '1');
INSERT INTO fly VALUES (1, '2');
INSERT INTO fly VALUES (1, '3');
INSERT INTO fly VALUES (1, '4');
INSERT INTO fly VALUES (1, '5');
INSERT INTO fly VALUES (1, '6');
INSERT INTO fly VALUES (1, '7');

更新查询:

UPDATE fly SET `fly_Num` = `fly_Num` -1 WHERE fly_id ='1';

Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

新表格内容:

SELECT * FROM fly;

+--------+---------+
| fly_id | fly_num |
+--------+---------+
|      1 |       0 |
|      1 |       1 |
|      1 |       2 |
|      1 |       3 |
|      1 |       4 |
|      1 |       5 |
|      1 |       6 |
+--------+---------+
7 rows in set (0.00 sec)