複合インデクス(インデクスの構成列が複数ある定義)で、二つ目の列に
auto_incrementを指定できるのは、ストレージエンジンがMyISAMかBDBの場合だけです。
最後にauto_incrementの値を1に戻す方法の検証を行いました。

===================================================================================
                   【MYISAM】
===================================================================================

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES (“dog”),(“cat”),(“penguin”),
(“lax”),(“whale”),(“ostrich”);

mysql> INSERT INTO animals (name) VALUES (“dog”),(“cat”),(“penguin”),
-> (“lax”),(“whale”),(“ostrich”);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM animals;
+—-+———+
| id | name |
+—-+———+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> show create table animals;
+———–+——————————————————————-
| Table | Create Table
+———–+——————————————————————-
| animals | CREATE TABLE `animals` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
mysql>

=================================================================================
MyISAM テーブルと BDB テーブルでは、複合インデックスの2つめのカラムに
AUTO_INCREMENT を指定できます。この場合、AUTO_INCREMENT
カラムで生成される値は、MAX(auto_increment_column)+1) WHERE prefix=given-prefix
として計算されます。これは、データを順序付きのグループに分割する場合に便利です。
=================================================================================

mysql> CREATE TABLE animals02 (
-> grp ENUM(‘fish’,’mammal’,’bird’) NOT NULL,
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (grp,id)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO animals02 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
-> (“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),
-> (“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM animals02 ORDER BY grp,id;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> desc animals02;
+——-+——————————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+——————————+——+—–+———+—————-+
| grp | enum(‘fish’,’mammal’,’bird’) | NO | PRI | NULL | |
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | | NULL | |
+——-+——————————+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> show create table animals02;
+———–+——————————————————————-
| Table | Create Table
+———–+——————————————————————-
| animals02 | CREATE TABLE `animals02` (
`grp` enum(‘fish’,’mammal’,’bird’) NOT NULL,
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`grp`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+———–+——————————————————————-+
1 row in set (0.00 sec)

===================================================================================
                  【INNODB】
===================================================================================

mysql> CREATE TABLE animals03 (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO animals03 (name) VALUES (“dog”),(“cat”),(“penguin”),
-> (“lax”),(“whale”),(“ostrich”);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals03;
+—-+———+
| id | name |
+—-+———+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> show create table animals03;
+———–+——————————————————————-
| Table | Create Table
+———–+——————————————————————-
| animals03 | CREATE TABLE `animals03` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

※ MYISAMだと可能でしたが、INNODBだと作成出来ない。
mysql> CREATE TABLE animals04 (
-> grp ENUM(‘fish’,’mammal’,’bird’) NOT NULL,
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (grp,id)
-> ) ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key
mysql>

※INNODBでは、idのみをPKに設定すると作成できました。

mysql> CREATE TABLE animals04 (
-> grp ENUM(‘fish’,’mammal’,’bird’) NOT NULL,
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> INSERT INTO animals04 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
-> (“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),(“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals04;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| mammal | 1 | dog |
| mammal | 2 | cat |
| bird | 3 | penguin |
| fish | 4 | lax |
| mammal | 5 | whale |
| bird | 6 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

■■■おまけ■■■
INCRIMENTは、TRUNCATEだと”0″からになるがDELETEだと他のDBと同じように
再度1から開始出来ない。どうすれば良いだろうか?

mysql> delete from animals04;
Query OK, 6 rows affected (0.01 sec)

mysql> select * from animals04;
Empty set (0.00 sec)

mysql> INSERT INTO animals04 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
(“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),(“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals04;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| mammal | 7 | dog |
| mammal | 8 | cat |
| bird | 9 | penguin |
| fish | 10 | lax |
| mammal | 11 | whale |
| bird | 12 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> delete from animals04;
Query OK, 6 rows affected (0.01 sec)

mysql> select * from animals04;
Empty set (0.00 sec)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ALTER TABLE `テーブル名` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_INCREMENT =1;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

mysql> ALTER TABLE `animals04` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_
INCREMENT =1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO animals04 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
(“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),(“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

※再び1から開始出来ました。

mysql> select * from animals04;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| mammal | 1 | dog |
| mammal | 2 | cat |
| bird | 3 | penguin |
| fish | 4 | lax |
| mammal | 5 | whale |
| bird | 6 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

auto_increment


━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

MyISAM          MySQLの基本となるタイプ。テーブル単位のロック
BDB(BerkeleyDB) トランザクション,ページ単位のロックが可能
InnoDB        トランザクション,行単位のロック,外部キーが可能

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CREATE TABLE `DB01`.`TABLE007` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`comment` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = MyISAM;

mysql> show create table DB01.TABLE007 \G
*************************** 1. row ***************************
Table: TABLE007
Create Table: CREATE TABLE `TABLE007` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>

mysql> show table status like ‘TABLE007’\G
*************************** 1. row ***************************
Name: TABLE007
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 24
Data_length: 72
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: 4
Create_time: 2009-02-03 10:23:48
Update_time: 2009-02-03 10:28:37
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.13 sec)

mysql>

mysql> ALTER TABLE TABLE007 ENGINE = InnoDB;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
TABLEがINNODBに変換されていることを確認出来ます。
mysql> show table status like ‘TABLE007’\G
*************************** 1. row *******************
Name: TABLE007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 4
Create_time: 2009-02-03 10:34:22
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.12 sec)

mysql>

mysql> select table_name,engine from INFORMATION_SCHEMA.TABLES where TABLE_NAME
=’TABLE007’;
+————+——–+
| table_name | engine |
+————+——–+
| TABLE007 | InnoDB |
+————+——–+
1 row in set (0.26 sec)

mysql>

MYISAM

MYISAM



INNODB

INNODB