Postgres to MySQL row ID

nlafferty has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to figure out how to rewrite a program that I made for Postgre. We recently Dropped it in favor of MySQL. My program uses the OID field throughout the program in SELECT and INSERT statements. From what I gather MySQL doen't support the row id concept. So i'm trying to figure out how to emulate it. Help much appreciated. Thanks

Comment on Postgres to MySQL row ID

Replies are listed 'Best First'.
Re: Postgres to MySQL row ID by Zed_Lopez (Chaplain) on Aug 13, 2004 at 18:39 UTC
Easiest thing I can think of would be to add an oid column to your tables as an auto_increment field (and make it an index.) Then you've got your unique id per row, and after an insert you can get it from $sth->{mysql_insertid} or $dbh->{mysql_insertid}.	[reply]
Re: Postgres to MySQL row ID by waswas-fng (Curate) on Aug 13, 2004 at 18:54 UTC
lol, what are you using the oid for? Postgres OID's are not forced unique -- they will wrap (and dup) eventually. If you have a lot of inserts and deletes I am surprised you have not been caught by that already. The post above has the correct solution -- just wanted you to be aware of your design flaw. -Waswas	[reply]
Re^2: Postgres to MySQL row ID by nlafferty (Scribe) on Aug 13, 2004 at 19:10 UTC
Thanks, Luckily the design had not yet been implemented. What does the above mean, Make it and index? And will the autoincrement field be given a value for every row created without specifying it to do so?	[reply]
Re^3: Postgres to MySQL row ID by waswas-fng (Curate) on Aug 13, 2004 at 19:19 UTC
You will need to read up on mysql before you dig to far into this project. auto inc type columns will auto increment the value for new inserts this combined with defining the column as unique and indexing will give you what you want. -Waswas	[reply]