UseBB Community

There is a nicer-better way to do a boolean settings column in MySQL.
ENUM('Y', 'N') is much more readable and uses less storage space (even if tiny-to-insignificant),.. I suggest that the types of the columns that have taken up such role should be changed to ENUM type, so that they also dont cause confusion between integer values.

Does this field type exist in MySQL 3.23? UseBB should work on that version. I think however that this will more likely be fixed in 2.0 together with more changes in database structure (including field types). We might have upped the minimum system requirements for MySQL to 4.0 by then.

I found this on the MySQL Manual page for ENUM :

MySQL Manual wrote

Starting from MySQL 3.23.51, trailing spaces are automatically deleted from ENUM member values when the table is created.

so yes I suppose it works with that version too.

The question is: is it easy to just change the column type without losing any changes?

PC_Freak wrote

The question is: is it easy to just change the column type without losing any changes?

Basicly the queries that refer to columns that had their type changed need to been changed too. If I understood you right ..

Yes, but can you change the column types without losing the information stored in it? So that TRUE (ie 'Y') stays TRUE as it was before (1).

hmm ... I'd need to test to say for sure .. but it looks that it would be set to the first value in the Enum .. :/

Ok, tested it, here are the results.

If you alter a column type to ENUM from INT the ENUM assigns the value of the field with value n to the n-th option of the ENUM..

meaning..

There is a table like

id(int)	- 	to_enum(int)
   1 	- 	     0
   2 	- 	     1
   3 	- 	     2

an alteration of to_enum to a type of ENUM with options ('Yes', 'No', 'Maybe' ) will result in this table:

id(int) 	- 	to_enum(enum)
1 	- 	    NULL ( or '' I am not sure yet )
2 	- 	     'Yes'
3 	- 	     'No'

The first value will result in NULL simply because there is no 0 option in ENUM. Options start at 1.

Herodes wrote

ENUM('Y', 'N') uses less storage space

tinyint -> 1 byte
enum -> 1 or 2 bytes

how so it's smaller? And (because of the strings) it has to lookup the meaning each time you query (ofcourse, that's little extra overhead but extra overhead).

So I think it's actualy less good than tinyint

Yep that is a good observation,... seems that I had a bad understanding on the memory thing.
It cant trade off the readability of the code thought. I wanted you to look at the last post on this. It could be a bit old but situation hasn't changed a bit.

As he writes: the most logical way to mimic a boolean is with 1 (true) and 0 (false) and I think (as programmer) that he's right. But I don't agree with his representation. I think a tinyint (1) is the most logical way to present this and actually the enum represents it the exact same way (only with more work).

And the creators of mysql agree with me because they made a boolean field (see: http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html) and it's represented by a tinyint (1) (the 1 means 1 bit).

Personally I use a Query object and I set a boolean by calling a method setBoolean. Very readable and it translates the boolean to 1 or 0 (the C way).

okie,.. I surrender to the tinyint..