| 2 Columns AUTO-INCREMENT emulation in InnoDB [message #57] |
Wed, 16 August 2006 06:21  |
 Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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
[Updated on: Wed, 16 August 2006 06:21]
Martin Gallagher | Speeple: The latest news
|
|
|
Members
Search
Help
Register
Login
Home
