通过子查询更新或插入固定前缀的递增字段

insertPrefixMaxID.sql

# 如果前缀要使用年份加月份，第7行需要增加判断条件
UPDATE `table` SET `no`=
    (
        SELECT `no`
        FROM (
            SELECT IF(
                (SELECT 1 FROM `table` WHERE `no` IS NOT NULL AND `no` <> "") IS NULL,
                "NO1000",
                (
                    SELECT CONCAT("NO", SUBSTRING(`no`, 3) + 1)
                    FROM `table`
                    WHERE `no` IS NOT NULL AND `no` <> "" ORDER BY `no` DESC LIMIT 1
                )
            ) AS `no`
        ) AS `t`
    )
WHERE `uid`=?