Data types supported by Mysql (column type summary) _mysql

A. Numeric type

MySQL supports numeric types in all standard SQL, including strict data types (integer,smallint,decimal,numberic), and approximate numeric data types (float,real,double presision). And on this basis to expand.

After the expansion, the 3 different lengths of Tinyint,mediumint,bigint were added, and bit types were added to store the bit data.

Integer type byte 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 3 Wuyi e-38,3.402 823 466 e+38) single-precision floating-point number

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 69 3 134 862 315 7 e+308) 0, (2.225 073, 858 507 4 201 e-308,1.797 693 134 862 7 315) 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, specify that the type of a field is INT (6),

You can ensure that a value that contains fewer than 6 digits is 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. And one of the more special places is,

MySQL is automatically modified to 0 before the specified value is inserted into the table.

The 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 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,

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 and Zerofill modifiers can also be used by FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

Two. String type

MySQL provides 8 basic string types, respectively: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM set, and many other string types.

The range can be stored from a simple character to a large text block or binary string data.

String type byte size description and storage requirements

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

Long text data in BLOB 0-65535-byte binary form

Text 0-65535 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

VARBINARY (M) a fixed-length byte string with a length of 0-m byte, the length of the value + 1 bytes

BINARY (m) m allows a fixed-length byte string of length 0-m bytes

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 MYSQL database processing

The way this indicator is used: CHAR treats this size as a value, with a blank space to make up the size of the problem. The VARCHAR type treats it as the maximum value and uses only the length of the actual string

(Add an extra byte 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 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 text blocks or images,

A binary data type such as a sound file.

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.

Three. Date and Time type

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 transform the diverse input formats into a standard format.

Type size (byte) range format use

Date 4 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 4 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,

Values for DATE types should be separated by hyphens as delimiters, 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.

Dateyime 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.

Composite 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.

The addition of 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.

This error value 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

MySQL inserts 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.

There is an overview of the use of each data type, physical storage, presentation scope, and so on. So in the face of specific applications, it can be based on the corresponding special to choose the appropriate data types, so that we can strive to meet the application on the basis of

Higher database performance with smaller storage costs.

MySQL supports a large number of column types, which can be grouped into 3 categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the nature of the types in each class. The overview is intended to be simplified, and more detailed instructions should take into account additional information about a specific column type, such as the allowable format for which you can specify a value.

The column types supported by MySQL are listed below. The following code letters are used in the description:

M
indicates the maximum display size. The largest legal display size is 255.
D
Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but it should be less than M-2.
The brackets ("[" and "]") indicate the part of the optional type modifier.

Note that if you specify a zerofill,mysql will automatically increase the unsigned property for the column.

tinyint[(M)] [UNSIGNED] [Zerofill]
A very small integer. The signed range is 128 to 127, and the unsigned range is 0 to 255.

smallint[(M)] [UNSIGNED] [Zerofill]
A small integer. The signed range is 32768 to 32767, and the unsigned range is 0 to 65535.

mediumint[(M)] [UNSIGNED] [Zerofill]
A medium sized integer. The signed range is 8388608 to 8388607, and the unsigned range is 0 to 16777215.

int[(M)] [UNSIGNED] [Zerofill]
A normal size integer. The signed range is 2147483648 to 2147483647, and the unsigned range is 0 to 4294967295.

integer[(M)] [UNSIGNED] [Zerofill]
This is a synonym for int.

bigint[(M)] [UNSIGNED] [Zerofill]

A large integer. The signed range is 9223372036854775808 to 9223372036854775807, and the unsigned range is 0 to

18446744073709551615. Note that all arithmetic operations are done with signed bigint or double values, so you should not use a signed large integer greater than 9223372036854775807 (63 bits), except for the bit function! Note that when two parameters are integer,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from functions that return integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, can be <=24 for a single precision float, and the precision of a double-precision float is between 25 and 53, these types such as float and double are described immediately below. Float (X) has the same range as float and double, but the display dimensions and decimal places are undefined. In MySQL3.23, this is a real floating-point value. In earlier versions of MySQL, FLOAT (precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.

float[(m,d)] [Zerofill]
A small (single precision) floating-point number. cannot be unsigned. The allowable values are -3.402823466E+38 to -1.175494351e-38,0 and 1.175494351E-38 to 3.402823466E+38. M is the display width and d is the number of digits. A float with no parameters or a parameter with <24 represents a single precision floating-point number.

double[(m,d)] [Zerofill]
A normal size (double precision) floating-point number. cannot be unsigned. The allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the width of the display and D is the decimal digit. Double or float (x) with no arguments (< = x < = 53) represents a double-precision floating-point number.

DOUBLE precision[(m,d)] [Zerofill]

real[(m,d)] [Zerofill]
These are double synonyms.

decimal[(M[,d])] [Zerofill]
An uncompressed (unpack) floating-point number. cannot be unsigned. Behaves like a char column: "Uncompressed" means that the number is stored as a string, and each digit of the value uses one character. decimal point, and for negative numbers, the "-" symbol is not evaluated in M. If D is 0, the value will not have a decimal point or fractional fraction. The maximum range of decimal values is the same as double, but for a given decimal column, the actual range can be limited by the choice of M and D. If D is omitted, it is set to 0. If M is omitted, it is set to 10. Note that in MySQL3.22, the M parameter includes a symbol and a decimal point.

NUMERIC (M,D) [Zerofill]
This is a synonym for decimal.

DATE
A date. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL Displays the date value in ' YYYY-MM-DD ' format, but allows you to assign the value to the Date column using a string or a number.

Datetime
A combination of dates and times. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. MySQL displays datetime values in the ' yyyy-mm-dd HH:MM:SS ' format, but allows you to assign values to datetime columns using strings or numbers.

timestamp[(M)]
A time stamp. The range is ' 1970-01-01 00:00:00 ' to a certain time of 2037. MySQL Displays the timestamp value in Yyyymmddhhmmss, Yymmddhhmmss, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or omitted), 12, 8, or 6, But allows you to assign values to timestamp columns using strings or numbers. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not assign a value to it yourself, it is automatically set to the date and time of the most recent operation. You can set it to the current date and time by assigning it a null value.
Time
a time. The range is ' -838:59:59 ' to ' 838:59:59 '. MySQL displays the time value in ' HH:MM:SS ' format, but allows you to assign the value to the time column using a string or a number.

year[(2|4)]
The year of a 2-or 4-bit number format (the default is 4 bits). The allowable values are 1901 to 2155, and 0000 (4-bit year format) if you use 2 bits, 1970-2069 (70-69). MySQL Displays the year value in yyyy format, but allows you to assign a string or numeric value to the year column. (The year type is the new type in MySQL3.22.) ）

CHAR (M) [BINARY]
A fixed-length string that, when stored, always fills the right to the specified length with a space. The range of M is 1 ~ 255 characters. When the value is retrieved, the trailing space is deleted. Char values are sorted and compared according to the default character set in a case-insensitive manner, unless a binary keyword is given. National CHAR (short form nchar) is an ANSI-SQL way to define that the CHAR column should use the default character set. This is the default for MySQL. Char is an abbreviation for character.

[National] VARCHAR (M) [BINARY]
A variable-length string. Note: When the value is stored, the trailing spaces are removed (this is different from the ANSI SQL specification). The range of M is 1 ~ 255 characters. VarChar values are sorted and compared according to the default character set in a case-insensitive manner, unless a binary keyword value is given. VARCHAR is an abbreviation of character varying.

Tinyblob

Tinytext
A blob or text column with a maximum length of 255 (2^8-1) characters.
Blob

TEXT
a BLOB or text column with a maximum length of 65535 (2^16-1) characters.

Mediumblob

Mediumtext
A blob or text column with a maximum length of 16777215 (2^24-1) characters.
Longblob

Longtext
A blob or text column with a maximum length of 4294967295 (2^32-1) characters.

ENUM (' value1 ', ' value2 ',...)
Enumeration. A string object that has only one value, which is selected from the Value list ' value1 ', ' value2 ', ..., or null. An enum can have a maximum of 65535 different values.

SET (' value1 ', ' value2 ',...)
A collection. A string object that can have 0 or more values, each of which must be from a list of values ' value1 ', ' value2 ', ... Elected. A set can have up to 64 members.