2

I want to put some text in front of my auto increment field with mysql, but I still have no answer to do this. here is the example data that I want to insert into table

"IDC00000001"

and what I do now just auto incremented integer with zerofill format, and the result just 00000001.

and I want the number auto incremented. so when Insert some data again it will be like this "IDC00000002", "IDC00000003",........,"IDC00000022",etc

my questions:

  1. how to solved this case?
  2. am I need a store procedure?
  3. what is best practice to do this, prepare this in php before insert or in mysql directly?
naticap
  • 379
  • 1
  • 5
  • 19
  • 2
    The first question you need to ask is "WHY" do you need this? Why not just use the auto-increment number, and add "ID" in front of it when fetching the data? – junkfoodjunkie Apr 21 '17 at 23:10
  • You're using PHP, just stick your prefix in during serialization, when you turn your SQL results into "whatever" (but most likely, HTML). The whole point of an autoincrementing `id` is that it's a guaranteed, unique-per-new-record number, don't try to change that: just add your string only at the point where that string becomes *needed*. – Mike 'Pomax' Kamermans Apr 21 '17 at 23:21
  • @junkfoodjunkie I want to display user id in front end same with id in database to make easier when we want to query. Thanks junkfoodjunkie – naticap Apr 22 '17 at 08:53
  • @Mike'Pomax'Kamermans Thank you for your advice, Mike – naticap Apr 22 '17 at 08:53

1 Answers1

3

What you need is trigger and one more table. Try this trigger below;

DELIMITER $$
CREATE TRIGGER tg_tableName_insert
BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
  INSERT INTO tableName_seq VALUES (NULL);
  SET NEW.id = CONCAT('IDC', LPAD(LAST_INSERT_ID(), 8, '0'));
END$$
DELIMITER ;

Dont forget to create sequence table;

CREATE TABLE tableName_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE tableName
(
  id VARCHAR(11) NOT NULL PRIMARY KEY DEFAULT '0'
);

When you insert data to the tableName table your first id will be IDC00000001 and second IDC00000002 goes like that. I hope it helps!

eisbach
  • 417
  • 3
  • 7