Techie Weblog

Web Hosting & Network Security Guide

How to auto increment an alphanumeric primary key in MySQL

If you like the post, Please Share

Facebooktwittergoogle_pluspinterestlinkedin

How to auto increment an alphanumeric primary key in

MySQL

How to auto increment an alphanumeric primary key in MySQL

How to auto increment an alphanumeric primary key in MySQL

How to auto increment an alphanumeric primary key in MySQL

While programming with MySQL Database, sometimes we need alphanumeric value as primary key. And apart from that we may need the alphanumeric value to be incremented automatically. Suppose, there is a primary key, say, employeeID, whose value will be emp001, emp002, emp003, …, emp1000, i.e., the alphabet ’emp’ will keep constant but the numerical value will be incremented by 1 automatically.

Now the problem is how to auto increment an alphanumeric primary key in mysql. Basically it can not be done with any command. But we are, after all, programmer and we have to do it by hook or crook.

The solution is very straight forward. We have to make a field which will be automatically incremented by 1, and later we will add the alphabet value with that incremented field to make a new field. In this approach we have to use a TRIGGER to add the alphabet to the numerical value which in illustrated below:

 

MySQL> CREATE TABLE emp(
              id INT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,    
              empid CHAR(6) NOT NULL,
              name CHAR(50) NOT NULL,
              PRIMARY KEY (empid)
              );
MySQL> CREATE TRIGGER emp_auto_id BEFORE INSERT ON emp
       FOR EACH ROW
       SET NEW.empid = CONCAT("EMP",LPAD((SELECT AUTO_INCREMENT 
       FROM information_schema.TABLES 
       WHERE TABLE_SCHEMA = DATABASE() AND 
       TABLE_NAME = 'emp'), 3, '0'));

MySQL> INSERT INTO emp (name) VALUES
       ('Bob'),('Smith'),('Harry');
   

MySQL> SELECT * FROM emp;

This last SQL command will generate the following result:

+------------+---------+
| empid      | name    |
+------------+---------+
|  EMP001    | Bob     |
|  EMP002    | Smith   |
|  EMP003    | Harry   |
+------------+---------+

This is the best way to AUTO INCREMENT an alphanumeric value in MySQL.

***How to auto increment an alphanumeric primary key in MySQL***

Updated: September 6, 2016 — 5:14 pm

The Author

Sukanta Dutta

The author is writing technical blog for last few years. He shares his knowledge on Computer Networks, Database Technologies, Security Aspects of Network and Database etc. He also likes to hear from the reader of this blog to learn more, so he welcomes guest writing for this blog.

Leave a Reply

Your email address will not be published. Required fields are marked *

Techie Weblog © 2015-2016 Frontier Theme