How to Delete Duplicate Rows and Add a Composite Primary Key in MySQL Without ROWID?

Alternative to Oracle's ROWID in MySQL for Deleting Duplicate Values and Imposing Composite Primary Key

Oracle's ROWID provides a unique identifier for each row in a table. In MySQL, however, there is no exact equivalent to ROWID.

For the provided query:

delete from my_table where rowid not in (select max(rowid) from my_table group by field1,field2)

which aims to delete duplicate rows based on specific columns and then impose a primary key, there are different approaches in MySQL.

Temporary Table and Unique Identifier: One way to achieve the desired result is by utilizing a temporary table. Create a temporary table with a unique row identifier (e.g., some_row_uid) and insert the desired data into it. Then, join the original table with the temporary table to identify and delete the duplicate rows.

Session Variables: Alternatively, session variables can be used to generate a running count for each row, effectively creating a pseudo-ROWID. However, this approach may face limitations when manipulating the same table in subqueries.

Example Query Using Temporary Table (MySQL 8 syntax):

CREATE TEMPORARY TABLE duplicates AS
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 my_table t, (SELECT @rowid:=NULL, @f1:=NULL, @f2:=NULL) as init
ORDER BY field1, field2 DESC;

DELETE my_table FROM my_table
INNER JOIN duplicates ON my_table.some_row_uid = duplicates.some_row_uid
WHERE duplicates.rowid > 0;

ALTER TABLE my_table ADD PRIMARY KEY (field1, field2);

This approach may introduce some overhead due to creating and manipulating the temporary table. For one-time operations, it should provide a reasonable solution.

The above is the detailed content of How to Delete Duplicate Rows and Add a Composite Primary Key in MySQL Without ROWID?. For more information, please follow other related articles on the PHP Chinese website!