Oh yes, I am aware that I am bringing this up again :) I was never quite happy with the end of that last conversation but was accepting of it until I started thinking about how to convert some of my own DB's over to Drizzle and then ran into the IP problem.
To me, 13% is 13% (See Brian's second link). It's not an argument about the disaster of 3-byte integers. It's an argument about using a 8-byte integer when we can store everything we need, ever, in 4-bytes. IPv6 is the future, though there is no real solution in Drizzle for that yet and IPv4 is still going to be around probably for the near and medium future. I'm not saying unsigned ints should be reintroduced or anything; I'm simply saying there is a gap for efficiently and easily storing IPs. I've been playing with use of binary types but haven't gotten very far. First, did binary() go away? drizzle> create table t3 (num1 serial, ip binary(4)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'binary(4))' at line 1 Second, if I use a varbinary(), I must be doing something wrong because: drizzle> create table t3 (num1 serial, ip varbinary(4)); Query OK, 0 rows affected (0 sec) drizzle> INSERT INTO t3 (ip) VALUES (4294967295); ERROR 1406 (22001): Data too long for column 'ip' at row 1 drizzle> INSERT INTO t3 (ip) VALUES (4294967294); ERROR 1406 (22001): Data too long for column 'ip' at row 1 drizzle> INSERT INTO t3 (ip) VALUES (12345); ERROR 1406 (22001): Data too long for column 'ip' at row 1 I'm assuming I'm doing it wrong :) But if I can't do it, some clueless PHP developer surely won't do it either (no offense to the clueful PHP developers that might be on this list). I just see the end result here being even fewer people storing IPs as integers and using varchar instead. That sucks, particularly in a UTF8 world. I could be appeased with an IP type (and perhaps a serial type, since the lack of unsigned for auto_increments is something I still find bothersome) but I'm just pointing out that this issue might be something that need be addressed at some point and to point out at least one casualty where throwing away unsigned could cause some confusion. Tim S. On Mar 26, 2010, at 12:10 AM, Brian Moon wrote: > Heh, you know, this all started sounding really familiar. > > Your post about this in 2008 Tim: > https://lists.launchpad.net/drizzle-discuss/msg02584.html > > And my reply about the not so wasted storage by InnoDB with BIGINT vs. INT. > > https://lists.launchpad.net/drizzle-discuss/msg02584.html > > Brian. > -------- > http://brian.moonspot.net/ > > On 3/25/10 4:03 PM, Tim Soderstrom wrote: >> The problem is that IPv4 is only 32-bits and will always only ever be >> 32-bits. So storing something that will only ever be 32-bis into 64-bit >> space seems a bit silly. Perhaps I'm over-optimizing or jumping the gun (say >> before someone somewhere opts to make an IP type :) but seems somewhat >> severe. I was thinking about how to get around it by using a binary column >> or something like that but haven't quite figured that one out yet. >> >> >> >> On Mar 25, 2010, at 3:42 PM, Brian Moon wrote: >> >>> What is wrong with BIGINT? Are you looking to have your database do >>> constraint checking on your data? >>> >>> Brian. >>> -------- >>> http://brian.moonspot.net/ >>> >>> On 3/25/10 3:19 PM, Tim Soderstrom wrote: >>>> I think this came up a while ago, but the lack of unsigned integers >>>> has been bugging me. I know there is or will be a way to do pluggable >>>> types in Drizzle but until then, for applications that rely on using >>>> unsigned INT for IPs, the only work-around I can think of is to use a >>>> BIGINT. Which is too big for an IPv4 but not big enough for IPv6. >>>> AAAH! :) >>>> >>>> Thoughts on some ways around that? I fear people will do the naughty >>>> and start storing IPs as varchars which is quite slow by comparison. >>>> >>>> Tim S. _______________________________________________ Mailing list: >>>> https://launchpad.net/~drizzle-discuss Post to : >>>> [email protected] Unsubscribe : >>>> https://launchpad.net/~drizzle-discuss More help : >>>> https://help.launchpad.net/ListHelp >>> >> > _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
