How to Handle this issue in mysql primary key Insertion?

[vngx]

Hi there

I am working on a shoping web application.In which there is a fields for unique value Insertion.that is working fine.But I am inserting product like this
PI01
PI02
PI03
PI04
.
.
.

and I am getting value of last Inserted record like this

  String sql="SELECT product_id FROM product_info ORDER BY product_id  DESC LIMIT 1";
        try {
          pstmt=conn.prepareStatement(sql);
         rs=pstmt.executeQuery();
        if(!rs.next()){
            return "PI01";
        }
        else{
            String pastRecordId=rs.getString(1);
            System.out.println(pastRecordId);
            int id=Integer.parseInt(pastRecordId.substring(2, pastRecordId.length()));
            int newId=id+1;
            if(newId<=10){
            productId="PI0"+newId;
            }
            else{
                productId="PI"+newId;
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    System.out.println("Exiting getNewProductId() in AdminDao");
    return productId;

I amusing Java for this case
Problem is last Id it return is using the last alphabetical numerical sorted record from db.
Not the actual last Inserted record in db?

So how can I get actual last Inserted record from db?not the alphabetically sorted record

[Mittineague]

What database / version are you querying?

[vngx]

mysql 5.1.30-community

[Mittineague]

Thanks, I’m guessing the main problem is using Stings for Ids and wanting to use them as Ints.

This probably will give the same result but it’s worth a quick try

String sql="SELECT MAX(product_id) FROM product_info"

[vngx]

This is also giving same answar

Which I was getting
My Question is I am looking for some mechanism in database sides that will keep Information about last row that is Inserted in the mysql db table?.

I hope now you got my point?

[Mittineague]

Yes, I got what you were saying.

You could use LAST_INSERT_ID
if - the field was an AUTO_INCREMENT
but - it looks like the Id fields are not AUTO_INCREMENT

IMHO you should consider doing an ALTER TABLE making the Id field an AUTO_INCREMENT

[vngx]

But How could I make an alphanumberic key as autoincrement?

[Mittineague]

AFAIK you can’t easily. If there are already a lot of them, you would probably need to write a function to change the existing values and then do the ALTER

My Java is rusty, but I’m sure there is a way to do a (inefficient) sort outside of the query, (again, would need to write a function)

Maybe the better solution would be to add an new key that is AUTO_INCREMENT

How many rows are there?

[r937]

@Mittineague

Maybe the better solution would be to add an new key that is AUTO_INCREMENT

that’s probably the way to go

note that you could “simulate” the PI prefix on the auto_increment using concatenation on every query that accesses that column… which is made a lot easier using a view that has the same name as the table (the table would be renamed)

[RT_]

Auto increment is probably the best option. Searching text columns is much slower than integer columns too, so you should see a increase in query speed.

Is there a reason why you need an alpha numeric key?

[vngx]

I Will try your approach and later will update my post