|
|
Posted by Miles Thompson on 10/19/88 11:31
At 09:37 AM 11/8/2005, Rahul S. Johari wrote:
>Ave,
>
>Is there a way to fix ID sequence in a MySQL table using PHP?
>Basically I have some tables with a lot of records, 1000 to 5000. ID¹ is
>the Primary Key column in these tables, and at the time the code was
>originally written, there weren¹t much verification of data being input
>going on. Thus a lot of junk records were being let in, which had to be
>deleted at some point or another. Now the ID sequence is all screwed up.
>It¹s not in sequence anymore.
>
>Is there a way to fix this?
>
>Thanks,
>
>Rahul S. Johari
Rahul,
What is OEID, the field name?
If it's a numeric key, auto-generated by MySQL, it behaves in two ways,
depending on the table-type.
If the table is MyISAM, the keys remain unique, and do not repeat.
If a record with the key value of 5 is deleted, that number is not re-used.
This is very important if the primary key field is used as a foreign key
(reference) in another table.
For other table types, that number would be re-used, even if the
five thousandth record was being added. And the value is useless as a
foreign key in another table, unless you have programmed cascading deletes.
(Not natively supported in MySQL.)
(Just double-check the MySQL docs on this - I'm 90% certain I'm
right.)
So what does "screwed up" mean? Why is sequence important? All a primary
key does is UNIQUELY IDENTIFY a record. That's all it should do.
Given that, sequence is utterly irrelevant and does not matter.
Once the table is cleaned up you can export it and re-import it and have
MySQL generate new primary key values. But do not, do not, do not rely on
them for sequence or meaning, they are simply lables. Gaps really do not
matter.
If you want to maintain your own ID's, better to maintain a table and
procedure to generate keys. Whenever a key value is needed it is grabbed
from that table, a new key is calculated using whatever algorithm you want,
and it is stored in the key_table. Ideally, before getting the key value
from that table, a lock on the record is checked for, when it is obtained
the value is retrieved, new value calculated and stored, and the lock
released.
HTH - Miles Thompson
Navigation:
[Reply to this message]
|