Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


14.6.5 InnoDB での AUTO_INCREMENT 処理

InnoDB では、AUTO_INCREMENT カラムを含むテーブルに行を挿入する SQL ステートメントの拡張性およびパフォーマンスが大幅に改善される最適化が提供されています。InnoDB テーブルで AUTO_INCREMENT メカニズムを使用するには、テーブルで最大カラム値を取得するインデックス SELECT MAX(ai_col) ルックアップと同等の操作を実行できるように、AUTO_INCREMENT カラム ai_col をインデックスの一部として定義する必要があります。一般に、これはカラムをどこかのテーブルインデックスの 1 番目のカラムにすることで実現されます。

このセクションでは、InnoDB の自動インクリメントロックの元の (従来の) 実装に関する背景情報を提供し、構成可能なロックメカニズムについて説明し、このメカニズムを構成するためのパラメータを示し、その動作やレプリケーションとの相互作用について説明します。


User Comments
  Posted by Ap Muthu on October 20, 2011
Currently we cannot avoid the AUTO_INCREMENT=XXX in mysqldumps which is essential if only the schema is dumped for use in new dbs.

The following bug has yet to be addressed:
http://bugs.mysql.com/bug.php?id=20786

It is nicely summarised with a post operative fix:
http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/

mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > <filename>.sql

  Posted by Thomas Mayer on January 20, 2012
As InnoDb forgets its highest auto_increment after server restart, you can set it again, if you have stored it anywhere. This happens often if you archive your data in an archive table and then delete it and then restart mysql. When archiving again this will result in duplicate key entries.

To work around this you can create a trigger which makes sure your auto_increment is higher than the auto_increment of your archive table:

delimiter //
drop trigger if exists trigger_autoinc_tbl;
CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
declare auto_incr1 BIGINT;
declare auto_incr2 BIGINT;
SELECT AUTO_INCREMENT INTO auto_incr1 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl';
SELECT AUTO_INCREMENT INTO auto_incr2 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl_archiv';
IF (auto_incr2 > auto_incr1 and NEW.id<auto_incr2) THEN
SET NEW.id = auto_incr2;
END IF;
END;//
delimiter ;

Further reading: http://www.slicewise.net/index.php?id=82
  Posted by Stan Gor on June 12, 2012
work around for innodb auto_increment on multiple-column as last column auto_increment
Use additional counter table, and use BEFORE INSERT Trigger
also you can create Trigger on BEFORE update to watch for increment of `f2` if you do updates on `1test`.`f2`

################
CREATE TABLE `1test` (
`f1` int(10) unsigned NOT NULL,
`f2` int(10) unsigned NOT NULL default '0',
`data_f3` int(10) unsigned NOT NULL,
PRIMARY KEY (`f1`,`f2`)
) ENGINE=InnoDB;
####################################
CREATE TABLE `1test_counter` (
`f1` int(10) unsigned NOT NULL,
`f2` int(10) unsigned default NULL,
PRIMARY KEY (`f1`)
) ENGINE=InnoDB;

####################################
DELIMITER ///

DROP TRIGGER `1test_c`///
CREATE TRIGGER 1test_c
BEFORE INSERT ON `1test`
FOR EACH ROW BEGIN

IF NEW.`f2` IS NULL OR NEW.`f2` = 0 THEN
UPDATE `1test_counter`
SET `f2` = `f2` + 1, NEW.`f2` = `f2`
WHERE `f1` = NEW.`f1`
LIMIT 1;

IF ROW_COUNT() = 0 THEN
INSERT INTO `1test_counter`(`f1`, `f2`)
VALUES (NEW.`f1`, 1);
SET NEW.`f2` = 1;
END IF;
ELSE
INSERT INTO `1test_counter`(`f1`, `f2`)
VALUES (NEW.`f1`, NEW.`f2`)
ON DUPLICATE KEY UPDATE
`f2` = IF (VALUES(`f2`) > `f2`, VALUES(`f2`), `f2`);
END IF;
END;
///

DELIMITER ;

Sign Up Login You must be logged in to post a comment.