By Vijay


2013-07-27 03:38:04 8 Comments

I have table like this

table
id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30) NOT NULL,

I want to increment my id field like 'LHPL001','LHPL002','LHPL003'... etc. What should I have to do for that? Please let me know any possible way.

3 comments

@Fluffy 2015-03-02 21:56:31

Create a table with a normal numeric auto_increment ID, but either define it with ZEROFILL, or use LPAD to add zeroes when selecting. Then CONCAT the values to get your intended behavior. Example #1:

create table so (
 id int(3) unsigned zerofill not null auto_increment primary key,
 name varchar(30) not null
);

insert into so set name = 'John';
insert into so set name = 'Mark';

select concat('LHPL', id) as id, name from so;
+---------+------+
| id      | name |
+---------+------+
| LHPL001 | John |
| LHPL002 | Mark |
+---------+------+

Example #2:

create table so (
 id int unsigned not null auto_increment primary key,
 name varchar(30) not null
);

insert into so set name = 'John';
insert into so set name = 'Mark';

select concat('LHPL', LPAD(id, 3, 0)) as id, name from so;
+---------+------+
| id      | name |
+---------+------+
| LHPL001 | John |
| LHPL002 | Mark |
+---------+------+

@4 Leave Cover 2017-02-16 08:19:56

Initially I was thinking of storing the LHPL as a separate column then concat it by concat(col1,col2) but then it will be a pain in the S when someday I need to change the LHPL. So I guess I will be doing it in the program server side.

@yhAm 2017-03-10 02:37:19

I know it is late but I just want to share on what I have done for this. I'm not allowed to add another table or trigger so I need to generate it in a single query upon insert. For your case, can you try this query.

CREATE TABLE YOURTABLE(
IDNUMBER VARCHAR(7) NOT NULL PRIMARY KEY,
ENAME VARCHAR(30) not null
);

Perform a select and use this select query and save to the parameter @IDNUMBER

(SELECT IFNULL
     (CONCAT('LHPL',LPAD(
       (SUBSTRING_INDEX
        (MAX(`IDNUMBER`), 'LHPL',-1) + 1), 5, '0')), 'LHPL001')
    AS 'IDNUMBER' FROM YOURTABLE ORDER BY `IDNUMBER` ASC)

And then Insert query will be :

INSERT INTO YOURTABLE(IDNUMBER, ENAME) VALUES 
(@IDNUMBER, 'EMPLOYEE NAME');

The result will be the same as the other answer but the difference is, you will not need to create another table or trigger. I hope that I can help someone that have a same case as mine.

@peterm 2013-07-27 04:26:33

If you really need this you can achieve your goal with help of separate table for sequencing (if you don't mind) and a trigger.

Tables

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE table1
(
  id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

Now the trigger

DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

Then you just insert rows to table1

INSERT INTO Table1 (name) 
VALUES ('Jhon'), ('Mark');

And you'll have

|      ID | NAME |
------------------
| LHPL001 | Jhon |
| LHPL002 | Mark |

Here is SQLFiddle demo

@n00b 2018-03-28 01:11:17

Is the statement within the trigger atomic? I.e. is it possible that between the INSERT and SET another thread/request inserts into table1_seq causing LAST_INSERT_ID() to return the id of the other request and not the one that was inserted within the BEGIN/END.

@user9437856 2018-04-15 14:21:02

@peterm, I have a doubt trigger part. I changed SET NEW.id = CONCAT('A', LPAD(LAST_INSERT_ID(), 4, '500')); because I need my id start from 5001 so I set 500. So I am getting output A5001 which is correct. My doubt is once id reached 5999 then what will be the next id? Is it 6000?

@user9437856 2018-04-15 14:26:01

I tried my self after adding the records in the database I added more than 1000 records and after 5999 my id value starts from V1000 which is totally wrong. I need it should be continued 6000,6001,6002 and so on. any idea in this?

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Get the new record primary key ID from mysql insert query?

14 Answered Questions

[SOLVED] How to get primary key of table?

  • 2010-02-26 11:41:46
  • Martti Laine
  • 97832 View
  • 67 Score
  • 14 Answer
  • Tags:   php mysql primary-key

15 Answered Questions

[SOLVED] Reorder / reset auto increment primary key

  • 2009-04-11 16:11:39
  • Jonathan
  • 200362 View
  • 120 Score
  • 15 Answer
  • Tags:   mysql database

6 Answered Questions

[SOLVED] How do I add indices to MySQL tables?

15 Answered Questions

[SOLVED] MySQL error: key specification without a key length

11 Answered Questions

[SOLVED] Insert auto increment primary key to existing table

  • 2012-01-30 21:05:50
  • FlyingCat
  • 198425 View
  • 146 Score
  • 11 Answer
  • Tags:   mysql primary-key

2 Answered Questions

[SOLVED] SQL create table and set auto increment value without Alter table

  • 2015-04-07 08:16:23
  • Blue Fishy
  • 14794 View
  • 3 Score
  • 2 Answer
  • Tags:   mysql create-table

1 Answered Questions

How to make MySQL table primary key auto increment with some prefix with current year

  • 2016-08-08 10:06:42
  • Deepak
  • 205 View
  • -1 Score
  • 1 Answer
  • Tags:   mysql

3 Answered Questions

[SOLVED] How to make SQL Server table primary key auto increment with some characters

2 Answered Questions

[SOLVED] MySQL - how to use VARCHAR as AUTO INCREMENT Primary Key

Sponsored Content