MySQL历史表；丢失修订顺序

Question

我不久前就实现了这个解决方案。 https://stackoverflow.com/a/12657012/112680直到昨天为止，它一直工作得很好。我添加了几列，并且我相信索引(我在没有问题之前就已经完成了)，现在它不会对每个 eventid 进行像 1,2,3 这样的修订，它只是每行递增。我与正在运行的内容进行了比较，除了自动增量起始值之外，我没有看到任何差异。

主表上的我的插入后触发器

thisTrigger: BEGIN
IF ((@TRIGGER_CHECKS = FALSE)
  OR (@TRIGGER_AFTER_INSERT_CHECKS = FALSE))
  AND (USER() = xxx)
THEN
  LEAVE thisTrigger;
END IF;

INSERT INTO eventshistory 
  SELECT 'initial booking', NULL, UTC_TIMESTAMP(), e.* 
  FROM events AS e 
  WHERE e.eventid = NEW.eventid;
END

更新后触发器

thisTrigger: BEGIN
IF ((@TRIGGER_CHECKS = FALSE)
  OR (@TRIGGER_AFTER_UPDATE_CHECKS = FALSE))
  AND (USER() = xxxx)
THEN
  LEAVE thisTrigger;
END IF;

INSERT INTO eventshistory SELECT 'update', NULL, UTC_TIMESTAMP(), e.*
  FROM events AS e WHERE e.eventid = NEW.eventid;
END

DDL

CREATE TABLE eventshistory (
  action VARCHAR(15) NOT NULL DEFAULT 'insert'
 ,revision INT(6) NOT NULL AUTO_INCREMENT
 ,actiondate DATETIME DEFAULT NULL
 ,eventid INT(11) NOT NULL
 ,franchiseid INT(11) NOT NULL
 ,territoryid INT(11) DEFAULT NULL
 ,customerinfoid INT(11) DEFAULT NULL
 ,eventaddressid INT(11) DEFAULT NULL
 ,eventstartdate DATETIME DEFAULT NULL
 ,eventenddate DATETIME DEFAULT NULL
 ,createdon DATETIME DEFAULT NULL
 ,createdby INT(11) DEFAULT NULL
 ,updatedon DATETIME DEFAULT NULL
 ,updatedby INT(11) DEFAULT NULL
 ,attendeecount SMALLINT(6) DEFAULT NULL
 ,onhold TINYINT(1) NOT NULL DEFAULT 0
 ,holduntildate DATETIME DEFAULT NULL
 ,canceledon DATETIME DEFAULT NULL
 ,canceledby INT(11) DEFAULT NULL
 ,isquotesent TINYINT(1) NOT NULL DEFAULT 0
 ,quoteexpireson DATETIME DEFAULT NULL
 ,haspriceoverride TINYINT(1) NOT NULL DEFAULT 0
 ,pretaxtotal DECIMAL(8,2) DEFAULT NULL
 ,couponcodeid INT(11) DEFAULT NULL
 ,eventnotes VARCHAR(500) DEFAULT NULL
 ,discounttype CHAR(1) DEFAULT NULL COMMENT 'P for percentage, F for fixed dollar'
 ,discountamount DECIMAL(8,2) DEFAULT NULL
 ,istaxexempt TINYINT(1) NOT NULL DEFAULT 0
 ,videogameratingid INT(11) DEFAULT NULL
 ,quotedon DATETIME DEFAULT NULL
 ,tax DECIMAL(8,2) DEFAULT NULL
 ,receiptnotes VARCHAR(500) DEFAULT NULL
 ,outstandingbalance DECIMAL(8,2) DEFAULT NULL
 ,flatparkingsetup TINYINT(1) DEFAULT NULL
 ,honoreename VARCHAR(50) DEFAULT NULL
 ,HonoreeAge VARCHAR(10) DEFAULT NULL
 ,honoreegender CHAR(1) DEFAULT NULL
 ,referralsourceid INT(11) DEFAULT NULL
 ,eventtypeid INT(11) DEFAULT NULL
 ,pricingmodel CHAR(11) DEFAULT NULL COMMENT 'Hourly 1, Hourly 2 etc.'
 ,depositamt DECIMAL(8,2) DEFAULT NULL
 ,isquoteconverted TINYINT(1) DEFAULT NULL
 ,status VARCHAR(25) DEFAULT NULL
 ,activitiestotal DECIMAL(8,2) DEFAULT NULL
 ,coupontotal DECIMAL(8,2) DEFAULT NULL
 ,overridediff DECIMAL(8,2) DEFAULT NULL
 ,refunddue DECIMAL(8,2) DEFAULT NULL
 ,bookeddate DATETIME DEFAULT NULL
 ,cancelationfee DECIMAL(8,2) DEFAULT NULL
 ,eventtotal DECIMAL(8,2) DEFAULT NULL
 ,discounttotal DECIMAL(8,2) DEFAULT NULL
 ,g2uwareeid INT(11) DEFAULT NULL
 ,islocked TINYINT(1) DEFAULT NULL
 ,lockeddate DATETIME DEFAULT NULL
 ,isQuotePaid TINYINT(1) DEFAULT NULL
 ,taxrate DECIMAL(6,5) DEFAULT 0.00000
 ,quotepaidpct TINYINT(4) UNSIGNED DEFAULT NULL
 ,cancelationfeecash DECIMAL(8,2) DEFAULT NULL
 ,canceltype CHAR(1) DEFAULT NULL
 ,isguaranteed TINYINT(1) DEFAULT 0
 ,commission DECIMAL(6,2) DEFAULT 0.00
 ,commissionreportedbooked TINYINT(1) NOT NULL DEFAULT 0
 ,commissionreportedcompleted TINYINT(1) NOT NULL DEFAULT 0
 ,CommissionableCost DECIMAL(8,2) NOT NULL DEFAULT 0.00
 ,CouponType CHAR(1) DEFAULT NULL
 ,CouponCode VARCHAR(25) DEFAULT NULL
 ,CouponAmount DECIMAL(6,2) DEFAULT NULL
 ,PRIMARY KEY (eventid,revision)
 ,INDEX IDX_eventshistory_franchiseid (franchiseid)
 ,INDEX UK_eventshistory (status)
 ,INDEX UK_eventshistory2 (canceledon)
 ,INDEX UK_eventshistory3 (revision,eventid,franchiseid)
)
ENGINE = MYISAM
AUTO_INCREMENT = 235
AVG_ROW_LENGTH = 251
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

Answer 1

所以，RTM... https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

我刚刚添加了一个包含 PK 的新索引。

同样对于自动增量重置，MySQL为MyISAM声明

you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one

但是当我将其重置为 1000 时，它实际上又回到了 1。所以这是个好消息。