CODEWITHSUNDEEP
mysql
Crazy
Crazy

Reputation: 867

How to generate a unique id based on different id category?

I have a table as shown below

| id         | name        | doc_no       |
|:-----------|------------:|:------------:|
| 1          | abc         |     D11710001
| 2          | efg         |     D21710001
| 3          | hij         |     D31710001
| 4          | klm         |     D41710001
| 5          | nop         |     D51710001
| 1          | qrs         |     D11710002

I want to generate an unique id based on the id given. For example, when i have item to be stored in this table, it will generate an unique id based on the id of the table.

Note: The id in this table is a foreign key. The doc no can be modified by user into their own format manually.

The id format - D 'id' 'year' 'month' 0001(auto increment)

How can i write the sql to generate unique id during storing data?

Upvotes: 2

Views: 1636

Answers (2)

Ravi
Ravi

Reputation: 31417

You could create Trigger and update the column or you can write the update state just after your INSERT

insert into <YOUR_TABLE>(NAME,DOC_NO) values('hello','dummy');

update <YOUR_TABLE> set DOC_NO=CONCAT('D',
                            CAST(YEAR(NOW()) AS CHAR(4)),
                            CAST(MONTH(NOW()) AS CHAR(4)),
                            LAST_INSERT_ID()) 
WHERE id=LAST_INSERT_ID();

Please note, as above SQL may cause race condition, when simultaneously server get multiple requests.

@Tim Biegeleisen has good point though, as it is better to construct the id when you are SELECTing the data.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521339

Continuing with the comment by @strawberry I might recommend not storing the ID in your database. Besides the fact that accessing the auto increment ID at the same time you are inserting the record might be tricky, storing this generated ID would be duplicating the information already stored elsewhere in your table. Instead of storing your ID, just generate it when you query, e.g.

SELECT
    id, name, doc_no,
    CONCAT('D', id, STR_TO_DATE(date, '%Y-%m'), auto_id) AS unique_id
FROM yourTable;

This assumes that you would be storing the insertion date of each record in a date column called date. It also assumes that your table has an auto increment column called auto_id. Note that having the date of insertion stored may be useful to you in other ways, e.g. if you want to search for data in your table based on date or time.

Upvotes: 1

Related Questions