Data Types supported by Mysql (Summary of column types) and mysql Data Types

Data Types supported by Mysql (Summary of column types) and mysql Data Types

I. Numerical type

Mysql supports all numeric types in standard SQL, including strict data types (INTEGER, SMALLINT, DECIMAL, NUMBERIC), and approximate numeric data types (FLOAT, REAL, DOUBLE PRESISION ), in addition, it is extended.

Added TINYINT, MEDIUMINT, and BIGINT integers with different lengths and added BIT types to store BIT data.

 

Integer byte 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 the type of a field is INT (6 ),

This ensures that the values containing less than six numbers are 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 feature is,

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

 

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. Precision here refers to the total number of valid numbers saved for this value,

The count 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.

 

Ii. string type

MySQL provides eight basic string types, including CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, and ENUM.

It can store data ranging from a simple character to a huge text block or binary string.

 

Character string byte size description and storage requirements

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-bytes long text data in binary format

TEXT 0-65535 bytes 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

VARBINARY (M) allows a fixed-length string of 0-M bytes. The value length is + 1 byte.

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

 

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

The method of this indicator: CHAR regards this size as the value size. If the length is not long enough, use spaces to fill it up. The VARCHAR type regards it as the maximum value and only uses the actual length required for storing strings.

(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 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 is used to store text blocks or images,

Audio files and other binary data types.

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.

 

Iii. Date and Time types

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 4 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 4 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 a null value,

Otherwise, MySQL inserts an empty string in 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 to store error messages,

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 the ENUM type is that any attempt to insert a non-predefined value in the SET Type field will make

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

 

A brief understanding of the usage, physical storage, and range of each data type. In this way, we can select a suitable data type based on the specific characteristics of the application, so that we can strive to meet the application requirements,

The database performance is high at a lower storage cost.

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

The column types supported by MySQL are listed below. The following code is used to describe:

M
Specifies the maximum display size. The max valid display size is 255.
D
Applicable to the floating point type and the number of digits following the decimal point. The maximum possible value is 30, but should not be greater than the M-2.
Square brackets ("[" and "]") indicate the part of the optional type modifier.

Note: If you specify ZEROFILL, MySQL automatically adds the UNSIGNED attribute to this column.

TINYINT [(M)] [UNSIGNED] [ZEROFILL]
A 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]
An integer of medium size. The signed range is-8388608 to 8388607, And the unsigned range is 0 to 16777215.

INT [(M)] [UNSIGNED] [ZEROFILL]
A normal 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

18446744073709551615. Note that all arithmetic operations are completed with signed BIGINT or DOUBLE values. Therefore, you should not use signed big integers greater than 9223372036854775807 (63 bits), except for bitwise functions! Note that when the two parameters are INTEGER values,-, +, and * use BIGINT operations! This means that if you take two big integers (or from the function that returns integers) and the result is greater than 9223372036854775807, you can get unexpected results. A floating-point number cannot be unsigned. For a single-precision floating-point number, its precision can be <= 24. For a double-precision floating-point number, it is between 25 and 53, these types, such as FLOAT and DOUBLE, are immediately described below. FLOAT (X) has the same FLOAT and DOUBLE ranges, but the display size and decimal places are undefined. In MySQL3.23, this is a real floating point value. In earlier MySQL versions, FLOAT (precision) always has two decimal places. This syntax is provided for ODBC compatibility.

FLOAT [(M, D)] [ZEROFILL]
A small (single precision) floating point number. It cannot be unsigned. The allowed values are-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38. M indicates the display width, and D indicates the decimal digits. FLOAT without parameters or a parameter with <24 represents a single precision floating point number.

DOUBLE [(M, D)] [ZEROFILL]
A normal-sized (double-precision) floating point number. It cannot be unsigned. The allowed values are-1.7976931348623157E + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157E + 308. M indicates the display width, and D indicates the number of decimal places. A double or FLOAT (X) (25 <= X <= 53) without a parameter represents a DOUBLE-precision floating point number.

Double precision [(M, D)] [ZEROFILL]

REAL [(M, D)] [ZEROFILL]
These are DOUBLE synonyms.

DECIMAL [(M [, D])] [ZEROFILL]
Unpack floating point number. It cannot be unsigned. The behavior is like a CHAR column: "uncompress" means that the number is stored as a string, and each bit of the value uses one character. Decimal point. For negative numbers, the "-" symbol is not calculated in M. If D is 0, there will be no decimal point or decimal part. The maximum range of a DECIMAL value is the same as that of a DOUBLE value. However, for a given DECIMAL column, the actual range can be limited through the selection of M and D. If D is omitted, it is set to 0. If M is saved, it is set to 10. Note: In MySQL3.22, the M parameter includes the symbol and the decimal point.

NUMERIC (M, D) [ZEROFILL]
This is a synonym for DECIMAL.

DATE
A date. The supported range is '2017-01-01 'to '2017-12-31 '. MySQL displays the DATE value in 'yyyy-MM-DD 'format, but allows you to assign the value to the DATE column using strings or numbers.

DATETIME
A combination of dates and times. The supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. MySQL displays DATETIME values in '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 value range is '2017-01-01 00:00:00 'to a certain time on January 1, 1970. MySQL uses YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD to display the TIMESTAMP value, depending on whether M is 14 (or omitted), 12, 8, or 6, however, you can use strings or numbers to assign values to the TIMESTAMP column. 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 assign it a NULL value to set it to the current date and time.
TIME
A time. The value range is '-838: 59: 59' to '2014: 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 number.

YEAR [(2 | 4)]
A year in the format of two or four digits (4 digits by default ). The allowed values are 1901 to 2155, and 0000 (4-digit 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 number value to the YEAR column. (YEAR is a new type in MySQL3.22 .)

CHAR (M) [BINARY]
A fixed-length string, when stored, is always filled with spaces to the right to the specified length. The range of M is 1 ~ It can contain 255 characters. When the value is retrieved, the tail of the space is deleted. CHAR values are sorted and compared in a case that is not distinguished by the default Character Set, unless BINARY keywords are given. National char (short form NCHAR) is an ansi SQL method to define CHAR columns should use the default character set. This is the default value of MySQL. CHAR is an abbreviation of CHARACTER.

[NATIONAL] VARCHAR (M) [BINARY]
A variable-length string. Note: When the value is stored, spaces at the end are deleted (different from the ansi SQL Specification ). The range of M is 1 ~ It can contain 255 characters. VARCHAR values are sorted and compared based on the default character set in uppercase/lowercase, unless BINARY keyword values are 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 with only one value. This value type is set to 'value1 ', 'value2',..., or NULL. An ENUM can have a maximum of 65535 different values.

SET ('value1', 'value2 ',...)
A collection. A String object with zero or multiple values, each of which must be selected from the Value List 'value1 ', 'value2. A set can have up to 64 members.