About autoincremental with mysql

Hi,

In mySQL, I have a table with an autoincremental ID,
but when entries were removed, there'are lots of holes in the ID number
list.
(ex: ID : 1, 2, 4, 20, 459, 460,...)
I try to find in php manuel (function mysql) a function to process this
modfication, but I didn't find it.

Do you know how to do this action ?

Thks,
Chris.

On Sun, 18 Jul 2004 19:05:42 +0200, "Chris" <nospam@nospam.com> wrote:

>In mySQL, I have a table with an autoincremental ID,
>but when entries were removed, there'are lots of holes in the ID number
>list.
>(ex: ID : 1, 2, 4, 20, 459, 460,...)

This is expected and correct.

>I try to find in php manuel (function mysql) a function to process this
>modfication, but I didn't find it.
>
>Do you know how to do this action ?

Why would you want to?

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

Thanks for your answer,

I want to reorganize my ID, when you have lots of entries, and lots of
removed, you can arrive to the end of your value (int: 0 -> 4294967295 ) a
day you can obtain the max value for your ID, instead of the reorganization
process, it's less possible.


"Andy Hassall" <andy@andyh.co.uk> a écrit dans le message de
news:qgflf0hdculja0gp4l2sj5f81j19fjap66@4ax.com...
> On Sun, 18 Jul 2004 19:05:42 +0200, "Chris" <nospam@nospam.com> wrote:
>
> >In mySQL, I have a table with an autoincremental ID,
> >but when entries were removed, there'are lots of holes in the ID number
> >list.
> >(ex: ID : 1, 2, 4, 20, 459, 460,...)
>
> This is expected and correct.
>
> >I try to find in php manuel (function mysql) a function to process this
> >modfication, but I didn't find it.
> >
> >Do you know how to do this action ?
>
> Why would you want to?
>
> --
> Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
> http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

On Sun, 18 Jul 2004 20:21:00 +0200 (more or less), "Chris" <nospam@nospam.com>
wrote:

>Thanks for your answer,
>
>I want to reorganize my ID, when you have lots of entries, and lots of
>removed, you can arrive to the end of your value (int: 0 -> 4294967295 ) a
>day you can obtain the max value for your ID, instead of the reorganization
>process, it's less possible.
>

Please don't top post.

What you could do is export the table, drop the table, re-create it, and
reload it.

To dump your databases, use someting like

mysqldump --all-databases > exported.sql


After exporting, edit the file because it will have the ID numbers in it.
Replace the ID with a null value (e.g., '1234' with '') before loading.

If any other tables rely on that ID, you're going to have a mess on your
hands. It might just be easier to change the type of the field you're using as
the ID to a larger (wider) integer.

On Sun, 18 Jul 2004 20:21:00 +0200, "Chris" <nospam@nospam.com> wrote:

>"Andy Hassall" <andy@andyh.co.uk> a écrit dans le message de
>news:qgflf0hdculja0gp4l2sj5f81j19fjap66@4ax.com.. .
>> On Sun, 18 Jul 2004 19:05:42 +0200, "Chris" <nospam@nospam.com> wrote:
>>
>> >In mySQL, I have a table with an autoincremental ID,
>> >but when entries were removed, there'are lots of holes in the ID number
>> >list.
>> >(ex: ID : 1, 2, 4, 20, 459, 460,...)
>>
>> This is expected and correct.
>>
>>>I try to find in php manuel (function mysql) a function to process this
>>>modfication, but I didn't find it.
>>>
>>>Do you know how to do this action ?
>>
>> Why would you want to?
>
>Thanks for your answer,
>
>I want to reorganize my ID, when you have lots of entries, and lots of
>removed, you can arrive to the end of your value (int: 0 -> 4294967295 ) a
>day you can obtain the max value for your ID, instead of the reorganization
>process, it's less possible.

(Have you actually come anywhere near this limit? That's one record every
second for 136 years...)

If you wanted to do this, then first you work out if you have any foreign keys
into the table, then you'd probably just do a select of the data ordered by id,
and keep a counter incremented per row to update the id field in the parent
table and any child tables. Then reset the auto_increment property of the table
at the end.

There's certainly no single PHP function to do this, and I don't think you can
do this in a single statement in MySQL.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

On Sun, 18 Jul 2004 20:49:00 +0100, Andy Hassall <andy@andyh.co.uk>
wrote:

>On Sun, 18 Jul 2004 20:21:00 +0200, "Chris" <nospam@nospam.com> wrote:
>
>>"Andy Hassall" <andy@andyh.co.uk> a écrit dans le message de
>>news:qgflf0hdculja0gp4l2sj5f81j19fjap66@4ax.com. ..
>>> On Sun, 18 Jul 2004 19:05:42 +0200, "Chris" <nospam@nospam.com> wrote:
>>>
>>> >In mySQL, I have a table with an autoincremental ID,
>>> >but when entries were removed, there'are lots of holes in the ID number
>>> >list.
>>> >(ex: ID : 1, 2, 4, 20, 459, 460,...)
>>>
>>> This is expected and correct.
>>>
>>>>I try to find in php manuel (function mysql) a function to process this
>>>>modfication, but I didn't find it.
>>>>
>>>>Do you know how to do this action ?
>>>
>>> Why would you want to?
>>
>>Thanks for your answer,
>>
>>I want to reorganize my ID, when you have lots of entries, and lots of
>>removed, you can arrive to the end of your value (int: 0 -> 4294967295 ) a
>>day you can obtain the max value for your ID, instead of the reorganization
>>process, it's less possible.
>
> (Have you actually come anywhere near this limit? That's one record every
>second for 136 years...)
>
> If you wanted to do this, then first you work out if you have any foreign keys
>into the table, then you'd probably just do a select of the data ordered by id,
>and keep a counter incremented per row to update the id field in the parent
>table and any child tables. Then reset the auto_increment property of the table
>at the end.
>
> There's certainly no single PHP function to do this, and I don't think you can
>do this in a single statement in MySQL.

I agree, it's not worth it, but if you truely wanted to do it anyway,
my suggestion would be to select all the records (ordered by the order
you want them in) and then create an array of all the records. Then,
you could do:

for ($i = 1; $i <= count($data_array); $i++) {
$sql = "INSERT INTO table SET id='$i',"
" var1='" . $data_array['var1'] . "'");
// So on and so forth for every field.
mysql_query($sql);
}

Now, that's just a quick thought, there are better programatic ways of
going about this, but it could be done.

In all honesty, if you are afraid of reaching the cieling of your int
id field, change int to unsigned or even bigint unsigned and get a
much larger base of numbers to work with:

http://dev.mysql.com/doc/mysql/en/Numeric_types.html