mysql - 如果值为 NULL，则将值递增 1

Question

如果表中至少有一行，则下面的查询有效。我希望它在返回 NULL 时返回 1。

mysql> desc users_map_item;
+----------------+-----------------------+------+-----+---------+-------+
| Field          | Type                  | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+-------+
| map_user_id    | mediumint(8) unsigned | NO   | MUL | NULL    |       |
| map_item_id    | mediumint(8) unsigned | NO   | PRI | NULL    |       |
| unique_item_id | mediumint(8) unsigned | NO   |     | NULL    |       |
| item_owner     | mediumint(8) unsigned | NO   |     | NULL    |       |
| privilege      | tinyint(1)            | YES  |     | NULL    |       |
+----------------+-----------------------+------+-----+---------+-------+

SELECT case when unique_item_id IS NULL then isnull(max(unique_item_id)+1) else     
max(unique_item_id)+1 end as unique_item_id FROM users_map_item WHERE map_user_id = 1;

Answer 1

SELECT MAX(COALESCE(unique_item_id, 0)) + 1 FROM users_map_item WHERE ...

应该可以解决问题。