MySQL如何实现每个访客独立的visit_times访问次数序号列

MySQL 原生的 AUTO_INCREMENT 是全局自增逻辑，无法直接实现按指定字段分组自增的需求，你可以根据业务场景选择以下三种方案实现：

方案1：使用 MyISAM 原生分组自增特性（适配你当前的引擎选择） #

MyISAM 引擎本身支持 AUTO_INCREMENT 列作为联合主键的第二列时，按第一列分组自增，你之前的代码问题出在索引顺序、字段定义、表名不统一三个点，修正后的代码如下：

建表语句 #

CREATE TABLE `test`.`visitor_visits` (
  `visitor_ID` INT NOT NULL,
  `visit_times` INT NOT NULL AUTO_INCREMENT,
  `Date` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`visitor_ID`, `visit_times`)
) ENGINE = MyISAM DEFAULT CHARSET=utf8mb4;

注意：联合主键必须把visitor_ID放在第一位，visit_times放在第二位，才能触发分组自增逻辑。

插入语句 #

INSERT INTO `visitor_visits`(`visitor_ID`, `Date`) 
VALUES 
("1", "15/05/2021"),
("1", "20/07/2021"),
("2", "02/09/2021"),
("3", "24/08/2021");

插入后直接查询即可得到你期望的visit_times结果：1、2、1、1。

方案2：InnoDB 引擎+触发器实现（更推荐，适配 MySQL 默认引擎） #

MyISAM 不支持事务、行锁，已经是被淘汰的引擎，如果你用默认的 InnoDB 引擎，可以通过触发器自动计算访问序号：

建表语句 #

CREATE TABLE `test`.`visitor_visits` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '全局自增主键',
  `visitor_ID` INT NOT NULL,
  `visit_times` INT NOT NULL,
  `Date` VARCHAR(10) NOT NULL,
  UNIQUE KEY `uniq_visitor_times` (`visitor_ID`, `visit_times`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;

创建插入前触发器 #

DELIMITER //
CREATE TRIGGER `calc_visit_times` BEFORE INSERT ON `visitor_visits`
FOR EACH ROW
BEGIN
  -- 取当前访客的最大访问序号+1，没有历史记录则默认从1开始
  SELECT COALESCE(MAX(visit_times), 0) + 1 INTO NEW.visit_times
  FROM `visitor_visits`
  WHERE visitor_ID = NEW.visitor_ID;
END //
DELIMITER ;

插入数据的逻辑和方案1完全一致，不需要手动传visit_times值即可自动生成对应序号。

方案3：查询时动态生成（无需修改存量表结构） #

如果你已经有存量数据不想改动表结构，直接用 MySQL 8.0 支持的窗口函数在查询时计算序号即可：

SELECT 
  visitor_ID,
  Date,
  ROW_NUMBER() OVER (PARTITION BY visitor_ID ORDER BY 全局自增主键列名 ASC) AS visit_times
FROM `你的原表名`;

把代码里的全局自增主键列名和你的原表名替换成你实际的字段、表名即可，按全局自增主键排序可以保证序号和插入顺序一致。

内容的提问来源于stack exchange，提问作者OMAR TOUIL