You insert the row, and then make a "SELECT last_insert_id()" call - this returns the value of the last auto-increment generated via an insert for the connection (so some other database connection won't overwrite your value). You can then propagate that value into child-records.
This is much easier, and more efficient than either of the two other suggestions.
David
Kenneth Lim wrote:
Hello -
I was wondering if others have had to deal with an Oracle to MySQL migration and how you handled the implementation equivalent of Oracle sequences in MySQL.
Our application uses a bunch of Oracle sequences to keep ID uniqueness for each sequence type. For example, we have:
UserIDSequence NodeIDSequence etc.
When we create new records, we first ask the Oracle sequences for all new IDs. Then we generate a bunch of insert statements and ultimately insert the new records.
We've thought of 3 possible solutions from easiest to difficult
based on our source semantics and amount of work. I would appreciate any helpful insights that others might have.
We are using MySQL 4.1.5-Gamma with the InnoDB engine.
1- Create a single table with a single auto-increment column to hold a system-wide unique ID. Every time we want a unique ID, we insert in this table and get the value with the LAST_INSERT_ID() function.
This approach seems to create a bottleneck at this single table.
2- Create a single table with 2 columns: sequencename, counter. Every time we want a unique ID for a particular sequence, we increment the counter and get it back immediately.
This approach seems to create a bottleneck also. But I would imagine this approach is more costly than solution #1.
3- Redo our semantics by replacing our insert statements and allowing AUTO_INCREMENTed columns to keep the IDs unique.
Thanks for you feedback. -ken
Kenneth Lim Software Engineer Senvid, Inc. 2445 Faber Place, Suite #200 Palo Alto, CA 94303 phone: 650-354-3612 fax: 650-354-8890 email: [EMAIL PROTECTED] http://www.senvid.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
