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
Peter
2
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