Generating an auto generated number with 6 digits

Question

I am generating new ids for my animals and i am running the following Query

SELECT concat('TZ',YEAR(CURDATE()),FLOOR(RAND() * 999999.99)) as ID , animalid FROM adggeth.view_allanimals;

my output for the following query is

# ID, animalid
'TZ2019703169', 'TZN000044001722'
'TZ2019914906', 'TZN000067976797'
'TZ2019465022', 'TZN000094299429'
'TZ2019580395', 'TZN000192792688'

my expected output

# ID, animalid
'TZ2019000001', 'TZN000044001722'
'TZ2019000002', 'TZN000067976797'
'TZ2019000003', 'TZN000094299429'
'TZ2019000004', 'TZN000192792688'

How can generate a random number for my id to achieve the expected output

After changes i get the following unexpected error

using

CONCAT('TZ', YEAR(CURDATE()), LPAD(@seq, 6, '0'))

'TZ2019000001', 'TZN000044001722'
'TZ2019000001', 'TZN000067976797'
'TZ2019000001', 'TZN000094299429'
'TZ2019000001', 'TZN000192792688'

I think you're confusing the id used by the database internals with the public id. Store the internal id as an AI id. — Strawberry, Feb 21 '19 at 12:18
@MirieriMogaka mybe https://stackoverflow.com/questions/14298154/mysql-sequential-number-table helps — smartmeta, Feb 21 '19 at 12:18
@smartmeta I have gone through solution but i am still confused how to do it — Mirieri Mogaka, Feb 21 '19 at 12:29
What is that good for? It's not an ID if the result can change with every select. — Paul Spiegel, Feb 21 '19 at 13:37
i want to write a trigger, and being able to select would allow me to write the trigger to populate a table — Mirieri Mogaka, Feb 22 '19 at 05:23

O. Jones · Answer 1 · 2019-02-21T13:02:14.200

It looks like you want IDs with the format TZYYYYNNNNNN where YYYY is the present year and NNNNNN is a guaranteed sequential number. Here you ask about how to generate the sequential number in a way where it's guaranteed to be unique.

If you were using Oracle you could use a sequence object. But who can afford Oracle?

So, you need to simulate the sequence object in MySQL. It's a little nasty, but here we go:

Create the following table:

 CREATE TABLE sequence (
     sequence_id BIGINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
)

Then, each time you need a new sequence number, issue these three queries one after the other:

INSERT INTO sequence () VALUES ();
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SET @seq := LAST_INSERT_ID();

The third line places a guaranteed unique number into the @seq variable. This guarantee holds even if you have dozens of different client programs connected to your database generating sequence numbers. (The DELETE query merely keeps this otherwise pointless table from taking up too much space.)

Once you have @seq you can use it to generate your id values, something like this.

CONCAT('TZ', YEAR(CURDATE()), LPAD(@seq, 6, '0'))

To reset the sequence number at the first of the next year simply drop and recreate the sequence table.

@seq isn't magic. You must get a new value every time you need a new one. See my update. — O. Jones, Feb 21 '19 at 13:03

Generating an auto generated number with 6 digits

1 Answers1