CodingForums.com - Resolved Need help on creating ticket ID

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Resolved Need help on creating ticket ID (http://www.codingforums.com/showthread.php?t=275752)

nani_nisha06

10-08-2012 03:58 PM

Need help on creating ticket ID

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

10-08-2012 05:19 PM

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

10-08-2012 06:51 PM

Quote:

Originally Posted by Fou-Lu (Post 1277631)

Fou-Lu,

Also suggest on the below thread.

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

Regards,
Nani

Old Pedant

10-08-2012 08:44 PM

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

For example:

Code:

CREATE TABLE tickets_base (

    prefix CHAR(4),

    ticketcount INT AUTO_INCREMENT,

    name VARCHAR(50),

    PRIMARY KEY ( prefix, ticketcount )

);

And then

Code:

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

Code:

INSERT INTO tickets_base ( prefix, ticketcount, name )

VALUES( 'ABCD', NULL, 'Abigail Adams' );

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

Code:

SELECT * FROM tickets WHERE name LIKE 'Abigail%'

And that will return you

Code:

ABCD000073 Abigail Adams

nani_nisha06

10-09-2012 09:15 AM

Old Pedant Sir,

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

Code:



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

10-09-2012 01:51 PM

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.

All times are GMT +1. The time now is 08:22 PM.