1

I do not want to be picky but why the moment you define a table column to be boolean the very next moment you try to get back table structure it is returned as tinyint(1)?

So for as for a table created with

CREATE TABLE IF NOT EXISTS `test` (
    `aField` BOOLEAN DEFAULT true
);

The moment I try to get it's structure with

SHOW COLUMNS FROM test

I got

+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| aField | tinyint(1) | YES  |     | 1       |       |
+--------+------------+------+-----+---------+-------+

I have not (a big) problem with the fact that a boolean is stored as a tinyint(1) but I have a problem with the fact that the moment you see a tinyint(1) you do not know if that was originally created as a boolean or as a tiny int to store small number range. I saw MySQL documentation ( https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html ) saying that

BOOL, BOOLEAN These types are synonyms for TINYINT(1)

but I disagree for the above mentioned problem.

Is there any other query I can run that will return me the original (boolean) type of the field?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Alex
  • 5,510
  • 8
  • 35
  • 54

1 Answers1

1

No, there is no way you can distinguish a column that was created as BOOLEAN from a column that was created as TINYINT(1).

The BOOLEAN type isn't stored anywhere. It's only like an alias, which is transformed into TINYINT(1) before it is stored in the database. You won't find any mention of BOOLEAN in the metadata for a column.

It's like if you store an integer value from an expression ABS(?). You don't know if the original value passed to ABS() was positive or negative, because only the result of the ABS() expression is stored.

By the way, TINYINT(1) doesn't mean it allows only a small number range. It's natural to think the (1) is a size limit, like it is for CHAR or DECIMAL. But for integer types in MySQL, it has no effect on the size of the data type or the range of values it accepts. TINYINT is always an 8-bit integer, and accepts values from -128 to 127, or for TINYINT UNSIGNED it always accepts values from 0 to 255.

See also my answer to https://stackoverflow.com/a/3135854/20860

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This very unfortunate especially if you try to create/use an ORM where the ORM needs to recreate the entities directly from a DB. – Alex Oct 06 '19 at 08:58
  • Do what the MySQL JDBC driver does: Assume `TINYINT(1)` maps to a boolean type. See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html – Bill Karwin Oct 06 '19 at 17:20