Toggle a TINYINT Field in MySQL

MySQL uses the datatype TINYINT to store boolean values. MySQL stores the value as TINYINT(1) which is the same as a bit so the value is either 0 (false) or 1 (true). Using boolean fields can be very useful, but it can be costly in processing as to change the value you have to query the database, find out the value of the field and then act accordingly.

Here is a simple MySQL query that can be used to toggle the value already present in the TINYINT field without having to do any pre-querying.

UPDATE table SET field = 1 - field


works with bool even thanks

Nice solution. Thanks! 

Later on, I found an alternative solution using the bitwise XOR operator (^). I think I prefer that solution as it also works if the field is signed.

example: UPDATE table SET field = 1 ^ field

Cheers, Paul


Add new comment

The content of this field is kept private and will not be shown publicly.
2 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.