MYSQL 表创建，其中列具有默认值（表达式）

Question

我有一个表 Employee(id,name,dept_name)。我希望 id 为字母数字 [dddddaaaaa]，前 5 位数字将自动递增 id，其余 4 个字符将是员工姓名的前 4 个字符。

例如，对于第一个员工 name=John Todd ，Id 的自动递增部分将为 00001。因此 Id 将为00001JOHN。

是否可以为列 Id=(concat(autoincrement,substring(name,4)) 设置默认表达式。

我还在想是否可以在插入 Employee 和触发器后创建触发器 MySql 不允许从触发的触发器更新同一个表。

将更新Employee.Id，但

Answer 1

像这样的模式怎么样？

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

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

这将允许您使用自然主键相当快速地执行查找，同时为您提供人工主键并且不强制非规范化。

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 部分足够唯一来识别用户，因此您根本不需要查询名称。

如果您坚持预先计算，这应该可以进行

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 在某些权限不受您控制的数据库上将不可用。

Answer 2

您可以尝试在 select 语句中连接它，而不是存储自动增量列和 id 列，

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

这样您就不需要触发器