mysql - 按 information_schema 自动递增

Question

我有一个表成员。我想使用自动增量创建一个用户 ID。我尝试使用以下方法:

SET new.user_id = CONCAT(new.name, LPAD((SELECT AUTO_INCREMENT FROM 
information_schema.TABLES WHERE
TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member'),3,'0'));

但是当我插入一个新成员时，我收到一条错误消息 user_id cannot be null 并且没有添加数据。我希望用户 ID 显示为 jos001 作为主键。

请帮帮我..如何实现？

Answer 1

试试这个 BEFORE INSERT 触发器 -

完整脚本:

CREATE TABLE member(
  user_id VARCHAR(23) NOT NULL,
  name VARCHAR(20) DEFAULT NULL
);

DELIMITER $$

CREATE TRIGGER trigger1
  BEFORE INSERT
  ON member
  FOR EACH ROW
BEGIN

  -- Find next id for new user - MAX+1
  SELECT MAX(CAST(SUBSTRING(user_id, LENGTH(user_id) - 2, 3) AS SIGNED)) + 1 INTO @next_id
    FROM member WHERE SUBSTRING(user_id, 1, LENGTH(user_id) - 3) = NEW.name;

  SET NEW.user_id = CONCAT(NEW.name, LPAD(IFNULL(@next_id, 1), 3,'0'));
END$$

DELIMITER ;

插入一些记录:

INSERT INTO member VALUES('', 'jos');
INSERT INTO member VALUES('', 'jos');
INSERT INTO member VALUES('', 'jos');
INSERT INTO member VALUES('', 'alex');
INSERT INTO member VALUES('', 'alex');
INSERT INTO member VALUES('', 'jos');

检查结果:

SELECT * FROM member;

+---------+------+
| user_id | name |
+---------+------+
| jos001  | jos  |
| jos002  | jos  |
| jos003  | jos  |
| alex001 | alex |
| alex002 | alex |
| jos004  | jos  |
+---------+------+