MySQL Autoincrement InnoDB like MyISAM

Question

MyISAM allows very convenient way to create serials. E.g. In the table primary key is id+seq(-uence)

id seq
1  1  insert into table(seq) values(1),(2),(3),(1),(2),(1),(1),(2);
1  2
1  3
2  1
2  2
3  1
4  1
4  2

So logic is when id remains the same untill appears duplicate key, in this case (MyISAM) will increment id.

But when I try use it in InnoDB - doesn't work. Is there a workaround (because I need transactions)?

Thanks.

May be better example from comments to Manual of MySQL Posted by [name withheld] on October 23 2003 8:41pm

create table location
(
    id bigint not null auto_increment, -- "serial" per 4.1
    longitude int,
    latitude int,
    place int,
    primary key(id, longitude, latitude, place)
);

insert into location (longitude, latitude, place)
values (0,0,0), (1,1,1), (2,2,2);

select * from foo;

+----+-----------+----------+-------+
| id | longitude | latitude | place |
+----+-----------+----------+-------+
|  1 |         0 |        0 |     0 |
|  2 |         1 |        1 |     1 |
|  3 |         2 |        2 |     2 |
+----+-----------+----------+-------+


drop table location;

create table location
(
    id bigint not null auto_increment, -- "serial" per 4.1
    longitude int,
    latitude int,
    place int,
    primary key(longitude, latitude, place, id)
);

insert into location (longitude, latitude, place)
values (0,0,0), (1,1,1), (2,2,2), (0,0,0);

select * from location order by id;

+----+-----------+----------+-------+
| id | longitude | latitude | place |
+----+-----------+----------+-------+
|  1 |         0 |        0 |     0 |
|  1 |         1 |        1 |     1 |
|  1 |         2 |        2 |     2 |
|  2 |         0 |        0 |     0 |
+----+-----------+----------+-------+

I'm assuming that you're talking about the feature of MyISAM when the primary key is compound. If that's the case, no - such feature isn't available for InnoDB. Workaround - ditch the auto_increment, write your own procedure for calculating the key - basically, a huge hassle. — Michael J.V., Jun 17 '11 at 13:39

score 3 · Accepted Answer · edited May 23 '17 at 10:08

3

But when I try use it in InnoDB - doesn't work. Is there a workaround (because I need transactions)?

You can work around it using advisory locks and triggers.

See this identical question for PostgreSQL. You'll want to write the MySQL version of the same.

edited May 23 '17 at 10:08

Community

1
1

answered Jun 17 '11 at 13:39

Denis de Bernardy

75,850
13
131
154

Thanks, but I need to insert into transaction into near 20 tables (xml adjacent mapping) - so I need to do transaction management more simple as possible. – Lorenzo Manucci Jun 17 '11 at 13:44
Well, an easier means to do that will be to lock your table as needed. But it'll be slower. – Denis de Bernardy Jun 17 '11 at 13:47

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

This will work:

create table location
(
    id bigint not null auto_increment,
    longitude int,
    latitude int,
    place int,
    primary key(longitude, latitude, place, id)
) ENGINE =myisam;

But this will not:

create table location
(
    id bigint not null auto_increment,
    longitude int,
    latitude int,
    place int,
    primary key(longitude, latitude, place, id)
) ENGINE =innodb;

because:

Disadvantages of MyISAM

No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.

and

Disadvantages of InnoDB

Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.

I found it would work for me in innoDB so long as I made the auto -incrementing field the first part of the primary key. — Gerry, Oct 18 '13 at 04:09

MySQL Autoincrement InnoDB like MyISAM

2 Answers2