Requirement: take the maximum id value + 1 in the stock table as the next id value.
Special case: considering that there will be no value in the table and max(id) will return null, case when is used for judgment.
Implementation 1: select (case max(id) is null when true then 0 else max(id)+1 end) from stock
Implementation 2: select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) from stock
Efficiency analysis:
Compared with the implementation of two, the implementation of one does not get the number, but also can get the value quickly with the help of index, so the efficiency should be higher than the implementation of two.
Validation of analysis:
(MySQL database)
When there is no data in the stock table:
mysql> explain select (case max(id) is null when true then 0 else max(id)+1 end) from stock; +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+ 1 row in set (0.00 sec) mysql> explain select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) from stock; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | | 2 | SUBQUERY | stock | index | NULL | PRIMARY | 4 | NULL | 3749 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------+ 2 rows in set (0.05 sec)
When there is data in the stock table:
mysql> select count(*) from stock; +----------+ | count(*) | +----------+ | 3768 | +----------+ 1 row in set (0.00 sec) mysql> explain select (case max(id) is null when true then 0 else max(id)+1 end) from stock; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.00 sec) mysql> desc select (case (select count(*) from stock) when 0 then 0 else max(id)+1 end) from stock; +----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | | 2 | SUBQUERY | stock | index | NULL | PRIMARY | 4 | NULL | 3696 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+ 2 rows in set (0.00 sec)