What are the MySQL Alternatives to Oracle's RowID?

MySQL Equivalent of Oracle's RowID

In MySQL, there is no direct equivalent to Oracle's RowID. However, there are several ways to achieve similar functionality.

One approach is to use session variables and subqueries:

SELECT @rowid:=@rowid+1 as rowid
FROM table1, (SELECT @rowid:=0) as init
ORDER BY sorter_field

This will assign a unique identifier to each row, which can be used for sorting or other purposes.

Another approach to deleting duplicate records is to create a temporary table and perform a join between the temporary table and the original table:

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 approach is recommended when sorting cannot be performed on the table being deleted from in subqueries.

The above is the detailed content of What are the MySQL Alternatives to Oracle's RowID?. For more information, please follow other related articles on the PHP Chinese website!