Re: mySQL integer types
Hi Bob, I don't think that's true. I think you can have it not autoincrement and supply the primary key value yourself when you insert a row. In fact, you have to do that if the primary key is one of the text data types since autoincrement doesn't make any sense in that context. Pete On Mon, Jan 16, 2012 at 1:25 PM, Bob Sneidar b...@twft.com wrote: It's my understanding that a primary key MUST be AI in mySQL. A friend of mine gave me a query that will find the next available value in a column of numbers: -- Pete Molly's Revenge http://www.mollysrevenge.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: mySQL integer types
Oh right duh that makes sense. I have no idea where I got that. Bob On Jan 17, 2012, at 11:47 AM, Pete wrote: Hi Bob, I don't think that's true. I think you can have it not autoincrement and supply the primary key value yourself when you insert a row. In fact, you have to do that if the primary key is one of the text data types since autoincrement doesn't make any sense in that context. Pete On Mon, Jan 16, 2012 at 1:25 PM, Bob Sneidar b...@twft.com wrote: It's my understanding that a primary key MUST be AI in mySQL. A friend of mine gave me a query that will find the next available value in a column of numbers: -- Pete Molly's Revenge http://www.mollysrevenge.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: mySQL integer types
INT(64) is a 64-bits integer, so 8 instead of 64 bytes :-) Still, that limit should keep you safe for some time to come... Jan Schenkel. = Quartam Reports PDF Library for LiveCode www.quartam.com = As we grow older, we grow both wiser and more foolish at the same time. (La Rochefoucauld) From: Bob Sneidar b...@twft.com To: How to use LiveCode use-livecode@lists.runrev.com Sent: Monday, January 16, 2012 8:14 PM Subject: mySQL integer types 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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: mySQL integer types
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 b...@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@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 ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: mySQL integer types
On Jan 16, 2012, at 12:56 PM, Pete wrote: 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. It's my understanding that a primary key MUST be AI in mySQL. A friend of mine gave me a query that will find the next available value in a column of numbers: put select l. theColumnName + 1 as start cr \ from theTable as l cr \ left outer join theTable as r on l. theColumnName \ + 1 = r. theColumnName cr \ where r. theColumnName is null; into theSQL I do not pretend to know what that means. But it works! I know you can update a primary key with something other than the next incremental value, so long as it is unique. I suppose if you lock the table first, get the next unique value, update the primary key, unlock the table, then select with that primary key, that would accomplish the same thing, but I believe that the next incremental value gets updated anyway, so if you use that method, you can't go back to allowing mySQL to increment without a gap in your sequence. In other words it's a one way street, and I am not sure that mySQL won't still throw an error anyways once it reaches it's max on the value, so the point may be moot. Bob ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: mySQL integer types
I remember in Trevor's original DB abstraction library (before SQL Yoga) he used a manual indexing method, instead using a special table of indexes for each table that is used. That allowed the changing the actual value of the keys if needed. Automatic indexing is a feature that is not mandatory, but convenient. On 16 January 2012 13:25, Bob Sneidar b...@twft.com wrote: On Jan 16, 2012, at 12:56 PM, Pete wrote: 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. It's my understanding that a primary key MUST be AI in mySQL. A friend of mine gave me a query that will find the next available value in a column of numbers: put select l. theColumnName + 1 as start cr \ from theTable as l cr \ left outer join theTable as r on l. theColumnName \ + 1 = r. theColumnName cr \ where r. theColumnName is null; into theSQL I do not pretend to know what that means. But it works! I know you can update a primary key with something other than the next incremental value, so long as it is unique. I suppose if you lock the table first, get the next unique value, update the primary key, unlock the table, then select with that primary key, that would accomplish the same thing, but I believe that the next incremental value gets updated anyway, so if you use that method, you can't go back to allowing mySQL to increment without a gap in your sequence. In other words it's a one way street, and I am not sure that mySQL won't still throw an error anyways once it reaches it's max on the value, so the point may be moot. Bob ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode -- Stephen Barncard San Francisco Ca. USA more about sqb http://www.google.com/profiles/sbarncar ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
