更新版本：仅使用一个
id字段。这很可能不是原子的，因此如果需要并发，请在事务内部使用：
http://sqlfiddle.com/#!2/a4ed8/1
CREATE TABLE IF NOT EXISTS person ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ( id )) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TRIGGER insert_kangaroo_id BEFORE INSERT ON person FOR EACH ROW BEGIN DECLARE newid INT; SET newid = (SELECt AUTO_INCREMENT FROM information_schema.TABLES WHERe TABLE_SCHEMA = DATAbase() AND TABLE_NAME = 'person' ); IF NEW.id AND NEW.id >= newid THEN SET newid = NEW.id; END IF; SET NEW.id = 5 * CEILING( newid / 5 );END;
旧的，无效的“解决方案”（插入前触发器无法看到当前的自动增量值）：
http://sqlfiddle.com/#!2/f4f9a/1
CREATE TABLE IF NOT EXISTS person ( secretid INT NOT NULL AUTO_INCREMENT, id INT NOT NULL, PRIMARY KEY ( secretid )) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATE TRIGGER update_kangaroo_id BEFORE UPDATE ON person FOR EACH ROW BEGIN SET NEW.id = NEW.secretid * 5;END;CREATE TRIGGER insert_kangaroo_id BEFORE INSERT ON person FOR EACH ROW BEGIN SET NEW.id = NEW.secretid * 5; -- NEW.secretid is empty = unusuable!END;
