[MySQL] 解决mysql的int型主键自增问题

CREATE TABLE test1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)

INSERT INTO test1 VALUES(NULL,'小牛');
INSERT INTO test1 VALUES(NULL,'大牛');

INSERT INTO test1 VALUES(2147483647 ,'小华')

INSERT INTO test1 VALUES(NULL,'母牛');

INSERT INTO test1 VALUES(NULL,'母牛');

-- 生成32位UUID
SELECT REPLACE(UUID(),'-','') AS UUID;

CREATE TABLE test2(
  id VARCHAR(50) PRIMARY KEY,
  NAME VARCHAR(20) NOT NULL
)

-- 插入UUID
INSERT INTO test2 VALUES(REPLACE(UUID(),'-',''),'老王');

-- 创建触发器
DELIMITER $$
CREATE
TRIGGER auto_id    -- 名称
BEFORE INSERT      -- 触发时机
ON test2 FOR EACH ROW   -- 作用于test2表，对每行数据生效
BEGIN
IF new.id = '' THEN   -- 当id为空字符串时设置UUID
  SET new.id = REPLACE(UUID(),'-','');
END IF;
END$$

-- 插入一条数据
INSERT INTO test2 VALUES('','小王');