Resolved Need help on creating ticket ID

[nani_nisha06]

Hi friends,

I want to create a field in the MYSQL which should be auto increment and have started from ABCD000001 and so on.......


Please help me !!!


Regards,
Nani

[Fou-Lu]

You can't, auto increment only works on numbers. They are literally surrogate keys.
Perhaps a better approach would be to use a char(36) and run against a UUID(). UUID will generate a 128bit hex encoded string. The documentation suggests is uses a UUID version 2, but it also specifies it uses random numbers on any OS that isn't freebsd based. Otherwise it is random replaced which is more appropriate of a version 4, even though it doesn't resign it as a version 4 UUID.
UUID is supposed to give you global unique numbers, but with the above mention that doesn't actually guarantee it now. Since its the key, it will inform you if it fails and you can try again.

[nani_nisha06]

Originally Posted by Fou-Lu

You can't, auto increment only works on numbers. They are literally surrogate keys.
Perhaps a better approach would be to use a char(36) and run against a UUID(). UUID will generate a 128bit hex encoded string. The documentation suggests is uses a UUID version 2, but it also specifies it uses random numbers on any OS that isn't freebsd based. Otherwise it is random replaced which is more appropriate of a version 4, even though it doesn't resign it as a version 4 UUID.
UUID is supposed to give you global unique numbers, but with the above mention that doesn't actually guarantee it now. Since its the key, it will inform you if it fails and you can try again.

Fou-Lu,

Also suggest on the below thread.


http://www.codingforums.com/showthread.php?t=275489

Regards,
Nani

[Old Pedant]

But another answer is to simply use TWO fields and then a VIEW when you SELECT from the table.

For example:

CREATE TABLE tickets_base (
    prefix CHAR(4),
    ticketcount INT AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY ( prefix, ticketcount )
);

And then

CREATE VIEW tickets 
AS
SELECT CONCAT(prefix, RIGHT( CONVERT( 1000000 + ticketcount, CHAR ), 6 ) ) AS ticket, name
FROM tickets_base;

So when you add records, you always use INSERT into ticket_base, for example

INSERT INTO tickets_base ( prefix, ticketcount, name )
VALUES( 'ABCD', NULL, 'Abigail Adams' );

But when you GET a record you always SELECT from the view:

SELECT * FROM tickets WHERE name LIKE 'Abigail%'

And that will return you

ABCD000073  Abigail Adams

[nani_nisha06]

Old Pedant Sir,

The above procedure worked like a charm but, I did small change in the create table, As below.

CREATE TABLE tickets_base (
    prefix CHAR(4),
    ticketcount INT AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY ( prefix), KEY (ticketcount) 
);  

//Because You can have an auto-Incremental column that is not the PRIMARY KEY, as long as there is an index (key) on it:

[guelphdad]

Your solution however is not as good as OP's solution.
with the two column primary key, you will only have to enter the info in the prefix column and the autoincrement will start at 1 and increase for each prefix so you will end up with

ABC 1
ABC 2
DEF 1
DEF 2
DEF 3
GHI 1

and for your solution you will just have the autoincrement column increase indefinitely, it won't start at 1 for each of your different values in the prefix column, whereas his will.