PHP & MySQL: How to create unique `year-month-number` keys?

Question

I have to create unique id's for invoices. Each invoice id is of the form year-month-number which consists of the current year & month of the invoice and an incrementing number, which is starting at 1 (gaps are not allowed).

So for example, if we have 4 invoices in January 2017 I would have the following 4 invoice id's:

2017-1-1
2017-1-2
2017-1-3
2017-1-4

Now I want to create an app that creates these unique id's. In particular I want to make sure that even when 2 people request an invoice number at the exact same time, they should get different id's.

I am using InnoDB and I have the following table

book

year  | month  | number | 
------------------------
2017  | 7      | 2      | 
2017  | 6      | 5      |
2017  | 5      | 6      |

If an invoice has not been created for a year-month pair, then no entry is in the database. The primary key is the year-month pair and number is an auto increment index.

Assume I would compute the next invoice id like this:

$stmt = $db->prepare('INSERT INTO book(year,month,number) 
                      VALUES (?,?,1) 
                      ON DUPLICATE KEY UPDATE number= LAST_INSERT_ID(number+1)');
$stmt->bind_param('ii', $year, $month);
$stmt->execute();
echo 'Next invoice id: ' . $year . '-' . $month . - . $db->insert_id;

Explanation: $db->insert_id; returns the column number because its an auto increment column and LAST_INSERT_ID(number+1) increases the number that has been inserted last (also maybe by a different user? I am not sure about it and I have problems finding that out in the docs http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id)

Would this code really work, or does it maybe create multiple identical id's if people execute this code simultaneously?

Edit Assume the number of current month/year is 5. Why is it not possible that 2 people compute an invoice at the same time, so that both queries upgrade the number 5 to 6 at the same time? In this case, they would both get the invoice id '2017-11-6' right?

do you not have an AI'd column? if not, you should since that will automatically make all records unique. — Funk Forty Niner, Nov 23 '17 at 17:25
@Fred-ii- the `number` field is an `AI` column, but I increase it with update. — Adam, Nov 23 '17 at 17:26
you shouldn't have to increase the AI for it, it will increase on its own. I feel I'm not fully grasping what it is you want to do here. Can you add `mysqli_error($db)` to the query and see what that returns? — Funk Forty Niner, Nov 23 '17 at 17:28
@Fred-ii- you are right, this is a very unusual way to use the `AI` field. But the field `number` is only an `AI` key so that it gets returned by `$db->insert_id;`. In fact, its not even unique. Could be `2017-7-2` and `2017-8-2` in the database at the same time. However `2017-7-2` and `2017-7-3` is not possible. I found that trick from here (https://stackoverflow.com/a/38544279/2311074) but I am just not sure if if it may give two different users the same invoice id or not. — Adam, Nov 23 '17 at 17:32
*Hm.......* - the `001` etc. are those related to the `number` column? The `and number is an auto increment index` won't increment with leading zeros, so you may have to find another method to do that (I've seen that done somewhere before) and add another column for it, an `id` as an AI and remove/ALTER the present AI'd column. Maybe I didn't get your question/related columns and `00X_integer` so I may not be of more help on this, sorry Adam. Wish I could be of more help on this. See if someone else can offer a(another) helping hand. — Funk Forty Niner, Nov 23 '17 at 17:39
@Fred-ii- no problem, thanks for looking at the problem. Your right about the leading zeros they shouldn't be there.. I remove them now. — Adam, Nov 23 '17 at 17:41
You're welcome Adam. Well.. just a last shot here. If those are the only columns you have, then I would suggest you add another related to the person/company that it is related to, that way you wouldn't have to go through all this trouble. One such as a company name/email and another with an AI'd column for an `id`, while dropping what you have now and simply use mysql's built-in date/datetime functions/types. You'll then be guaranteed uniqueness. You could also concatenate / assign a variable to multiple columns, it's an option. Least, that's what I would do but that's just me. — Funk Forty Niner, Nov 23 '17 at 17:46
This code shouldn't create two identical numbers per year-month combination. But you might get gaps, if the script fails for any reason after grabbing a number. — Paul Spiegel, Nov 23 '17 at 18:49
@PaulSpiegel Is it possible to create two identical numbers per year-month combination if I remove `LAST_INSERT_ID` or is that essential to prevent that? — Adam, Nov 23 '17 at 22:34
If the numbers don't need to be consecutive, but can have gaps, simply use `AUTO_INCREMENT`. If you must avoid gaps, see http://mysql.rjweb.org/doc.php/myisam2innodb#index_issue_2_column_pk — Rick James, Nov 24 '17 at 15:45

score 1 · Accepted Answer · answered Nov 24 '17 at 17:27

For questions like this, you can try it—by opening two terminal windows and using the mysql client.

mysql1> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      5 |
+------+-------+--------+

Start a transaction in two concurrent sessions:

mysql1> begin;

mysql2> begin;

Session 1 does the IODKU and increments the number (but does not commit yet, because begin implicitly takes us out of autocommit mode):

mysql11> insert into book values (2017, 5, 0) 
  on duplicate key update number = last_insert_id(number+1);

mysql1> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      6 |
+------+-------+--------+

Session 2 still sees the original number value, because of repeatable-read transaction isolation. But once it tries to do it's own increment, it waits because session 1 still has the row locked.

mysql2> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      5 |
+------+-------+--------+
mysql12> insert into book values (2017, 5, 0) 
  on duplicate key update number = last_insert_id(number+1);
-- waits for lock

Commit in session 1:

mysql1> commit;

Now the IODKU in session 2 finishes, and we can see it incremented the number a second time:

mysql2> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      7 |
+------+-------+--------+

Note that this way you won't set the `LAST_INSERT_ID` on the first insert. To fix that it should be `insert into book values (2017, 5, last_insert_id(1)) ...` — Paul Spiegel, Nov 24 '17 at 18:04
Thank you, that is also a great addition to your last answer from Jul 2016 :P. I guess I need to dig in more deeply to see why `$stmt = $db->prepare ... $stmt->execute` is the same as `mysql1> begin; ... mysql1> commit;`. But just to be sure - this would not work without innodb right? - Well actually now I can test it :D. — Adam, Nov 24 '17 at 18:17
@Adam for transactions with `mysqli` you should use [mysqli::begin_transaction](http://php.net/manual/en/mysqli.begin-transaction.php) and [mysqli::commit](http://php.net/manual/en/mysqli.commit.php). — Paul Spiegel, Nov 24 '17 at 18:27
@PaulSpiegel tahnk you! Do you also know some basic literature that covers this kind of stuff and is easier to read then the documentation? — Adam, Nov 24 '17 at 18:32
@Adam, I did not learn this from a book. First I got a degree in computer science. Then I read lots of documentation. then I used logic to create experiments to test my understanding. Then I answered questions like yours on the internet for 20 years. It takes time, but there's no better way to learn a subject. — Bill Karwin, Nov 24 '17 at 19:13

PHP & MySQL: How to create unique `year-month-number` keys?

1 Answers1