MySQL Data Type

MySQL Data Type

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.

The following lists various numerical types and their permitted ranges and occupied memory space.

Type size range (Signed) range (unsigned) Usage
TINYINT, 1-byte (-128,127) (0,255), small integer
SMALLINT 2-byte (-32 768,32 767) (535) large integer
MEDIUMINT 3-byte (-8 388 608 388 607) (777 215) large integer
INT or INTEGER 4 bytes (-2 147 483 648,2 147 483 647) (294 967 295) large INTEGER
BIGINT 8-byte (-9 233 372 036 854 775 223 372 854 775 807 446 744) (709 551 615) extremely 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 E + 38) single 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 315 7 E + 308) Double Precision
Floating point value
DECIMAL on DECIMAL (M, D). If M> D is M + 2, otherwise, D + 2 depends on M and D. The value depends on M and D.

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.

String type
MySQL provides eight basic string types that can store data ranging from a simple character to a huge text block or binary string.

Type size usage
CHAR 0-255 bytes fixed length string
VARCHAR 0-255 bytes long string
A binary string of no more than 255 characters between TINYBLOB and bytes
TINYTEXT 0-255 byte short text string
BLOB 0-65 535-byte long text data in binary format
TEXT 0-65 535-byte long TEXT data
MEDIUMBLOB 0-16 777 215 bytes of binary text data of medium length
MEDIUMTEXT 0-16 777 215 bytes of medium-length text data
LOGNGBLOB 0-4 294 967 295 bytes extremely large text data in binary format
LONGTEXT 0-4 294 967 295 bytes of extremely large text data

CHAR and VARCHAR types
The CHAR type is used for fixed-length strings and must be defined by a size modifier in parentheses. The size modifier ranges from 0 to 255. Values greater than the specified length are truncated, while values smaller than the specified length are filled with spaces.

The CHAR type can use the BINARY modifier. When used for comparison operations, this modifier enables CHAR to be involved in operations in binary mode, rather than the traditional case-sensitive mode.

A variant of the CHAR type is the VARCHAR type. It is a variable-length string type and must also contain an indicator ranging from 0 to 255. The difference between CHAR and VARCHGAR lies in the way the MuSQL database processes this indicator: CHAR regards this size as the value size and uses spaces to fill it up without being short. The VARCHAR type regards it as the maximum value and only uses the length required to store the string (an additional byte is added to store the length of the string itself) to store the value. Therefore, the VARCHAR type shorter than the indicator length will not be filled by spaces, but the value longer than the indicator will still be truncated.

Because the VARCHAR type can dynamically change the length of the storage value according to the actual content, using the VARCHAR type can greatly save disk space and improve storage efficiency when you cannot determine the number of characters required by the field.

The VARCHAR type is identical to the CHAR type when the BINARY modifier is used.

TEXT and BLOB types
If the length of a field exceeds 255, MySQL provides two types: TEXT and BLOB. They all have different subtypes based on the size of the stored data. These large data types are used to store binary data types such as text blocks or images and audio files.

The TEXT and BLOB types differ in classification and comparison. BLOB type is case sensitive, while TEXT is case insensitive. The size modifier is not used for various BLOB and TEXT subtypes. Values that are larger than the maximum range supported by the specified type are automatically truncated.

Date and Time Type
When processing values of the date and time types, MySQL has five different data types available. They can be divided into simple date, time type, and mixed date, time type. According to the required precision, subtypes can be used in each shard type, and MySQL has a built-in function to convert diverse input formats into a standard format.

Type size
(Byte) range format usage
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD DATE value
TIME 3 '-838: 59: 59'/'838: 59: 59' HH: MM: ss time value or duration
YEAR 1 1901/2155 yyyy year 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 2037 00:00:00/yyyymmdd hhmmss mixed Date and Time Value, TIMESTAMP

DATE, TIME, and TEAR types
MySQL uses the DATE and TEAR types to store simple DATE values, and the TIME type to store TIME values. These types can be described as strings or integer sequences without delimiters. If the description is a string, values of the DATE type should be separated by the font size, while values of the TIME type should be separated by colons.

It should be noted that the TIME type value without the colon separator will be understood by MySQL as the duration rather than the timestamp.

MySQL also maximizes the translation of the values of two numbers in the year of the date, or the two numbers entered for the TEAR type in the SQL statement. Because all TEAR values must be stored in four numbers. MySQL tries to convert the year of two numbers to the value of four numbers. Convert the value in the range of 00-69 to 2000-2069. Convert the value in the range of 70-99 to 1970-1979. If the Automatically converted MySQL value does not meet our needs, enter four numbers to indicate the year.

DATEYIME and TIMESTAMP types
In addition to the date and time data types, MySQL also supports the combination of DATEYIME and TIMESTAMP. They can store dates and times as a single value. These two types are usually used to automatically store timestamps containing the current date and time, it can also play a good role in applications that need to execute a large number of database transactions and audit trails that require the establishment of a debugging and review purpose.

If we do not explicitly assign values to fields of the TIMESTAMP type, or are assigned a null value. MySQL will automatically fill it with the current date and time of the system.

Composite Type
MySQL also supports two types of composite data, ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be considered as different data types. An ENUM type can obtain only one value from a SET, while a SET type can obtain any number of values from a SET.

ENUM type
The ENUM type is similar to a single option because only one value can be obtained in the set. It is easy to understand when processing mutually exclusive data, such as human gender. An ENUM type field can get a value from the set or use null value. Other input causes MySQL to insert an empty string into this field. In addition, if the case of the inserted value does not match the case of the set value, MySQL will automatically convert the case of the inserted value to a value consistent with the upper and lower case of the set.

The ENUM type can be stored as numbers in the system and indexed from 1. An ENUM type can contain a maximum of 65536 elements, one of which is retained by MySQL and used to store error information. This error value is represented by index 0 or an empty string.

MySQL considers the value in the ENUM type set as a valid input, and any other input will fail. This means that the location of the error record can be easily found by searching for rows that contain null strings or corresponding numeric indexes of 0.

SET Type
The SET type is similar to the ENUM type but not the same. The SET type can obtain any number of values from a predefined SET. What is the same as ENUM type is that any attempt to insert a non-predefined value in the SET Type field will cause MySQL to insert an empty string. If you insert a record with both valid and invalid elements, MySQL retains valid elements to Remove invalid elements.

A set type can contain up to 64 elements. The value in the SET element is stored as a separated "bit" sequence, which indicates the elements corresponding to it. "Bit" is a simple and effective way to create an ordered element set. Besides repeated elements, the SET type cannot contain two identical elements.

To find an invalid record from the SET type field, you only need to find the row that contains a null string or a binary value of 0.

Http://blog.sina.com.cn/u/46f224ea010008zp