使用默认值(表达式)到列的MYSQL表创建 [英] MYSQL Table creation with default value(expression) to a column

问题描述

我有一张表Employee(id,name,dept_name).我希望ID的前5位字母数字[dddddaaaaa]将是自动递增ID，其余4位字符将是员工姓名的前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.

例如，对于第一个雇员姓名= 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)

这是一种hack，在某些权限不受您控制的数据库上，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.

这篇关于使用默认值(表达式)到列的MYSQL表创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！