Page 1 of 1

TinyInt Vs Bool

Posted: Fri Feb 24, 2006 2:15 pm
by Benjamin
I created a table and specified that some of the fields be bools. I used phpMyAdmin and after the table was saved these showed up as tinyints instead. I didn't specify a length when I created the bool, so I don't know if that was the problem or not. Another thing is that the tinyints didn't have the unsigned flag. Is that a problem? I just need to store a 0 or 1 (true or false). Why were they changed to tinyints and don't they have to be signed? Should I try changing them to bool?

Posted: Fri Feb 24, 2006 2:17 pm
by josh
http://www.phpbuilder.com/board/showthr ... nextoldest

Found by googling "mysql bool"

Edit:

Just FYI I just use ENUM fields for this type of thing

ENUM('false','true')

You can use 0 / 1 or 'false' / 'true' because mysql lets you specify the # of the option you want to use when assigning an enum a value

Posted: Fri Feb 24, 2006 2:30 pm
by feyd
as it says in the manual and on the page jshpro2 found, BOOL and BOOLEAN are synonymous with TINYINT(1)

Posted: Fri Feb 24, 2006 2:30 pm
by raghavan20
I just figured that out two days earlier....true and false are considered as 1 and 0 in numbers and you just need a space that is large enough to store these which is obviously tinyint(1), with size one which can hold obviously from 0...9 ........... not really intended.... :wink:

Posted: Fri Feb 24, 2006 2:33 pm
by Benjamin
Seems a little misleading that there is a bool option then.

Posted: Fri Feb 24, 2006 2:42 pm
by raghavan20
agtlewis wrote:Seems a little misleading that there is a bool option then.
you are right...i am not also happy in the way it is behaving....i would expect it to take 1 or TRUE and 0 or FALSE.

Posted: Fri Feb 24, 2006 2:44 pm
by feyd
It does. :?

Posted: Fri Feb 24, 2006 2:47 pm
by Benjamin
feyd wrote:It does. :?
But doesn't it also take 2,3,4,5,6,7,8 and 9?

Posted: Fri Feb 24, 2006 3:00 pm
by josh
Yes it does, but just don't use those and you won't have to worry about it. If its really a big deal use my ENUM trick

Posted: Fri Feb 24, 2006 3:09 pm
by Benjamin
Well I design databases under the assumption that the smaller the fields (less data) the faster the queries. So with that in mind, when I want to create a bool field, I would expect it to be 1 bit. A small unsigned int(1) would have to be at least 4 bits but it's probably a byte. When using your enum trick, does that create a 1 bit field? If not then it doesn't matter.

Posted: Fri Feb 24, 2006 3:16 pm
by feyd
You'd expect it to be 1 bit? There's no data type in any language I know that actually is 1 bit. The smallest unit of storage (excluding bitfields and "custom" datatypes) is 1 byte. small unsigned int(1) and tinyint(1) are both 1 byte. They don't break them into nibbles, that'd be silly.

If you want to know how big each type is, read the manual.

Posted: Fri Feb 24, 2006 3:30 pm
by Benjamin
feyd wrote:You'd expect it to be 1 bit?
Yes, if it's possible.
feyd wrote:There's no data type in any language I know that actually is 1 bit.
What about assembly?
feyd wrote:The smallest unit of storage (excluding bitfields and "custom" datatypes) is 1 byte. small unsigned int(1) and tinyint(1) are both 1 byte.
Thats good to know.
feyd wrote:They don't break them into nibbles, that'd be silly.
Even if it increased performance? If you had an extremely large database which could store 8 bools in a 1 byte field it would only use 1/8th the amount of memory. Searching through it would be much faster. Am I wrong?
feyd wrote:If you want to know how big each type is, read the manual.
Either the terminology used in that manual is too complex for me or it's too poorly written for me to understand it. I have tried gathering information from it before, and it's a pain.

Posted: Fri Feb 24, 2006 4:02 pm
by feyd
agtlewis wrote:What about assembly?
Last I checked, asm didn't have an actual bit type. It still operated on bytes, words, doubles and sometimes quads.
agtlewis wrote:Even if it increased performance? If you had an extremely large database which could store 8 bools in a 1 byte field it would only use 1/8th the amount of memory. Searching through it would be much faster. Am I wrong?
Packing data at the bit level is extremely detrimental to performance a huge percentage of the time. I've seen many many situtaions where even packing data on a byte level will bite you. The reason why is it comes down to what the native register size is for the processor you're on. If the processor operates in 32 bits, it's optimized to load 32 bit chunks of data. Even when loading a byte, it loads four, then has to mask out the one you want.
agtlewis wrote:Either the terminology used in that manual is too complex for me or it's too poorly written for me to understand it. I have tried gathering information from it before, and it's a pain.
This is complicated? Pretty straight forward to me.

Posted: Fri Feb 24, 2006 4:26 pm
by timvw
Imho, mysql isn't really good at keeping unwanted data out.. As far as i can remember it always accepted 9999-99-99 as a value for a DATETIME field.. Surprise is even bigger when you notice that it comes out again as 0000-00-00.. So far for data consistency..

Posted: Sat Feb 25, 2006 2:01 am
by raghavan20
I have also never seen mysql to use the constraint not null....

I have a doubt why one cannot specify NULL for any kind of integer field...it defaults to zero....it do not want to assign any value for a field but it defaults to zero...