I can understand that if you UPDATE a TINYINT(1) column and pass a value of 1 or 0 it will convert it to TRUE or FALSE, respectively.
I'm just confused on what happens when you UPDATE the column with a different value, say 2. In other words, if you UPDATE a TINYINT(1) column with the value 2, will this be TRUE or FALSE? Or, is this even doable?
A tinyint column can hold values from 0 to 255 (if it is defined as unsigned) or -128 to +127 (if it is signed). The (1) in tinyint(1) is only for some formatting options and generally ignored. You could create it as tinyint(100) and it wouldn't make a difference.
Regarding the TRUE or FALSE, any int (int, tinyint, smallint, bigint) value can be used as (or converted to) a boolean value. It is considered FALSE if it is 0 and TRUE otherwise. So, 2 would count as TRUE.
To be entirely clear, MySQL does not have a true BOOLEAN type. BOOLEAN is a synonym of TINYINT(1), as the docs explain in Numeric Type Overview:
BOOL,BOOLEANThese types are synonyms for
TINYINT(1). A value of zero is considered false. Nonzero values are considered true:...
However, the values
TRUEandFALSEare merely aliases for 1 and 0, respectively, as shown here:...
is_true boolean NOT NULL CHECK(is_true IN(TRUE, FALSE)) right? boolean UNSIGNED, if that would help with anything, but then found this answer stackoverflow.com/a/74376677/3484824 so it seems better to be avoided as bool/boolean are synonyms of tinyint(1) and not tinyint(1) UNSIGNED and libraries and ORMs might depend on this