Creating a MYSQL table with a default (expression) in a column

Question

I have an Employee(id,name,dept_name) table Employee(id,name,dept_name) . I want the identifier to be alphanumeric [ dddddaaaaa ] with the first 5-digit number, will automatically increment id, and the remaining 4 char will be the first 4 char of the employee name.

For example, for the first employee name = John Todd, the auto-increment part of the identifier will be 00001. And therefore, the identifier will be 00001JOHN .

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

I also thought if I could create a trigger after pasting Employee, and the trigger will update Employee.Id . But MySql does not allow updating the same table from the trigger for which the trigger was launched.

Please, help.

Answer 1

How about a circuit like

 CREATE TABLE employee ( employeeid INT PRIMARY KEY AUTO_INCREMENT, firstname varchar(255) ); CREATE INDEX part_of_firstname ON employee (firstname(4)); 

This will allow you to search fairly quickly using your natural primary key, providing you with an artificial primary key and not causing you 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 part 0001 of the primary key is unique enough to identify the user, you do not need to request a name at all.

If you insist on a preliminary calculation, 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:

 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 a kind of hack, and information_schema will not be available on some databases where permissions are not under your control.

Answer 2

You can try to combine it into your select statement instead of saving the auto-increment column and id column,

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

This way you don't need triggers