Toggle a TINYINT Field in MySQL

31st December 2007 - 2 minutes read time

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
query

Comments

works with bool even thanks

Submitted by Anonymous on Fri, 08/27/2010 - 14:26

Permalink

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

Submitted by Paul on Sun, 03/05/2023 - 16:38

Permalink

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
1 + 1 =
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.

Related Content

Solving Query Performance Problems With The MySQL Slow Query Log

21st January 2024

MySQL's slow query log is a key component in your MySQL administration setup. Whilst normal logging can help you in terms of tracking down issues with your database system, the slow query log can help you track down issues in your database setup before they become problematic.

Read more

Case Insensitive Like Searches In MySQL

2nd July 2011

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

Read more

Connecting To A Non Standard MySQL Socket In PHP

11th February 2011

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details.

Read more

Count Number Of Characters In A String With MySQL

30th September 2010

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL.

Read more

MySQL Order Table By Character Length

24th September 2009

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.

Read more

Starting And Stopping MySQL Using Windows .bat Files

13th May 2009

I use my PC for a lot of different things, and I don't necessarily need to have MySQL server running all the time, especially when I want to play a game. So I wondered if there was a simple way in which I could start and stop the server using a simple .bat file.

Read more