Geek asks and answers

Add prefix to auto-increment in mysql db

I have my database with table test1. It has the primary identifier "Id", which is an automatic increment. Now the identifier is in the format 1,2,3 .... Is it possible to save the primary identifier as PNR1, PNR2, PNR3 ..., etc. (with automatic increment).

+3
source share
4 answers

No. Either add a prefix to the request, or use a view.

+5
source

Not really, but you can use a different column (but a view) this is already described here: Automatically increment MySQL user values

+4
source

Yes, you can do this if you have the INT prefix. You have id as int in table

 // START PREFIX
  $query = mysql_query("SELECT id FROM `table_name` ORDER BY id DESC LIMIT 1"); 
  // GET THE LAST ID MAKE SURE IN TABLE YOU 9991

  while ($row = mysql_fetch_object($query)) {
    $lastId =  $row->id;
  }

  list($prefix,$Id) = explode('999',$lastId );
  $Id = ($Id+1);
  $new_id = '999'.$Id;
  // END PREFIX



$insertQuery = mysql_query("INSERT INTO `table_name` SET id = '".$new_id."',...");
+1
source

Hi, I did it like this:

Product table (products):

id_prod(varchar(11), NOT NULL, PK), name(varchar(40))

Product Sequence Table (productidseq):

id(AI, PK, NOT NULL)

Before inserting a trigger into the product table:

CREATE DEFINER=`root`@`localhost` TRIGGER `dbname`.`products_BEFORE_INSERT` BEFORE INSERT ON `products` FOR EACH ROW
BEGIN
insert into productidseq (id) values(NULL);
set new.id_prod = concat('PROD or any prefix here',last_insert_id());
set @productId = new.id_prod; -- To use outside of trigger this variable is useful.
END

When you run the query below:

insert into products (name) values('Bat');

the data inside the tables will be like this:

products:

id | name
---|-----
 1 | Bat

productidseq:

id 
---
 1

If there is any better way than this or any minus with this, please comment below. Thank.

0
source

More articles:


All Articles