MYSQL Table creation with default value(expression) to a column

Question

I have a Table Employee(id,name,dept_name).I want the id will alphanumeric [dddddaaaaa] with first 5 digit will be auto increment id and rest 4 char will be the first 4 char of employee name.

For example , for the first employee name=John Todd ,the auto incremented part of the Id will be 00001. And so the Id will be 00001JOHN.

Is it possible to set a default expression to the column Id=(concat(autoincrement,substring(name,4)).

I was also thinking if I Can create a trigger on after insert Employee and the trigger will update the Employee.Id. But MySql does not allow to update the same table from trigger for which trigger got fired.

Please Help.

Novikov · Accepted Answer · 2011-03-08T02:47:25.163

What about a schema like

CREATE TABLE employee
(
employeeid INT PRIMARY KEY AUTO_INCREMENT,
firstname varchar(255)
);

CREATE INDEX part_of_firstname ON employee (firstname(4));

That'll let you perform lookups fairly quickly using your natural primary key, while giving you an artificial primary key and not forcing to denormalize.

EXPLAIN SELECT * FROM EMPLOYEE WHERE EMPLOYEEID = 1 AND FIRSTNAME LIKE 'john%';

+----+-------------+----------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | employee | const | PRIMARY,part_of_firstname | PRIMARY | 4       | const |    1 |       |
+----+-------------+----------+-------+---------------------------+---------+---------+-------+------+-------+

Of course since the 0001 part of the primary key is unique enough to identify the user you need not query the name at all.

If you insist on precalculating this should work

CREATE TABLE employee
(
employeeid INT PRIMARY KEY AUTO_INCREMENT,
specialid VARCHAR(255),
firstname VARCHAR(255)
);

CREATE INDEX employee_specialid ON employee (firstname(4));

DELIMITER ;;
CREATE TRIGGER employeeid_trigger BEFORE insert ON employee
FOR EACH ROW
BEGIN
SET new.specialid = CONCAT(LPAD((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employee'), 4, '0'), SUBSTRING(new.firstname, 1, 4));
END
;;
DELIMITER ;

Testing it:

mysql> insert into employee (firstname) values ('johnathan');
Query OK, 1 row affected (0.04 sec)

mysql> insert into employee (firstname) values ('johnathan');
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee (firstname) values ('johnathan');
Query OK, 1 row affected (0.02 sec)

mysql> select * from employee;
+------------+-----------+-----------+
| employeeid | specialid | firstname |
+------------+-----------+-----------+
|          1 | 0001john  | johnathan |
|          2 | 0002john  | johnathan |
|          3 | 0003john  | johnathan |
+------------+-----------+-----------+
3 rows in set (0.00 sec)

This is kind of a hack, and information_schema won't be available on some DBs where permissions aren't under your control.

Thanks for your answer.This schema is quite impressive considering the performance.But as per my requirement I need to store that alphanumeric Id in the database.Different other application may access the same table, I would like to keep the select query as simple as possible. — Sourabh, Mar 07 '11 at 19:28
I've added a specific solution to your problem, but I'd recommend against it. — Novikov, Mar 08 '11 at 02:47

score 0 · Answer 2 · answered Mar 07 '11 at 18:58

0

You could try concatenating it in your select statement instead of storing an auto increment column and an id column,

SELECT CONCAT(id, substring(name,4) FROM tbl_employee

That way you wouldn't need triggers

answered Mar 07 '11 at 18:58

Eva611

5,964
8
30
38

Thanks for your answer. Actually I have simplified my requirement.The expression for the id is more complex. And the table has millions of data.So adding expression in select may reduce the performance.So I am thinking of generating the Id beforehand. – Sourabh Mar 07 '11 at 19:07

MYSQL Table creation with default value(expression) to a column

2 Answers2

Linked