包含特殊数字格式的字符串列的AUTO_INCREMENT实现

Question

Context and goal

In table clients I have a column clientNum CHAR(11) NOT NULL with UNIQUE KEY constraint. It contains client number in the format xxx-xxx-xxx where x is a decimal digit. For more details on the format see below.

I want to implement something like AUTO_INCREMENT for this column so that each client gets their number calculated automatically. From MySQL CREATE TABLE docs:

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

So I want to find the next number available and use it as clientNum value for newly inserted client row. Next number available is current maximum of clientNum incremented.

I’m coding in PHP using PDO to access the MySQL database (see PDO Tutorial for MySQL Developers).

Client number format

As stated above, the client number is in format xxx-xxx-xxx where x is a decimal digit. The range of each segment is 000 to 999. It is basically a 9-digit integer with leading zeroes and dash as thousands separator. It cannot get above 999-999-999.

Currently we want it be even more restricted, specifically in format 000-1xx-xxx (between 000-100-000 and 000-199-999). But there are already some numbers in the database that can start anywhere from 000-000-001 to 500-000-000.

Unfortunately it has to be stored in this format, I cannot change it.

Finding maximum

I need to get the max number in range 000-100-000 to 000-199-999, values outside this range must be ignored. This is where my problem comes in because as said before some numbers already exist above this.

Maximum is never 000-199-999. Otherwise in would result in adding 000-200-000 and the next time called maximum will be 000-199-999 again, resulting in attempt to insert 000-200-000 again.

How incrementation works

In PHP in can be done like this:

$clientNum = "000-100-000";
$clientNum = str_replace("-", "", $clientNum);
$clientNum++;
$clientNum = implode("-", str_split(str_pad($clientNum, 9, "0", STR_PAD_LEFT), 3));

Final $clientNum value is 000-100-001.

When the initial number is 000-120-015 then the code above produces 000-120-016. Overflow propagates to the next segment, i.e. 000-100-999 becomes 000-101-000. 999-999-999 cannot be incremented.

Idea to start with

In a loop I want to get the next number available, check if that number exists in the database, and if so, redo that loop until it finds an unused number. I know how to check if it’s in the database the first time, but I’m not sure how to do the loop.

Does anyone know a way to do this?

Answer 1

You may want to solve this in SQL, because otherwise you need two transactions (one for reading, one for writing) and meanwhile the number could be used by a concurrent access.

In MySQL, you can use this SQL reimplementation of your PHP code:

INSERT(INSERT(LPAD(CAST(CAST(REPLACE(clientNum, '-', '') as UNSIGNED) + 1 as CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-')

This increments 000-000-999 to 000-001-000 and 999-999-999 to 100-000-000 (truncated from 100-000-0000 by LPAD()). I warned you.

E.g. to just preview what the next value is, use

SELECT INSERT(INSERT(LPAD(CAST(CAST(REPLACE(clientNum, '-', '') as UNSIGNED) + 1 as CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-') FROM clients

If you want to use this when inserting a new row, it is used like this:

INSERT
INTO clients(clientNum, name)
SELECT
    INSERT(INSERT(LPAD(CAST(
        COALESCE(MAX(CAST(REPLACE(clientNum, '-', '') AS UNSIGNED)), 0) + 1
    AS CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-'),
    'John Doe'
FROM clients

This works regardless of what API you use to access the database, as long as it is MySQL database. The database does the computation. However, it does not work if clients is a temporary table, which I expect it not to be. More on that below.

See also string functions, CAST(), COALESCE() and INSERT … SELECT in MySQL manual.

---

Later you added that the permitted values are from range 000-100-000 to 000-199-999. Other values shall be ignored for the purpose of finding maximum. A WHERE clause must be added to the SELECT part of INSERT written above.

INSERT
INTO clients(clientNum, name)
SELECT
    INSERT(INSERT(LPAD(CAST(
        COALESCE(MAX(CAST(REPLACE(clientNum, '-', '') AS UNSIGNED)), 0) + 1
    AS CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-'),
    'John Doe'
FROM clients
WHERE clientNum BETWEEN '000-100-000' AND '000-199-999'

---

Then you stated that my solution does not work for you and proposed a supposed fix:

INSERT
INTO clients(clientNum, name)
VALUES
    (SELECT
        INSERT(INSERT(LPAD(CAST(
            COALESCE(MAX(CAST(REPLACE(clientNum, '-', '') AS UNSIGNED)), 0) + 1
        AS CHAR), 9, '0'), 7, 0, '-'), 4, 0, '-')
        FROM clients AS tmptable
        WHERE clientNum BETWEEN '000-100-000' AND '000-199-999'),
    'John Doe'

This uses a subquery instead of the INSERT … SELECT syntax.

In MySQL, table cannot be modified (by INSERT in this case) and read by a subquery at the same time. Quoting the subquery manual:

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

However, you found a workaround using a temporary table. A temporary table is used when an alias (in this case clients AS tmptable) is defined, which evades reading from and writing to the same table at the same time. You used temporary table to store the original table, the article describing the workaround uses it to store the result of the subquery (which is more efficient, I guess). Both approaches work.

At this point I want to point out that my solution should work (and works for me!) too except for the improbable case when clients is a temporary table. I think I can expect it not to be one. Quoting the INSERT … SELECT manual page:

When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, it remains true that you cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement (see Section C.5.7.2, “TEMPORARY Table Problems”).

As for me this is explicitly saying that my original approach using INSERT … SELECT should work.

---

Just to provide a complete answer, I’ll address your original request for PHP solution using database polling. Once more I must add that this is certainly not a good solution.

Your clientNum column must be a unique key. You need to repeat the following steps until successful update:

1. Get the current maximum of clientNum.
2. Increment the obtained value.
3. Try to insert the row.
4. If successful, finish, otherwise throw the clientNum max value away and loop.

The insertion will fail due to violation of the aforementioned unique key constraint. This happens when another connection to the database successfully performs an insert in the meantime between steps 1. and 3..

You should prepare the statement outside the loop using PDO::prepare() and then execute it in the loop. The return value of execute method indicates success (true) or failure (false).

This is enough info to implement step 3.. Steps 1. and 2. consist of fetching the result of

SELECT MAX(clientNum) FROM clients

and running it through the code provided by Stephanus Yanaputra. Step 4. is a simple loop condition using the return value from execution of INSERT query in step 3..