What is the benefit of zerofill in MySQL?

by

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

More Related Contents:

  1. What is the size of column of int(11) in mysql in bytes?
  2. What column type/length should I use for storing a Bcrypt hashed password in a Database?
  3. Types in MySQL: BigInt(20) vs Int(20)
  4. What data type to use for hashed password field and what length?
  5. What MySQL data type should be used for Latitude/Longitude with 8 decimal places?
  6. mysql equivalent data types
  7. If two columns are 1 then make new column
  8. Can we use CONCAT and JOIN together in MySQL? [closed]
  9. Truncate all tables in a MySQL database in one command?
  10. Retrieving the last record in each group – MySQL
  11. Cast from VARCHAR to INT – MySQL
  12. Find distance between two points using latitude and longitude in mysql
  13. How to convert result table to JSON array in MySQL
  14. MySQL Event Scheduler on a specific time everyday
  15. Export and Import all MySQL databases at one time
  16. How to access a RowDataPacket object
  17. Update row with data from another row in the same table
  18. tomcat7 – jdbc datasource – This is very likely to create a memory leak
  19. Select from one table where not in another
  20. MySQL password function
  21. phpMyAdmin is throwing a #2002 cannot log in to the mysql server phpmyadmin
  22. MySQL database with unique fields ignored ending spaces
  23. Set value to NULL in MySQL
  24. mysql DECLARE WHILE outside stored procedure how?
  25. How can I check MySQL engine type for a specific table?
  26. mySQL UPDATE query returns “0 rows affected”
  27. When is a timestamp (auto) updated?
  28. Should I normalize my DB or not?
  29. How to pass a variable to a IN clause?
  30. Insert and set value with max()+1 problems

Leave a Comment