Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    16

    Question Unanswered: Auto Increment Question

    Hi,

    I have a problem and seen as though I'm new to MYSQL I thought I better ask someone how to get around it.

    I have a table of employee numbers, the problem is that these numbers also contain characters for instance EMP001. How can I auto increment it so it goes up for example .....

    EMP001
    EMP002

    then

    EMP003

    etc etc ..

    Anyone know, its probably easy to do just I couldn't figure it out.

    Thanks

    Jona

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you you are going to have to store other prefixes to rows, such as CUSxxx,SUPxxx then
    -remove the EMP prefix, if all are EMP then its not needed
    -set an autoincrement for the prime key

    if you do need to have EMP001..999, CUS001..999 etc then consider a redesign
    use a different primamry key and create your person reference using a stored procedure int he db (MySQL 5 on) or a function within the user interface

    the process is relatively simple
    -secure am exclusive lock on the table that contains the current number
    -store that number
    -increment a copy of the number
    -write it back to the table that contains the current number
    -release the lock
    -use the stored number

    how you store the number is up to you. The safest is probably as a separate row in a table somewhere, and store it as the 'next available' number, ie already incremented ready for the next requirement. Other may suggest some form of sql querry on the current data to find its max value. No doubt there are other ways. Of these two the first is by far the better solution, if for no other reason its more scalable and has less chance of affecting other processess or users using the data.

    HTH

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you you are going to have to store other prefixes to rows, such as CUSxxx,SUPxxx then
    -remove the EMP prefix, if all are EMP then its not needed
    -set an autoincrement for the prime key

    if you do need to have EMP001..999, CUS001..999 etc then consider a redesign
    use a different primamry key and create your person reference using a stored procedure int he db (MySQL 5 on) or a function within the user interface

    the process is relatively simple
    -secure am exclusive lock on the table that contains the current number
    -store that number
    -increment a copy of the number
    -write it back to the table that contains the current number
    -release the lock
    -use the stored number

    how you store the number is up to you. The safest is probably as a separate row in a table somewhere, and store it as the 'next available' number, ie already incremented ready for the next requirement. Other may suggest some form of sql querry on the current data to find its max value. No doubt there are other ways. Of these two the first is by far the better solution, if for no other reason its more scalable and has less chance of affecting other processess or users using the data.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •