使用默认值(表达式)为列创建 MYSQL 表
我有一个表 Employee(id,name,dept_name).我想要 id 是字母数字 [dddddaaaaa]，前 5 位数字将是自动递增的 id 和 rest4 个字符将是员工姓名的前 4 个字符.
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.
例如，对于第一个员工 name=John Todd ，Id 的自动递增部分将为 00001.因此 Id 将为 00001JOHN.
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.
是否可以为列 Id=(concat(autoincrement,substring(name,4)) 设置默认表达式.
Is it possible to set a default expression to the column Id=(concat(autoincrement,substring(name,4)).
我也在考虑是否可以在插入 Employee 后创建触发器，触发器将更新 Employee.Id.但是 MySql 不允许从触发器被触发的触发器更新同一个表.
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.
请帮忙.
推荐答案
像这样的架构怎么样
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 | |
+----+-------------+----------+-------+---------------------------+---------+---------+-------+------+-------+
当然，由于主键的 0001 部分是唯一的，足以识别用户，您根本不需要查询名称.
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 ;
测试:
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)
这是一种黑客行为，并且在某些权限不受您控制的数据库上，information_schema 将不可用.
This is kind of a hack, and information_schema won't be available on some DBs where permissions aren't under your control.
相关文章