AND operation in calculated field in MySQL

MySQL

First of all, I apologize if this is a stupid question, since let's be honest, it probably is.

I am creating a table in MySQL that has four BOOLEAN fields. What I would like is to have a fifth, calculated, BOOLEAN field that is true if and only if all of the other BOOLEANs are true. Basically, an AND operation. Is there any good way to do that in MySQL? Thanks!

Best Answer

I've done some playing (I don't use MySQL for complex databases) but I think I found a simple solution - create the fifth column with "calculatedcol boolean as (booleancolumn1 & booleancolumn2 & booleancolumn3 & booleancolumn4)" - doing this appears to prevent you adding stuff to the calculated column, but allows you to query it.

I did a quick test and created a table as follows:

 create table deltest (id serial, v1 boolean, v2 boolean, v3 boolean, v4 boolean, v5 boolean as (v1&v2&v3&v4));

Inserts needed to be in the form

  insert into deltest (v1,v2,v3,v4,v5) values (1,1,1,1);

An alternative - which would allow you to maintain "normal" syntax for inserts into the table, but would only allow you to run select queries with a calculated table would be to create a normal table, then create a view. When doing inserts insert into the table. When reading a place where you will need the calculated boolean value, use the view.

Of-course, for something as simple as a table with multiple booleans it may be easier to not do this at the table level, but simply generate the column in the select query. Because they are boolean fields you can use the "&" operator to do a bitwise and much like I did to create the v5 column in the test table above.

Related Question