checkbox values stored in mysql

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a series of checkboxes and need to store their values in a MySQL

I'm wondering what data type people recommend:

varchar or text, exp. "True/False", "Yes/No"
tinyint, exp. "0/1"

Maybe it depends on the usage or maybe it doesn't matter and/or is a choice
of personal preference.

Comments welcome.

p.s. Is there a reference page somewhere that discusses the various data
types and their recommended uses?

Re: checkbox values stored in mysql

Xenophobe wrote:
Quoted text here. Click to load it

True/False 0/1 will basically be personal preference - and it is a lot more
efficient to store 1 byte (tinyint) vs. 5.

Michael Austin.
Consultant - Available.

Re: checkbox values stored in mysql

I tend to agree.

Speaking of efficiency, do you prefer to use varchar? I've been told in the
past to treat all data as text UNLESS it's a number that requires

Quoted text here. Click to load it

Re: checkbox values stored in mysql

Xenophobe wrote:

Quoted text here. Click to load it
you need to review the MySQL docs concerning the CHAR/VARCHAR data types at:">

There is a table that shows what the difference between CHAR and VARCHAR - read
them carefully.  If you don't understand, just ask.

Quoted text here. Click to load it

Whoever gave you that sage advice obviously should not be designing databases.
There are many different reasons to use different datatypes.  The least of which
is efficiency.  Yes, I did mention that before, but I also took into account
what you were trying to achieve.

<RANT mode=annoyed>
One of my pet peeves is the fact that too many programmers are creating
"databases" (and I use the term very loosely here), and then can't understand
why their database doesn't perform.  Sometimes the problem is also the database
engine they have chosen to employ. MySQL and PostgreSQL are fine for some
things, but may not be "enterprise class".  And expecting an interpreted
language like PHP or Python etc... to be scaldingly fast is just asking for
trouble.  wheeew...

Michael Austin.
Consultant - Available.
OracleRdb, OracleRDBMS, MySQL DBA and OpenVMS, Linux System Administrator
Web Administrator and Generalist
Donations welcomed.

Re: checkbox values stored in mysql

Quoted text here. Click to load it

 Largely comes down to personal preference, I think.

 I use 'T'/'F' mainly just because that's the convention at the company where I

 0/1 has the advantage that you can use the value as a Boolean value in PHP or
Perl etc.

 I wouldn't use anything longer than a byte, though, so that rules out the
wordier 'Yes'/'No' 'True'/'False' options.

 In fact in Oracle, which I mostly work with, 1 is two bytes in its internal
storage scheme, whereas 'T' is one byte (unless you're using UTF16 as the
database character set).

 MySQL's TINYINT type guarantees a single byte for the number though.

 Should be wary of trying to be clever and forming bitmasks in a single column
just to start saving bytes, since you're breaking normalisation rules if you go
down that route.

--          /

Site Timeline