How to Simulate Oracle's ROWID Functionality in MySQL?

MySQL Equivalent of Oracle's RowID

In MySQL, there is no direct equivalent to Oracle's rowid. However, it's possible to achieve similar functionality using session variables and subqueries.

To delete duplicate rows and enforce a composite primary key, you can utilize the following approach:

SELECT @rowid:=@rowid+1 as rowid
FROM my_table, (SELECT @rowid:=0) as init
ORDER BY field1, field2

This query will assign rowids to the table, but note that subqueries cannot sort tables you're trying to delete from.

To address this limitation, create a temporary table, insert the ranging subquery into it, and delete from the original table based on a join with the temporary table using a unique row identifier:

CREATE TEMPORARY TABLE duplicates ...

INSERT INTO duplicates (rowid, field1, field2, some_row_uid)
SELECT
  @rowid:=IF(@f1=field1 AND @f2=field2, @rowid+1, 0) as rowid,
  @f1:=field1 as field1,
  @f2:=field2 as field2,
  some_row_uid
FROM testruns t, (SELECT @rowid:=NULL, @f1:=NULL, @f2:=NULL) as init
ORDER BY field1, field2 DESC;

DELETE FROM my_table USING my_table JOIN duplicates
  ON my_table.some_row_uid = duplicates.some_row_uid AND duplicates.rowid > 0

This method should work for your one-time operation without significant overhead.

The above is the detailed content of How to Simulate Oracle's ROWID Functionality in MySQL?. For more information, please follow other related articles on the PHP Chinese website!