mysql – Why is length defined in an integer data type?

Question:

When we create a table and define an integer data type , we possibly define a specific length. For example:

  • SMALLINT(5) ,
  • INT(11) , or
  • BIGINT(20)

Why is this length defined in an integer data type?

Answer:

Investigating I got this documentation which gives a fairly clear idea, testing I was able to demonstrate what it is for, and the length that is defined in parentheses is called an attribute .

When declaring the integer data type, the attribute in question is used to define the padding length of the digit "0" on the left when doing a SELECT, and this can only be seen when you define the column with the ZEROFILL option .

In the following example I create a table in the test database called demo, we are going to test with the TINYINT data type and show how it is created by default by MySQL, then values ​​are inserted and we do an ALTER to see the changes:

USE test;

DROP TABLE IF EXISTS demo;
CREATE TABLE IF NOT EXISTS demo (
  id   INT NOT NULL AUTO_INCREMENT,
  demo TINYINT NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SHOW CREATE TABLE demo;

I insert three values ​​of different lengths:

INSERT INTO demo (demo) VALUES (127);
INSERT INTO demo (demo) VALUES (12);
INSERT INTO demo (demo) VALUES (1);

Now I do a SELECT to see what is inside:

SELECT * FROM demo;
+----+------+
| id | demo |
+----+------+
|  1 |  127 |
|  2 |   12 |
|  3 |    1 |
+----+------+

I modify the column so that it has the ZEROFILL property.

ALTER TABLE demo MODIFY demo TINYINT(3) ZEROFILL;

I do the SELECT again to see the change:

+----+------+
| id | demo |
+----+------+
|  1 |  127 |
|  2 |  127 |
|  3 |  012 |
|  4 |  001 |
+----+------+

As we can see, it fills the integer value with three leading zeroes "0". We can conclude that the integers attribute is used with the ZEROFILL property for a visual aspect.

Post Views: 1
Scroll to Top