What is the benefit of zerofill in MySQL

Question

I just want to know what is the benefit usage of defining ZEROFILL for INT DataType in MySQL    id  INT UNSIGNED ZEROFILL NOT NULL

User · Answer

It s a feature for disturbed personalities who like square boxes   You insert   1 23 123    but when you select  it pads the values   000001 000023 000123

User · Answer

If you specify ZEROFILL for a numeric column  MySQL automatically adds the UNSIGNED attribute to the column   Numeric data types that permit the UNSIGNED attribute also permit SIGNED  However  these data types are signed by default  so the SIGNED attribute has no effect   Above description is taken from MYSQL official website

User · Answer

When used in conjunction with the   optional  nonstandard  attribute   ZEROFILL  the default padding of   spaces is replaced with zeros  For   example  for a column declared as   INT 4  ZEROFILL  a value of 5 is   retrieved as 0005    http   dev mysql com doc refman 5 0 en numeric-types html

User · Answer

mysql gt  CREATE TABLE tin3 id int PRIMARY KEY val TINYINT 10  ZEROFILL   Query OK  0 rows affected  0 04 sec   mysql gt  INSERT INTO tin3 VALUES 1 12   2 7   4 101   Query OK  3 rows affected  0 02 sec  Records  3  Duplicates  0  Warnings  0  mysql gt  SELECT   FROM tin3   ---- ------------    id   val           ---- ------------     1   0000000012      2   0000000007      4   0000000101    ---- ------------  3 rows in set  0 00 sec   mysql gt   mysql gt  SELECT LENGTH val  FROM tin3 WHERE id 2   -------------    LENGTH val     -------------             10    -------------  1 row in set  0 01 sec    mysql gt  SELECT val 1 FROM tin3 WHERE id 2   -------    val 1    -------        8    -------  1 row in set  0 00 sec

User · Answer

I know I m late to the party but I find the zerofill is helpful for boolean representations of TINYINT 1    Null doesn t always mean False  sometimes you don t want it to   By zerofilling a tinyint  you re effectively converting those values to INT and removing any confusion ur application may have upon interaction   Your application can then treat those values in a manner similar to the primitive datatype True   Not 0

User · Answer

It helps in correct sorting in the case that you will need to concatenate this  integer  with something else  another number or text  which will require to be sorted as a  text  then   for example   if you will need to use the integer field numbers  let s say 5  concatenated as A-005 or 10 0005

User · Answer

When you select a column with type ZEROFILL it pads the displayed value of the field with zeros up to the display width specified in the column definition  Values longer than the display width are not truncated  Note that usage of ZEROFILL also implies UNSIGNED   Using ZEROFILL and a display width has no effect on how the data is stored  It affects only how it is displayed   Here is some example SQL that demonstrates the use of ZEROFILL   CREATE TABLE yourtable  x INT 8  ZEROFILL NOT NULL  y INT 8  NOT NULL   INSERT INTO yourtable  x y  VALUES  1  1    12  12    123  123    123456789  123456789   SELECT x  y FROM yourtable    Result           x          y  00000001          1  00000012         12  00000123        123 123456789  123456789

User · Answer

ZEROFILL  This essentially means that if the integer value 23 is inserted into an INT column with the width of 8 then the rest of the available position will be automatically padded with zeros   Hence  23   becomes   00000023

User · Answer

One example in order to understand  where the usage of ZEROFILL might be interesting   In Germany  we have 5 digit zipcodes  However  those Codes may start with a Zero  so 80337 is a valid zipcode for munic  01067 is a zipcode of Berlin   As you see  any German citizen expects the zipcodes to be displayed as a 5 digit code  so 1067 looks strange   In order to store those data  you could use a VARCHAR 5  or INT 5  ZEROFILL whereas the zerofilled integer has two big advantages    Lot lesser storage space on hard disk If you insert 1067  you still get 01067 back   Maybe this example helps understanding the use of ZEROFILL

[mysql] What is the benefit of zerofill in MySQL?

Examples related to mysql

Examples related to types

Examples related to unsigned-integer