TinyInt Vs Bool
Moderator: General Moderators
TinyInt Vs Bool
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?
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
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
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
If you want to know how big each type is, read the manual.
Yes, if it's possible.feyd wrote:You'd expect it to be 1 bit?
What about assembly?feyd wrote:There's no data type in any language I know that actually is 1 bit.
Thats good to know.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.
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:They don't break them into nibbles, that'd be silly.
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.feyd wrote:If you want to know how big each type is, read the manual.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Last I checked, asm didn't have an actual bit type. It still operated on bytes, words, doubles and sometimes quads.agtlewis wrote:What about assembly?
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: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?
This is complicated? Pretty straight forward to me.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.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact: