按information_schema自动递增

Question

我有一张member桌子。我想使用自动增量来创建一个用户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

在插入触发器之前尝试此操作-

完整脚本：

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  |
+---------+------+

Answer 2

您正在执行您提供的查询中所示的更新/触发器。

更新将在插入之后工作。如果触发器在同一数据库上，则触发器将不起作用(因为它可能会以无限循环结束)

您应该做的是，在尝试插入之前，使用您使用的任何编程语言执行此操作。在执行插入操作时，准备好要插入的名称。