The x in INT(x) in the mysql field definition simply refers to the display width. This optional display width provision is used to fill the width from the left when displaying values whose width is less than the specified column width. The display width does not limit the range of values that can be stored in a column, nor does it limit the display of values that exceed the specified width of the column. So the definition of x doesn’t have anything to do with storage space it’s 4 bytes.
As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The table below shows the storage and range required for each integer type.
| type | byte | Minimum value (signed/unsigned) | Maximum value (signed/unsigned) |
|---|---|---|---|
| TINYINT | 1 | – 128. | 127 |
| 0 | 255 | ||
| SMALLINT | 2 | – 32768. | |
| 0 | 65535 | ||
| MEDIUMINT | 3 | – 8388608. | |
| 0 | 16777215 | ||
| INT | 4 | – 2147483648. | |
| 0 | 4294967295 | ||
| BIGINT 8 | – 9223372036854775808. | 9223372036854775807 | |
| 0 | 18446744073709551615 |
1).int (10), which is returned to the application in the metadata of the result set. Applications can left-fill with less than 10 bits, with Spaces by default.
2). Add zerofill and fill 0.
mysql> create table ccc(a int(4), b int(20), c int(20) zerofill); Query OK, 0 rows affected (0.01sec)
mysql> insert into ccc values(4342343, 342, 342342); Query OK, 1 row affected (0.00 sec)
mysql> select * from ccc;
| a | b | c |
|---|---|---|
| 4342343 | 342 | 00000000000000342342 |
| 1 row in set (0.00 sec) |
As you can see, we asked B to fill the left space, but the program chose not to.
This is especially true if you add the optional ZEROFILL to the field. Because it replaces the default blank with zero.
//INT(4) UNSIGNED ZEROFILL 0001 0002 … 0099… 0999… 9999… 10000
//INT(2) UNSIGNED ZEROFILL 01 02 … 09… 99… 100
Without the ZEROFILL option, the left side is filled with Spaces
//INT(4) 1 2 … 99… 999… 9999… 10000
//INT(2) 1 2 … 9… 99… 100
Just to add a little bit
The parentheses after the Mysql type keyword specify the display width of the integer value (for example, INT(4)). This optional display width provision is used to fill the width from the left when displaying values whose width is less than the specified column width. The display width does not limit the range of values that can be stored in a column, nor does it limit the display of values that exceed the specified width of the column.
When used in conjunction with the optional extended ZEROFILL attribute, the default added space is replaced with zero. For example, for a column declared INT(5) ZEROFILL, the value 4 is retrieved as 00004. Note that if you store a value in an integer column that exceeds the display width, MySQL will encounter problems when generating temporary tables for complex joins, because in these cases MySQL trusts the data to fit the original column width.
All integer types can have an optional (nonstandard) attribute UNSIGNED. Use unsigned values when you want to allow only non-negative numbers in a column and when the column requires a large upper range of values.
So the characters in parentheses after int(10) and int(11) indicate the display width. The display width of an integer column has nothing to do with how many characters mysql needs to display the value of the column. It has nothing to do with the size of the storage space required by the integer. The maximum number of bytes that an int field can store remains 2147483647(signed) and 4294967295(unsigned).
Floating-point and fixed-point types can also be UNSIGNED. As a number type, this property prevents negative values from being stored in the column. However, unlike integer types, the upper range of column values remains the same.
If you specify ZEROFILL for a numeric column, MySQL automatically adds an UNSIGNED attribute to the column.
For floating-point column types, MySQL uses 4 bytes for single-precision values and 8 bytes for double-precision values.
The FLOAT type is used to represent approximate numeric data types. The SQL standard allows the option to specify precision with bits (but not exponential ranges) in parentheses after the FLOAT keyword. MySQL also supports optional precision provisions that are only used to determine the size of the storage. A precision of 0 to 23 corresponds to a 4-byte single precision for the FLOAT column. Accuracies 24 through 53 correspond to 8-byte doubles in the DOUBLE column.
MySQL allows non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” indicates that the value displays a total of M bits of integers, with the D bit after the decimal point. For example, a column defined as FLOAT(7,4) can be displayed as -999.9999. MySQL saves values by rounding them up, so if you insert 999.00009 in the FLOAT(7,4) column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for DOUBLE PRECISION(non-standard extension). MySQL also treats REAL as a synonym for DOUBLE PRECISION(non-standard extension), unless the SQL server schema includes the REAL_AS_FLOAT option.
For maximum portability, code that needs to use approximate numeric data value stores should use FLOAT or DOUBLE PRECISION, without specifying PRECISION or bits.
The DECIMAL and NUMERIC types are considered the same type in MySQL. They are used to hold values that must be exact, such as currency data. When declaring columns of this type, you can (and usually do) specify precision and scale; Such as:
Salary DECIMAL(5,2) In this example, 5 is precision and 2 is scale. Precision indicates the number of major digits of the saved value, and scale indicates the number of digits that can be saved after the decimal point.
Save DECIMAL and NUMERIC values in binary format in MySQL 5.1.
Standard SQL requires that the SALARY column be able to hold any value as a 5-digit integer and a 2-digit decimal. Thus, the values that can be saved in the SALARY column in this case range from -999.99 to 999.99.
In standard SQL, syntactic DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, syntactic DECIMAL is equivalent to DECIMAL(M,0), and the value of M can be determined by calculation. Variable forms of the DECIMAL and NUMERIC data types are supported in MySQL 5.1. M The default value is 10.
The maximum number of digits for DECIMAL or NUMERIC is 65, but the actual range of a specific DECIMAL or NUMERIC column is constrained by the precision or scale of the specific column. If such a column allocates a value with more than the number of digits after the decimal point allowed by the specified scale, the value is converted to that scale. (The operation depends on the operating system, but the results are generally truncated to the allowed number of bits).
The BIT data type can be used to hold BIT field values. The BIT(M) type allows the storage of M BIT values. M ranges from 1 to 64.
To specify a bit value, use the b ‘value’ character. Value is a binary value written in zeros and ones. For example, b ‘111’ and b ‘100000000’ represent 7 and 128 respectively.