MySQL database field data type, length detailed

Type of a value

Storage required for column types
TINYINT 1 bytes
SMALLINT 2 bytes
Mediumint 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT 8 bytes
FLOAT (x) 4 if X < = 24 or 8 If < = x < = 53
FLOAT 4 bytes
DOUBLE 8 bytes
DOUBLE PRECISION 8 bytes
Real 8 bytes
DECIMAL (m,d) m bytes (d+2, if M < D)
NUMERIC (m,d) m bytes (d+2, if M < D)

MySQL's numeric data types can be roughly divided into two categories, one is an integer and the other is a floating-point number or decimal. Many different subtypes are available for each of these categories, each of which supports different sizes of data, and MySQL allows us to specify whether the values in the Value field are either positive or negative or filled with 0.

The surface lists the various numeric types and their allowable range and memory footprint.

Type size range (signed) range (unsigned) purpose
TINYINT 1 byte (-128,127) (0,255) Small integer value
SMALLINT 2 bytes (-32 768,32 767) (0,65 535) Large integer value
Mediumint 3 bytes (-8 388 608,8 388 607) (0,16 777 215) Large integer value
int or integer 4 bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) Large integer value
BIGINT 8 Bytes (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) Maximum integer value
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. 402 823 466 e+38)

Single-precision floating-point values

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 8 7 e+308 315 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)

double-precision floating-point number

Decimal to Decimal (m,d), if m>d, for m+2 otherwise d+2 dependent on M and d for values that depend on the value of M and d are small values

INT type

The 5 main integer types supported in MySQL are Tinyint,smallint,mediumint,int and BIGINT. These types are to a large extent the same, and only the values they store are not the same size.
MySQL extends the SQL standard in the form of an optional display width indicator, which can be lengthened to a specified length when retrieving a value from the database. For example, specifying a field with a type of INT (6) ensures that values that contain fewer than 6 digits are automatically padded with spaces when retrieved from the database. It should be noted that using a width indicator does not affect the size of the field and the range of values it can store.
In case we need to store a number that is out of the allowable range for a field, MySQL stores it as soon as the allowable range is closest to its end. Another special place is that MySQL will automatically be modified to 0 before the specified value is inserted into the table.
The UNSIGNED (unsigned) modifier stipulates that the field holds only positive values. Because you do not need to save the positive and negative symbols of a number, you can save a "bit" space at the time of storage. This increases the range of values that the field can store.
The Zerofill (0 padding) modifier stipulates that 0 (not spaces) can be used to really complement the output value. Use this modifier to prevent the MySQL database from storing negative values.

FLOAT, DOUBLE, and DECIMAL types

Three floating-point types supported by MySQL are float, DOUBLE, and DECIMAL types. The float value type is used to represent single-precision floating-point values, and the double numeric type is 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 statement FLOAT (7,3) stipulates that the value displayed does not exceed 7 digits, followed by a 3-digit number.
If the number of digits after the decimal point exceeds the allowable range, MySQL automatically rounds it to the nearest value and inserts it.
The DECIMAL data type is used in calculations that require very high precision, and this type allows you to specify the precision and count method of the numeric value as the selection parameter. The precision here refers to the total number of valid digits saved for this value, and the Count method represents the number of digits after the decimal point. For example, Statement decimal (7,3) stipulates that the stored value does not exceed 7 digits, and that no more than 3 digits after the decimal point.
Ignoring the precision and count method modifiers for the DECIMAL data type will cause the MySQL database to set the field precision of all identifiers to this data type to 10, and the calculation method to 0.
UNSIGNED (unsigned) and Zerofill (0 fill) modifiers can also be used by FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

2, date and time type

Storage required for column types
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
Time 3 bytes
Year 1 bytes
When working with values for date and time types, MySQL has 5 different data types to choose from. They can be divided into simple dates, time types, and mixed date and time types. Depending on the required precision, subtypes can be used in each category, and MySQL has built-in functionality to change the variety of input formats into a standard format
Type size (byte) range format use
Date 3 1000-01-01/9999-12-31 Yyyy-mm-dd Day value
Time 3 ' -838:59:59′/' 838:59:59′hh:mm:ss value or duration
Year 1 1901/2155 YYYY years value
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 yyyy-mm-dd HH:MM:SS Mixed date and time value
TIMESTAMP 8 1970-01-01 00:00:00/2037 year sometime YYYYMMDD HHMMSS mixed date and time value, timestamp

DATE, time, and TEAR types

MySQL stores a simple date value with the date and TEAR types, and uses the time type to store the value of the Times. These types can be described as a string or a sequence of integers with no delimiters. If described as a string, the value of the DATE type should be separated by a hyphen, and the value of the time type should be separated by a colon as a separator.
It should be noted that the time type value without the colon delimiter will be interpreted by MySQL as a duration, not a timestamp.
MySQL also simultaneous the maximum number of two digits in the year of the date, or the two digits entered in the SQL statement for the TEAR type. Because values of all TEAR types must be stored in 4 digits. MySQL attempts to convert a 2-digit year to a 4-digit value. Converts a value in the range of 00-69 to a range of 2000-2069. Converts the value in the 70-99 range to within 1970-1979. If the MySQL conversion value does not meet our needs, please enter a 4-digit year.

DATETIME and TIMESTAMP types

In addition to date and time data types, MySQL also supports both the Dateyime and TIMESTAMP hybrid types. They can store the date and time as a single value. These two types are typically used to automatically store timestamps that contain the current date and time, and can play a good role in applications that need to perform a large number of database transactions and need to establish an audit trail for debugging and review purposes.
If we do not explicitly assign values to fields of type TIMESTAMP, or are assigned null values. MySQL will automatically populate it with the current date and time of the system.

3. String type

Storage required for column types
CHAR (m) m-byte, 1 <= m <= 255
VARCHAR (m) l+1 bytes, at this L <= m and 1 <= m <= 255
Tinyblob, Tinytext l+1 Byte, at this l< 2 ^ 8
BLOB, TEXT l+2 Byte, in this l< 2 ^ 16
Mediumblob, Mediumtext l+3 Byte, at this l< 2 ^ 24
Longblob, Longtext l+4 Byte, at this l< 2 ^ 32
ENUM (' value1 ', ' value2 ',... ) 1 or 2 bytes, depending on the number of enumerated values (maximum 65535)
SET (' value1 ', ' value2 ',... 1,2,3,4 or 8 bytes, depending on the number of members of the collection (up to 64 members)

MySQL provides 8 basic string types that can be stored in ranges from simple one character to huge text block or binary string data.

Type size Use
CHAR 0-255-byte fixed-length string
VARCHAR 0-255-byte variable-length string
Tinyblob a binary string of 0-255 bytes No more than 255 characters
Tinytext 0-255-byte short text string
BLOB 0-65 535 byte binary form of long text data
Text 0-65 535 byte long text data
Mediumblob 0-16 777 215 byte binary form of medium length text data
Mediumtext 0-16 777 215 bytes Medium length text data
Logngblob 0-4 294 967 295 byte binary form of maximum text data
Longtext 0-4 294 967 295 bytes Max Text data

CHAR and VARCHAR types

The CHAR type is used for fixed-length strings and must be defined in parentheses with a size modifier. The range of this size modifier is from 0-255. A value that is larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.
The CHAR type can use the BINARY modifier. When used for comparison operations, this modifier causes CHAR to be in binary mode, rather than in a traditional case-sensitive manner.
A variant of the CHAR type is the VARCHAR type. It is a variable-length string type and must also have an indicator with a range of 0-255. The difference between char and Varchgar is the way the Musql database handles this indicator: Char treats this size as a value, and is filled with a space if it is not of sufficient length. The VARCHAR type treats it as the maximum value and stores the value using only the length that is actually required by the storage string (an extra byte is added to store the length of the string itself). Therefore, the VARCHAR type shorter than the indicator length will not be filled with spaces, but the value longer than the indicator will still be truncated.
Because the VARCHAR type can dynamically change the length of the stored value depending on the actual content, using the VARCHAR type when unsure of how many characters the field requires can greatly conserve disk space and increase storage efficiency.
The VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier.

TEXT and BLOB types

MySQL provides TEXT and BLOB two types in cases where the field length requires more than 255. Depending on the size of the stored data, they all have different subtypes. These large data are used to store binary data types such as blocks of text or images, sound files, and so on.
TEXT and BLOB types differ in classification and comparison. BLOB types are case-sensitive, and TEXT is case-insensitive. The size modifier is not used for various blobs and TEXT subtypes. The maximum range of values supported by the specified type is truncated automatically.

3. Compound type

MySQL also supports two types of composite data types ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be treated as different data types. An ENUM type allows only one value to be obtained from a collection, whereas a set type allows any number of values to be obtained from a collection.

ENUM type

The ENUM type is somewhat similar to a single option because it allows only one value to be obtained in the collection. It is easy to understand when dealing with data that is torn apart, such as the sex of human beings. The ENUM Type field can get a value from the collection or use a null value, except that the input will cause MySQL to insert an empty string in this field. In addition, if the capitalization of the inserted value does not match the capitalization of the value in the collection, MySQL automatically converts the capitalization of the inserted value to a value that is case-consistent in the collection.
The ENUM type can be stored as a number within the system and indexed with numbers starting from 1. An ENUM type can contain up to 65,536 elements, one of which is reserved by MySQL to store the error message, which is represented by index 0 or an empty string.
MySQL believes that the value that appears in the collection of ENUM types is a valid input, except that any other input will fail. This means that you can easily find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0.

SET type

The SET type is similar to, but not identical to, the ENUM type. A set type can obtain any number of values from a predefined collection. And as with the ENUM type, any attempt to insert a value that is not predefined in the SET type field will cause MySQL to insert an empty string. If you insert a record that has a legitimate element and an illegal element, MySQL retains the legitimate elements and removes the illegal elements.
A SET type can contain up to 64 elements. The values in the SET element are stored as a separate "bit" sequence, which represents the element corresponding to it. Bit is a simple and efficient way to create an ordered set of elements. And it also removes duplicate elements, so it is impossible to contain two identical elements in a SET type.
You want to find illegal records from a SET type field just look for rows that contain an empty string or a binary value of 0.