How to auto increment an alphanumeric primary key in
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.