5

I have my database with table test1. It has a primary id "Id" which is auto-increment. Now the id is in the format 1,2,3.. . .Is it possible to store the primary Id as PNR1,PNR2,PNR3 .. . . and so on(with auto-increment).

dax
  • 10,779
  • 8
  • 51
  • 86
hjaffer2001
  • 933
  • 6
  • 18
  • 38
  • 1
    The answers on this questions are VERY UNHELPFUL! I did find much more help on this Q&A over here http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix for anyone looking to do this or similar! – JasonDavis Oct 01 '14 at 03:57
  • Possible duplicate of [How to make MySQL table primary key auto increment with some prefix](http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix) – showdev Mar 27 '17 at 16:12

4 Answers4

6

No. Either add the prefix in the query, or use a view instead.

Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
4

Not really, but you can use another column (but a view) this is already covered here: MySQL Auto Increment Custom Values

Community
  • 1
  • 1
radosch
  • 619
  • 3
  • 7
2

Yes you can do it if you have 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."',...");
test
  • 464
  • 5
  • 14
0

Hi, I made it work in this way :

Products Table (products):

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

Products Sequence Table (productidseq):

id(AI, PK, NOT NULL)

Before Insert Trigger in Products 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 below query :

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

data inside tables will we be like this :

products:

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

productidseq:

id 
---
 1

If any better way than this or any cons with this, please comment below. Thanks.

Himakar
  • 345
  • 4
  • 17