mySQL integer types

[Pete]

Hi Bob,
Int(64) refers to the external display of a column, not its internal
storage space.  As you mentioned INT takes 4 bytes of storage but its
external display format can be up 10 chars.

I guess if you're really concerned about it, you could use BIGINT but there
is no theoretical software limit to the number of rows in a mySQL table
(maybe hardware would determine it) so you can make it highly unlikely that
you'll run out of primary key values but not impossible!

In SQLite, if you define an INTEGER PRIMARY KEY column with AUTOINCREMENT
keyword, primary key values are always allocated as the next highest value
for the table, but if you omit the AUTOINCREMENT, then values freed up by
deleted rows may get assigned.  I don't know if this is how mySQL works.

Resetting the autoincrement value of a table seems like it would be
extremely dangerous and possibly corrupt your database.

Pete

On Mon, Jan 16, 2012 at 11:14 AM, Bob Sneidar <bobs at twft.com> wrote:

> Hi all.
>
> I am a little bit concerned with defining integer types. The manual
> defines INT as using 4 bytes for storage, for a maximum of 4294967296
> values. However, I read somewhere (possibly here) that for auto
> incrementing keys I should use int(64) the maximum allowed. Does that mean
> that my storage for these values will use 64 bytes for each record? That
> seems like overkill of overkill.
>
> I do however want to ensure that no matter how long my application runs I
> will never exceed the maximum value in an auto-incrementing column. There
> has to be some kind of balance here. Any ideas? I have tried looking for
> information on ways to reset the AI value of a table, but it seems by all
> accounts this is not allowed. I had hoped that if I did so, mySQL would
> simply find the lowest unused value each time, but I guess it doesn't work
> that way.
>
> I can do that myself with a query, but the simple way would be to make
> sure I have enough values that I will never run out.
>
> Bob
>
>
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>