0

Looking at integer values. It seems that setting the Unsigned attribute overrides the field length.

Traditionally, MYSQL translates the BOOLEAN alias to TINYINT(1). According to the inter-webs, as of MySQL 8.0.17, display width specifications for integer data types have been deprecated. There are two exceptions to this which include: TINYINT(1) However, there is a bug (known or unknown IDK) where when I set UNSIGNED on any TINYINT value, the display length is dropped.

Steps to reproduce:

  1. Create a table with a field intended to be used as a BOOLEAN;

    CREATE TABLE users ( id int unsigned NOT NULL AUTO_INCREMENT , user_name varchar(50) NOT NULL, password varchar(255) NOT NULL, is_active tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (id), ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  2. Observe that the display length on TINYINT(1) is in fact set.

  3. Alter the table to make is_active an UNSIGNED value:

    ALTER TABLE users CHANGE COLUMN is_active is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT '1' ;

  4. Observe that TINYINT no longer has a display length.

  5. List item

Expected result:

I argue that the correct field attribute for a "BOOLEAN" would be unsigned since your options would be 0 or 1. Not negatives. Therefore I would expect that the UNSIGNED behavior for TINYINT(1) be identical to the signed behavior, and field display length would be set / retained.

Question:

Has anyone else encountered this behavior? Any ideas on a work around? Right now I am sticking with signed tinyint's...

UnKulMunki
  • 15
  • 7
  • You would have to ask the mysql developers if this is a bug or not. To me tinyint(1) and unsigned tinyint(1) are not the same and the documentation does not mention that the field length exception would apply to the unsigned version as well. Not sure what workaround would be needed besides using the signed version of tinyint. – Shadow Nov 22 '21 at 22:50
  • 1) this only matters because I use a framework that relies on TINYINT(1) UNSIGNED in their code-generation tool to auto-gen boolean code values. 2) in reply to @Shadow , If I set the field to TINYINT(1) UNSIGNED ZERO FILL, it keeps the field length value. Since ZERO FILL essentially does nothing on a TINYINT(1) especially when there is a default value of 1 (or even 0), I was thinking it is a bug. I submitted a bug report to Oracle, so I will let you know whether they consider it a bug or not. – UnKulMunki Nov 22 '21 at 22:53
  • Then the creators of the framework got it wrong. The framework should adapt to mysql's conventions, not the other way around. – Shadow Nov 22 '21 at 22:55
  • I don't entirely disagree with that premise. The problem seems to be that MySQL is inconsistent in their behavior here to it could be confusing to try to conform when they behaviors dont exactly match and there is no documentation to explain which way it should go... – UnKulMunki Nov 22 '21 at 23:02
  • I'm not sure how mysql's documentation or its behaviour would be inconsistent. BOOL and BOOLEAN are synonyms for tinyint(1). In order to specify an unsigned tinyint, you would need tinyint(1) unsigned, not tinyint(1). Tinyint(1) was kept, tinyint(1) unsigned was not. – Shadow Nov 22 '21 at 23:11
  • Even if mysql were to concede this as a bug, I do not think this will ever be resolved as they are planning to retire the whole display with feature that confused a lot of users for decades. I would also note that the boolean aliases serve only the purpose of making migrations from other databases or interactions with programming languages easier. Some databases and programming languages do use -1 to indicate true. – Shadow Nov 22 '21 at 23:24

0 Answers0