mysql numerical type

MySQL supports all standard SQL numeric data types. These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

BIT data type saves bit field values ​​and supports MyISAM, MEMORY, InnoDB and BDB tables.

As an extension of the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT and BIGINT. The following table shows the storage and range required for each integer type.

Type Bytes Minimum Value Maximum Value

(Signed/Unsigned) (Signed/Unsigned)

TINYINT 1 1 -128 127

0 255

SMALLINT 2 -32768 32767

0 65535

MEDIUMINT 3 -8388608 8388607

0 16777215

INT 4 -2147483648 2147483647

0 4294967295

BIGINT 8 -9223372036854775808 9223372036854775807

0 18446744073709 551615

MySQL also supports the option of specifying it in brackets after this type of keyword The display width of an integer value (for example, INT(4)). The optional display width specification is used to fill the width from the left when the display width is smaller than the specified column width.

Display width does not limit the range of values ​​that can be saved within the column, nor does it limit the display of values ​​that exceed the specified width of the column.

When used in combination with the optional extended attribute ZEROFILL, the default supplementary spaces are replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, the value 4 is retrieved as 00004. Please note that if you store a value in an integer column that exceeds the displayed width, MySQL will encounter problems when generating temporary tables for complex joins, because in these cases MySQL believes that the data fits the original column width.

All integer types can have an optional (non-standard) attribute UNSIGNED. Unsigned values ​​can be used when you want to allow only non-negative numbers in a column and the column requires a larger upper numeric range.

Floating and fixed point types can also be UNSIGNED. For the same number type, this property prevents negative values ​​from being saved in the column. However, unlike integer types, the upper range of column values ​​remains unchanged.

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

For floating point column types, single precision values ​​use 4 bytes and double precision values ​​use 8 bytes in MySQL.

The FLOAT type is used to represent approximate numeric data types. The SQL standard allows the option of specifying precision in bits (but not exponential ranges) within parentheses following the keyword FLOAT. MySQL also supports optional precision specifications that are used only to determine storage size. Precisions from 0 to 23 correspond to the 4-byte single precision of the FLOAT column. Precisions from 24 to 53 correspond to the 8-byte double precision of the DOUBLE column.

MySQL allows the use of non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, "(M,D)" means that the value displays a total of M-digit integers, where D-digits are located after the decimal point. For example, a column defined as FLOAT(7,4) could be displayed as -999.9999. MySQL rounds the value when saving it, so if you insert 999.00009 into 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 mode includes the REAL_AS_FLOAT option.

To ensure the greatest possible portability, code that requires the storage of approximate numeric data values ​​should use FLOAT or DOUBLE PRECISION, without specifying precision or number of digits.

DECIMAL and NUMERIC types are treated as the same type in MySQL. They are used to hold values ​​that must be of exact precision, such as currency data. When declaring a column of this type, you can (and usually do) specify precision and scale; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. Precision indicates how many digits can be saved in the value, and scale indicates how many digits 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 can store any value with 5 integer digits and two decimal places. Therefore, in this case the range of values ​​that can be saved in the salary column is from -999.99 to 999.99.

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), and the value of M can be determined through calculation. Variable forms of DECIMAL and NUMERIC data types are supported in MySQL 5.1. The default value of M is 10.

The maximum number of digits for a 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 is assigned a value with more digits after the decimal point than allowed by the specified scale, the value is converted to that scale. (The specific operation depends on the operating system, but generally the results are truncated to the allowed number of digits).

BIT data type can be used to save bit field values. The BIT(M) type allows storage of M-bit values. M ranges from 1 to 64.

To specify a bit value, you can use the b'value' symbol. value is a binary value written with 0 and 1. For example, b'111' and b'100000000' represent 7 and 128 respectively.

If the length of the value assigned to the BIT(M) column is less than M bits, pad the left side of the value with 0s. For example, assigning a value b'101' to the BIT(6) column has the same effect as assigning b'000101'.

When you want to save a value in a numeric column that exceeds the allowed range of the column, MySQL's operation depends on the SQL mode in effect at that time. If mode is not set, MySQL clips the value to the corresponding endpoint of the range and saves the clipped value. However, if the mode is set to traditional ("strict mode"), out-of-range values ​​will be rejected with an error, and inserts will fail according to SQL standards.

If the INT column is UNSIGNED, the size of the column range will be the same, but its endpoints will change to 0 and 4294967295. If you try to save -9999999999 and 9999999999, the values ​​saved into the column in non-strict mode are 0 and 4294967296.

If the value assigned in a floating-point or fixed-point column exceeds the range specified by the specified (or default) precision and scale, MySQL saves the value representing the corresponding endpoint of the range in non-strict mode.

When MySQL is not working in strict mode, conversions due to clipping will be reported as warnings for ALTER TABLE, LOAD DATA INFILE, UPDATE and multi-row INSERT statements. When MySQL is working in strict mode, these statements will fail and some or all values ​​will not be inserted or changed, depending on whether the table is transactional and other factors.