[SQLite 开发] SQLite字段自增自减的SQL语句 (IF条件语句)

SQLite字段自增

UPDATE table_name
SET column1 = column1 + 1
WHERE columnID = 1

SQLite字段自减

UPDATE table_name
SET column1 = column1 - 1
WHERE columnID = 1

语句优化

防止column1已经等于0了，再-1等于负数。我们需要曾加一个if语句判断

UPDATE table_name
SET column1 = if(column1 < 1, 0, column1 - 1)
WHERE columnID = 1

但是SQLite不支持if语句，我们可以使用case when

UPDATE table_name
SET column1 = CASE 
	WHEN column1 < 1 THEN 0
	ELSE column1 - 1
END
WHERE columnID = 1