MySQL study note _ Summary of the length of MySQL integer Fields

MySQL study note _ Summary of the length of MySQL integer fields The following table shows the storage and range of MySQL integer fields:

MySQL also supports specifying the display width of an integer (for example, INT (4) in parentheses after a keyword of this type )). The optional display width rule is used to fill up the width from the left when the display width is less than the specified column width value. (Similar to the LPAD function) in INT (M), M indicates the maximum display width. The maximum valid display width is 255. The display width is irrelevant to the storage size or the range of values in the type. If ZEROFILL is specified for a value column, MySQL automatically adds the UNSIGNED attribute to the column.
The display width does not limit the range of values that can be saved in the column, nor the display of values that exceed the specified width of the column. When combined with the optional extension attribute ZEROFILL, the spaces supplemented by default are replaced by zero. For example, for a column declared as INT (5) ZEROFILL, value 4 is retrieved as 00004. Note that if you save a value that exceeds the display width in an integer column, MySQL may encounter problems when generating a temporary table for a complex join, because MySQL believes that the data is suitable for the width of the original column. [The above content is taken from the MySQL official Chinese translation documentation] The following is an example of testing to add zero to the ZEROFILL attribute:
1. create table 'test _ intzerofill '('id' int (11) not null AUTO_INCREMENT, 'cash _ id' int (6) unsigned zerofill not null, primary key ('id') www.2cto.com) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8 if you use the Navicat tool to visually create a table, as shown in, select "fill with zero. (The "unsigned" option is automatically selected when the file is saved ).

2. add test data insert into test_intzerofill VALUES ('1', '1'); insert into test_intzerofill VALUES ('2', '22'); insert into test_intzerofill VALUES ('3 ', '123'); insert into test_intzerofill VALUES ('4', '123'); insert into test_intzerofill VALUES ('5', '123 ');
3. When viewing table records in the Navicat tool, the tool will automatically remove the zero added. However, when querying through the MySQL 5.5 Command Line Client, the zero value is displayed (as shown in the right figure below ).

4. The corresponding Java program needs to convert it into a string type to display the added zero.

5. When you use the Navicat tool to export table data, the added zero will also be included.
Appendix: LPAD function example:



Author: Acacia Forest