Mysql – Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int

datatypesMySQL

I am developing a web app in Laravel where I have to ask users a set of Yes/No questions and save the answers in the database. I thought that I should try and save all answers in binary rather than saving them one-by-one since the number of questions vary from time to time. Say in three questions, the user answered Y-Y-N; that would convert into "110".

Now, on the database side, should I:

save the data as is (varchar)
use binary/blob
convert to int and save sa int

Not sure which option is the best, considering that the system sometimes output 50 questions.

MySQL -V info:
mysql Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

Best Answer

One bit per answer

If you are willing to limit the number of questions to 64, then BIGINT UNSIGNED will work somewhat easily, but limited to 64 true/false values. Let's say q is the column being used.

q & 1<<0    -- the bit for question 1 (assuming question numbers start at 1)
q & 1<<22   -- the bit for question 23
(q & 1<<22)  = 0   "false" for q23
(q & 1<<22) != 0   "true"  for q23
(q & 0x7FF)  = 0x7ff   "true" for all of q 1-11

Etc.

The SET datatype is implemented similarly but is clumsier to use for your case since it wants you to name each bit.

If you need more than 64, then BINARY and BLOB would let you have up to 4 billion bits (in LONGBLOB), but it is much messier to extract a single bit from them.

INT UNSIGNED has only 32 bits (4 bytes), thereby not satisfying your "50".

One byte per answer

It is probably not unreasonable to have the answers in rows -- a table with (student, question_number, answer). In this case answer could be any of these. Each takes 1 byte and is about as easy to work on as any other.

CHAR(1) CHARACTER SET ASCII NOT NULL.  -- and insert "T"/"F" (or whatever)
TINYINT UNSIGNED NOT NULL   -- 0=false, 1=true
ENUM("F", "T") NOT NULL

Related Solutions

MySQLDump wrong dump

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!

Mysqldump without slow query

The easiest way would be to change the global setting for long_query_time to a ridiculously high number, run the mysqldump, and change long_query_time back to its original value.

Perhaps something like this

MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Record the long_query_time value
#
SQL="SELECT variable_value FROM information_schema.global_variables"
SQL="${SQL} WHERE variable_name='long_query_time'"
ORIGINAL_LONG_QUERY_TIME=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Change long_query_time to 1 year 
#
SQL="SET GLOBAL long_query_time = 31536000"
mysql ${MYSQL_CONN} -ANe"${SQL}
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Change long_query_time to original value
#
SQL="SET GLOBAL long_query_time = ${ORIGINAL_LONG_QUERY_TIME}"
mysql ${MYSQL_CONN} -ANe"${SQL}

or disable the slow query log

MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Disble Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'OFF'"
mysql ${MYSQL_CONN} -ANe"${SQL}"
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Enable Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'ON'"
mysql ${MYSQL_CONN} -ANe"${SQL}"

Give it a Try !!!

CAVEAT #1

Your DBA must give the SUPER privilege to backupuser so that backupuser can globally change the long_query_time setting. Perhaps a DBA can have a cronjob give away SUPER privilege to backupuser just before the mysqldump and revoke SUPER privilege when the backup is done.

CAVEAT #2

Unfortunately, any long running queries that occur during the mysqldump might not be recorded