Forum Moderators: open

Message Too Old, No Replies

ENUM or TINYINT?

         

csdude55

1:52 am on Apr 19, 2019 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



I have a boolean field that's always going to either be 1 or NULL (yes, a null instead of 0, mainly because I wrote a lot of scripts over the years that include IS NULL).

Do you guys think that I should make this column a TINYINT (1), or an ENUM ('1')? Which will be smaller / faster? Or will they be identical?

graeme_p

9:20 am on Apr 19, 2019 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



I am assuming you are using MySQL, so you could use BOOLEAN: [dev.mysql.com...]

Its a synonym for TINYINT so you are not going to get a smaller type, and its a lot clearer, may benefit from boolean specific improvements in the future, may be better understood by libraries.

If you have multiple booleans in a row you can save a bit of space by using the BIT type but it would complicate your code.

csdude55

5:44 pm on Apr 20, 2019 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



You're right, it's MySQL. For some reason, when I try to enter MySQL code this site occassionally gives me a FORBIDDEN error, so I took out the word "MySQL" when I was trying to get it to post.

Why would BIT complicate the code? I was considering that, since it uses 1 bit instead of 8 bits like TINYINT. But I saw that when I changed the column to BIT, the default NULL shows <em>NULL</em>, so I suspect the NULL is an issue?

graeme_p

11:32 am on Apr 21, 2019 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



BIT uses a minimum of a byte, so the only way it would save storage is if you store a different bit in each element of a byte. SO 1) it will not have any saving over SMALLINT/BOOL unless you have more than once true/false a row and 2) you might end up with manipulation to separate values stored in one byte.

A trick that I knew worked on Postgres, but which I just found works on MySQL too, is to use blank or NULL CHAR(0): [dev.mysql.com...] Just one bit.