2 Columns AUTO-INCREMENT emulation in InnoDB

Hi,

In MyISAM you can set 2 columns to have auto-increment properties.

So, in a messaging system you could have:

user_id, message_id

INSERT INTO messages (user_id, message) VALUES (1, ‘My Message’);
INSERT INTO messages (user_id, message) VALUES (1, ‘My Message’);
INSERT INTO messages (user_id, message) VALUES (1, ‘My Message’);

A SELECT * would produce

user_id, message_id
1,1
1,2
1,3

InnoDB does not currently support this, so I’ve done the following work around:

INSERT INTO messages (user_id, message_id, message) VALUES (1, MAX(message_id), ‘My Message’) WHERE user_id=1;

Are there any known problems with self-implementing support this way?

It appears to be working fine in the development enviroment, however with thousands of users… might be a different story!

Cheers

I assume you mean:

INSERT INTO messages values(1,(select max(message_id)+1 from messages where user_id=1),‘message’)

This is actually similar to what MyISAM does to provide two column auto_increment.

This should work if subselect in insert statement is executed using read-commited repeatable mode, which it should be.

Also if you have enough users it is better than global auto_increment from contention standpoint.

Yes the actual query is like:

INSERT INTO messages (author, recipient, id, subject, body, checksum, timestamp) SELECT $user[id], @tmp:=author, (SELECT MAX(id) + 1 FROM messages WHERE …

Problem is you can’t do a subselect from the same table your inserting into in mysql. It would be nice if you could but it’s currently not supported.

[B]rhuddleston wrote on Sun, 20 August 2006 15:24[/B]
Problem is you can't do a subselect from the same table your inserting into in mysql. It would be nice if you could but it's currently not supported.

The above query (in it’s entirety) works perfectly in MySQL 5.0.22.

Hello everyone. Nice to meet you all. D

[COLOR=#F4F4F4]pret auto