How to Auto Increment With Prefix As Primary Key in MySQL

By default, MySQL automatically sets default value of auto increment column to 1 and automatically increments it by 1 for every new row. But sometimes you may need to auto increment primary key column with prefix as primary key in MySQL. In this article, we will learn how to auto increment with prefix as primary key in MySQL.

How to Auto Increment With Prefix As Primary Key in MySQL

Unfortunately, there is no direct or straightforward way to do this. We need to create a separate table for sequencing and also use triggers to add prefix to primary key column. Our primary key will NOT be set to auto increment. We will use an auto increment column in our sequence table. Every time we insert a row in original table, we will insert a row in sequencing table, and use its auto generated primary key field to create the primary key with prefix. We will use this value in our original table.

Let us say you have the following MySQL table. Please note, we have not set primary key column to auto increment.

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

We will need to create a sequence table for this table. In this table, we have set the id column to auto increment.

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Next, we will create a MySQL trigger as shown below.

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

The above trigger is executed every time you insert a new row in table1. Before the insertion takes place, a new row is entered in table1_seq which contains auto increment column. The id value of latest row in table1_seq is concatenated with a fixed string ‘ABC’ as prefix to generate new primary key for table1. This value is used to create new row in table1, along with values of other columns. Please note, new.id stands for primary key column in table1 and last_insert_id() stands for the id value of latest row in table1_seq table.

Here are a couple queries to insert rows in table1.

INSERT INTO Table1 (name) 
VALUES ('John'), ('Jim');

You will get

|      ID | NAME |
------------------
|  ABC001 | John |
|  ABC002 | Jim  |

In this article, we have learnt how to auto increment a column with prefix in MySQL. You can customize it as per your requirement.

How to Auto Increment With Prefix As Primary Key in MySQL

How to Auto Increment With Prefix As Primary Key in MySQL

Related posts:

Leave a Reply Cancel reply