MySQL Forums
Forum List  »  Newbie

Re: How to get row identifier(DB_ROW_ID)
Posted by: Rick James
Date: April 20, 2014 10:31PM

MySQL has a limited set (relative to Oracle) of possible keys:
INDEX (aka KEY) -- just an index; stored separately from the data.
UNIQUE -- INDEX, plus UNIQUEness constraint.
PRIMARY KEY -- Always UNIQUE, always (in InnoDB) 'clustered' with the data, implicitly NOT NULL.

Also, they are always BTree and ASCending. (OK, the MEMORY engine has a HASH, but that may be the only deviation from BTree.)

Without a PRIMARY or UNIQUE key, MySQL provides no way for a user to uniquely identify a row. You can play games with SELECT DISTINCT or maybe GROUP BY in an attempt to find duplicates, but it is up to you to devise a way what works for _your_ dataset.

CREATE TABLE ( ... ) ENGINE=InnoDB -- The PRIMARY KEY is the first available of:
1. a user-specified PRIMARY KEY,
2. the first UNIQUE index that involved all non-NULL fields,
3. an implicitly added hidden 6-byte field -- that number is not exposed to you, the end user.

Transactions must have a way to uniquely identify a row. It will use whichever of the above 3 items is available.

> its has some large number of duplicate records

If, by 'duplicate record', you mean that all fields are duplicated, then
CREATE TABLE new
SELECT DISTINCT * FROM original;
will create a `new` table with all the dups removed. You could (should) then add a PRIMARY KEY of your choosing. This would add an artificial AUTO_INCREMENT id (think SEQUENCE):
CREATE TABLE new (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB
SELECT DISTINCT * FROM original;
(That assumes the table does not already have a column named `id`.)

After CREATEing `new`, you can finish the cleanup thus:
RENAME TABLE original TO old, new TO original;
DROP TABLE old;

Options: ReplyQuote


Subject
Written By
Posted
Re: How to get row identifier(DB_ROW_ID)
April 20, 2014 10:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.