CODEWITHSUNDEEP
mysqlmysql-8.0tinyint
UnKulMunki
UnKulMunki

Reputation: 15

MySQL 8.x: Unsigned attribute overrides the field length

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...

Upvotes: 0

Views: 192

Answers (0)

Related Questions