Re: Oracle ROWID equivalent in MySQL

[David L Lambert]

(I see it's been a while since you posted this. Sorry if it's too late.)

No, MySQL doesn't expose a ROWID type like Oracle does. It wouldn't even be applicable for some storage engines. However, I can think of a couple of ways to solve your problem:

1. If the table has some sort of timestamp or transaction-ID column that usually identifies only one or a few rows, create a non-unique index on that column. The DELETE statement will use index lookup to find the rows.

2. If you know this won't break anything else that uses the table (no code is doing an INSERT without specifying columns, nor SELECT * FROM ...), add a new primary key column, AUTO_INCREMENT. Reference that key in the data-extraction code.

3. If neither of those is an option (you lack permissions, or the table is on a storage engine like CSV that doesn't support indexes), create a new table with the index or surrogate key, INSERT INTO new_table (...) SELECT ... FROM old_table, and proceed with the ETL operation out of the new table.

4. Better yet, you probably don't need to delete individual the rows in the table at all. Just get all the data out of it, then DELETE without a WHERE clause.