在没有AUTO_INCREMENT的CHAR字段中查找下一个ID

Question

I got a Table which stores objects. An Object can be anything from a chair to a employee. An Object got an ObjectID, which is a 10 characters code-39 barcode label on the Object.

Many Objects already have a Label, thus an ObjectID assinged to them. Some have Prefixes, e.g. "9000000345" might be a Desk or "0000000895" might be a folder with invoices.

When People start a new Folder for example, they take pre-printed Barcode Labels and put them on it. The pre-printed Barcode Labels are generated by a Printer which just increases a number by 1 and zerofills it to 10 Digits and then prints it as code-39.

All Most of the objects are stored in Excel Sheets. They now should be migrated into a MySQL Database.

Now, the System should also be able to create objects on its own. Objects created by the System have a leading "1" e.g. "1000000426".

The Problem: How do I get the next ObjectID for Auto generated Objects? I cant really use AUTO_INCREMENT because there are also non-auto-generated rows in the table.

Another Thing to say is that the 'ObjectID' field has to be CHAR(10) because for special occasions there were alphanumeric prefixes used like "T1" -> "T100003158"

My Table when using AUTO_INCREMENT:

    | ID |   Created   |   ObjectID   | Parent |    Title    |   Changed   | Note |
    |----|-------------|--------------|--------|-------------|-------------|------|
    | 1  | <timestamp> | "1000000001" |  NULL  | "Shelf 203" | <timestamp> | NULL |
    | 2  | <timestamp> | "9000000458" |  NULL  | "Lamp"      | <timestamp> | NULL |
    | 3  | <timestamp> | "1000000003" |  NULL  | "Shelf 204" | <timestamp> | NULL |

The ObjectID of the last Object in the table should be "1000000002" not "1000000003"

I hope I could explain the Problem well enough.

Answer 1

Naive solution can be:

SELECT CAST(ObjectID AS UNSIGNED) + 1 FROM yourTable WHERE ObjectId LIKE "1%" ORDER BY ObjectID DESC LIMIT 1

Basically search for all Object ID starting with 1xxxx then sort them (because its zero padded we can still sort) and then cast result to int and increment it.

Might be faster to cast to int first and then do between. Rest would be the same