17

From MySQL manual, it says:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

I created a BOOLEAN column with 0 as the default value. Then I update the value to 2. Logically, I would expect MySQL to accept either 0 or 1 since it is a boolean. However, MySQL did not issue an error or prevent me from performing the update.

If BOOLEAN works exactly the same as TINYINT(1), does it make any difference whether I use TINYINT(1) or BOOLEAN?

Improve this question
0

3 Answers 3

Reset to default
13

Those two are really synonyms, so you can use them interchangeably. You won't see any difference between them.

If you want to allow 0 and 1 only, you can still use the bit type.

Improve this answer
1
  • 1
    @QuestionOverflow Keep in mind that BIT is really a bit field that holds one to sixty-four bits compactly. Commented Sep 23, 2014 at 22:07
4

If you are working with other people (or you want to remind yourself), you can use the data type BOOLEAN to suggest that the data is only intended to take on a value of 1 or 0.

Improve this answer
0
0

It seems to me there is indeed a difference.

In a table I'm using I have a column defined as a tinyint with an index on the column. When I invoke the following query "explain select * from table where column is true" it indicates that it's going to read all the rows in the table (the "possible_keys" and "ref" columns are null despite the "key" column showing the index).

Changing the query to "explain select * from table where column = 1" the index kicks in correctly, with "possible keys" and "ref" columns being assigned values and "rows" set to a much lower number.

I also tried replacing the "true" with "false" and the 1 with a 0 and got comparable results.

Taking it a step further I switched from a tinyint to an int and it made no difference.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.