What is interesting about this situation can be solved using the MyISAM storage.
I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site
CREATE TABLE site_workorder_seq
(
SiteID int not null,
SiteWorkorderNum int not null auto_increment,
PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;
Here is a sample loading into this table:
mysql> DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)
mysql> USE david
Database changed
mysql> CREATE TABLE site_workorder_seq
-> (
-> SiteID int not null,
-> SiteWorkorderNum int not null auto_increment,
-> PRIMARY KEY (SiteID,SiteWorkorderNum)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO site_workorder_seq (SiteID) VALUES
-> (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
-> (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
+--------+------------------+
16 rows in set (0.00 sec)
mysql>
Let's look at the the last WorkorderNum from each site
mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
-> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 2 |
+--------+------------------+
5 rows in set (0.05 sec)
mysql>
Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:
INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;
Let's run this and see what happens
mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
-> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
| 5 |
+-------------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
+--------+------------------+
17 rows in set (0.00 sec)
mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
-> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
| 4 | 4 |
| 5 | 2 |
+--------+------------------+
5 rows in set (0.00 sec)
mysql>
As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.
If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:
create sequence my_sequence_name;
create table foo
(
id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);
That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.
Best Answer
The sequence will return values in order, whether or not they exist in the table. Your code will need to defend against that. Any method to defend against possibly already-in-use serial values will really hurt concurrency - and if your DB is big enough that you're wrapping int4 serial columns, you care about concurrency.
Most people just use bigint keys and don't worry about ID re-use. You won't run out.