How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | mysql> create table a1(i int unsigned not null auto_increment primary key); Query OK, 0 rows affected (0.01 sec) mysql> create table a2 like a1; Query OK, 0 rows affected (0.00 sec) mysql> insert into a1 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into a2 values(1); Query OK, 1 row affected (0.00 sec) mysql> create table am(i int unsigned not null auto_increment primary key) type=merge union(a1,a2) insert_method=last; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from am; +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec) |
So you can see merge table behaves quite smart. Even though the maximum value in table a2 was 1 it finds out what other subtable had value 2 and assigns the value 3 to the new row.
Let us see how stable merge table auto_increment values are by truncating the table we just inserted data to:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table 'a2' is in use (most likely by a MERGE table). Try FLUSH TABLES. mysql> flush tables; Query OK, 0 rows affected (0.01 sec) mysql> truncate table a2; Query OK, 0 rows affected (0.00 sec) mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from am; +---+ | i | +---+ | 2 | | 3 | +---+ 2 rows in set (0.00 sec) |
As you see newly inserted row got value 3, which is maximum value which remained in the table plus 1. So auto_increment values are reusable just as with old ISAM tables. Such behavior does not only corresponds to TRUNCATE – deleting last row will cause auto_increment value to be reused too.
Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> alter table am auto_increment=1000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table am; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | am | CREATE TABLE `am` ( `i` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`i`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`a1`,`a2`) | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
As you can see Merge table does not even stores auto_increment value (and of course does it without any warnings)
Neither setting auto_increment value for underlying MyISAM tables works:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> alter table a1 auto_increment=100; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table a2 auto_increment=100; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from am; +---+ | i | +---+ | 2 | | 3 | | 4 | +---+ 3 rows in set (0.00 sec) |
So for bad or for good you should remember auto_increment for Merge Tables works differently from both MyISAM and Innodb tables and being similar to what ISAM tables used to have. As the side effect of this – if you’re using auto_increment columns inserting into Merge Table and in the last table directly will have different results.
Another thing I should remind you should be very careful while running ALTER TABLE for underlying tables. Unlike with TRUNCATE TABLE there is no warning displayed but the MERGE TABLE may continue having old file descriptors open not seeing changes done to original table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | mysql> select * from am; +-----+ | i | +-----+ | 2 | | 3 | | 5 | | 10 | | 100 | | 101 | | 102 | | 103 | | 104 | | 105 | +-----+ 10 rows in set (0.01 sec) mysql> alter table a2 auto_increment=5000; Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> delete from a2 where i>2; Query OK, 9 rows affected (0.00 sec) mysql> select * from am; +-----+ | i | +-----+ | 2 | | 3 | | 5 | | 10 | | 100 | | 101 | | 102 | | 103 | | 104 | | 105 | +-----+ 10 rows in set (0.00 sec) mysql> select * from a2; Empty set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select * from am; +---+ | i | +---+ | 2 | +---+ 1 row in set (0.00 sec) |
As you can see delete is invisible in the merge table after we did alter table until we have run flush tables.
I’ve done my tests with MySQL 5.0.62.
