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;