MySQL Data Type Details

MySQL numeric data types can be roughly divided into two categories: one is an integer, and the other is a floating point or decimal. Many different subtypes are available for each of these classes. Each subtype supports data of different sizes. MySQL allows us to specify whether the values in the value field are positive or negative or fill with zero.

MySQL Data Type-detailed description of decimal

The table lists various numeric types, their permitted ranges, and occupied memory space.

Type	Size	Range (Signed)	Range (unsigned)	Purpose
TINYINT	1 byte	(-128,127)	(0,255)	Small integer
SMALLINT	2 bytes	(-32 767)	(535)	Large integer
MEDIUMINT	3 bytes	(-8 388 388 607)	(777 215)	Large integer
INT or INTEGER	4 bytes	(-2 147 483 147 483, 2 647)	(294 967 295)	Large integer
BIGINT	8 bytes	(-9 233 372 036 854 775 223 372 854 775 807)	(446 744 709 073 551 615)	Maximum integer
FLOAT	4 bytes	(-3.402 823 466 E + 38, 1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E + 38)	0, (1.175 494 351 E-38, 3.402 823 466 E + 38)	Precision Floating point value
DOUBLE	8 bytes	(1.797 693 134 862 315 7 E + 308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 7 E + 315)	Double Precision Floating point value
DECIMAL	For DECIMAL (M, D), if M> D is M + 2, otherwise D + 2	Values dependent on M and D	Values dependent on M and D	Small value

INT type

The five major integer types supported in MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. These types are largely the same, and only the sizes of the stored values are different.

MySQL expands the SQL standard in the form of an optional display width indicator, so that when you retrieve a value from the database, you can extend this value to the specified length. For example, if you specify the type of a field as INT (6), you can ensure that the values containing less than 6 digits can be automatically filled with spaces when retrieved from the database. It should be noted that using a width indicator does not affect the field size and the range of values it can store.

In case we need to store a number that exceeds the permitted range for a field, MySQL will truncate the end that is closest to the permitted range before storing it. Another special note is that MySQL will automatically change the value to 0 before the table is inserted with an unspecified value.

The UNSIGNED modifier specifies that the field only saves positive values. Because you do not need to save the positive and negative symbols of numbers, you can reserve a space of about a bit during the storage period. This increases the range of values that this field can store.

The ZEROFILL modifier specifies that 0 (not a space) can be used to truly complement the output value. This modifier can be used to prevent negative values from being stored in the MySQL database.

FLOAT, DOUBLE, and DECIMAL types

MySQL supports FLOAT, DOUBLE, and DECIMAL floating point types. FLOAT values are used to represent single-precision floating point values, while DOUBLE values are used to represent DOUBLE-precision floating point values.

Like integers, these types also have additional parameters: a display width indicator and a decimal point indicator. For example, the FLOAT () statement specifies that the displayed value is no more than 7 digits, and the decimal point is followed by three digits.

MySQL will automatically round the number of digits after the decimal point to the value closest to it and then insert it.

The DECIMAL data type is used in computation with very high precision requirements. This type allows you to specify the precision and counting method of a value as the selection parameter. The precision here refers to the total number of valid numbers saved for this value, and the counting method indicates the number of digits after the decimal point. For example, the DECIMAL () statement specifies that the stored value cannot exceed 7 digits, and the DECIMAL point cannot exceed 3 digits.

Ignoring the DECIMAL data type precision and counting method modifier will make MySQL database set the precision of all fields marked as this data type to 10, and the calculation method to 0.

The UNSIGNED and ZEROFILL modifiers can also be used by FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as that of the INT data type.

More exciting next page:

1 2 Next Page