MySQL Supported data types (summary)

I. Numeric 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). and expand on this basis.

The extension adds tinyint,mediumint,bigint to these 3 different lengths of shaping and adds a bit type for storing bit data.

Integer type byte range (signed) range (unsigned) use

TINYINT 1 bytes (-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 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 69 3 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 to Decimal (m,d), if m>d, is m+2 otherwise the value of d+2 dependent on M and d is dependent on the value of M and D decimal values

INT Type:

The 5 major integer types supported in MySQL are Tinyint,smallint,mediumint,int and BIGINT. These types are largely the same, except that the size of the values they store is not the same.

MySQL extends the SQL standard in the form of an optional display width indicator, so that when retrieving a value from the database, it can be extended to a specified length. For example, specify that a field is of type INT (6),

You can ensure that values with fewer than 6 contained numbers are automatically populated with spaces when retrieved from the database. It is important to note that using a width indicator does not affect the size of the field and the range of values that it can store.

In case we need to store a number that exceeds the permitted range for a field, MySQL is truncated and then stored according to the one end of the allowable range closest to it. And one of the more special places is,

MySQL will automatically be modified to 0 before the non-specified value is inserted into the table.

The UNSIGNED modifier specifies that the field only holds positive values. Because you do not need to save the positive and negative symbols of the numbers, you can save a "bit" space at the time of storage. thereby increasing the range of values that this field can store.

The Zerofill modifier specifies a value of 0 (not a space) that can be used to really complement the output. Use this modifier to prevent the MySQL database from storing negative values.

FLOAT, DOUBLE, and DECIMAL types

The three floating-point types supported by MySQL are the float, DOUBLE, and DECIMAL types. Float numeric types are used to represent single-precision floating-point numbers, whereas double numeric types are used to represent double-precision floating-point numbers.

As with integers, these types also have additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT (7,3) specifies that the value displayed will not exceed 7 digits, followed by a 3-digit number after the decimal point.

For the number of digits after the decimal point beyond the allowable range, MySQL automatically rounds it to the nearest value, and then inserts it.

The DECIMAL data type is used in calculations that require very high precision, which allows you to specify the precision and count method of a numeric value as a 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, the statement decimal (7,3) specifies that the stored value will not exceed 7 digits and no more than 3 digits after the decimal point.

Ignoring the precision and count method modifiers of the DECIMAL data type will cause the MySQL database to set the precision of all fields identified as this data type to 10, and the calculation method set to 0.

The UNSIGNED and Zerofill modifiers can also be used by the 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, namely CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM set, and more.

can store ranges from simple one character to huge blocks of text 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 and no more than 255 characters

Tinytext 0-255 bytes Short text string

BLOB 0-65535 byte binary form of long text data

Text 0-65535-byte long text data

Mediumblob 0-16 777 215 bytes Medium-length text data in binary form

Mediumtext 0-16 777 215 bytes Medium length text data

Logngblob 0-4 294 967 295 byte binary form of large text data

Longtext 0-4 294 967 295 byte maximum text data

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

BINARY (m) m allow length 0-m bytes of fixed length byte character string

CHAR and VARCHAR types

The CHAR type is used for fixed-length strings and must be defined within parentheses with a size modifier. This size modifier ranges from 0-255. A value 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 participate in the operation in binary mode, rather than in a traditional case-sensitive manner.

A variant of the CHAR type is a 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 that MYSQL database processing

The way of this indicator: CHAR treats this size as a value, and a blank is used to make up the case if it is not long enough. The VARCHAR type treats it as the maximum and uses only the length that the stored string actually needs

(Add an extra byte to store the length of the string itself) to store the value. So a VARCHAR type that is shorter than the indicator length is not padded with spaces, but the value longer than the indicator will still be truncated.

Because varchar types can dynamically change the length of stored values based on the actual content, the use of varchar type can greatly save disk space and improve storage efficiency when it is not possible to determine how many characters a field requires.

The VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier.

TEXT and BLOB types

For cases where the field length requires more than 255, MySQL provides TEXT and BLOB two types. Depending on the size of the stored data, they all have different subtypes. These large data are used to store text blocks or images,

Binary data types such as sound files.

The TEXT and BLOB types differ in classification and comparison. BLOB types are case-sensitive, while TEXT is not case-sensitive. The size modifier is not used for various blobs and TEXT subtypes.

A value that is larger than the maximum range supported by the specified type is automatically truncated.

Three. Date and Time type

MySQL has 5 different data types to choose from when working with date and time type values. They can be divided into simple dates, time types, and mixed date and time types.

Depending on the accuracy required, subtypes can be used in each category, and MySQL has built-in functionality to turn a variety of input formats into a standard format.

Type size (bytes) Range format Purpose

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 Blend date and time values

TIMESTAMP 4 1970-01-01 00:00:00/2037 YYYYMMDD HHMMSS Mixed date and time value, timestamp

DATE, time, and TEAR types

MySQL stores simple date values with the date and TEAR types, and time values are stored using the type of times. These types can be described as strings or sequence of integers without delimiters. If the description is a string,

Values of DATE types should be separated by hyphens, while values of type time should be separated by a colon as a delimiter.

Note that the time type value without the colon delimiter will be understood by MySQL as the duration, not the timestamp.

MySQL also maximizes the interpreter of the two numbers in the year of the date, or the two numbers entered in the SQL statement for the TEAR type. Because the values of all TEAR types must be stored in 4 digits.

MySQL attempted to convert a 2-digit year to a value of 4 digits. Converts values within the range of 00-69 to 2000-2069. Converts the value within the 70-99 range to 1970-1979.

If the MySQL auto-converted value does not meet our needs, please enter a 4-digit year.

Dateyime and TIMESTAMP types

In addition to the date and time data types, MySQL 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 role in applications that need to perform a large number of database transactions and audit trails that require a debug and review purpose.

If we have no definite assignment to a field of type TIMESTAMP, or we are assigned a null value. MySQL will automatically populate it with the current date and time of the system.

Composite type

MySQL also supports two 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 taken 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's easy to understand when dealing with data from each other, such as human sexuality. The ENUM Type field can get a value from the collection or use a null value.

In addition, the input will cause MySQL to insert an empty string in this field. In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the casing of the inserted value to a value that is consistent with the case in the collection.

The ENUM type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An ENUM type can contain up to 65,536 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 that the value that appears in the ENUM type collection is a valid input, except that any other input will fail. This means that it is easy to 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 but not the same as the ENUM type. A set type can take any number of values from a predefined collection. And the same as the ENUM type, any attempt to insert a non-predefined value in the SET Type field will cause the

MySQL inserts an empty string. If you insert a record that has a valid element and an illegal element, MySQL retains the legitimate element, removing the illegal element.

A SET type can contain up to 64 elements. The value in the SET element is stored as a separate "bit" sequence, which represents the element corresponding to it. A bit is a simple and efficient way to create a collection of ordered elements.

And it also removes duplicate elements, so it is not possible to include two identical elements in a SET type.

To find illegal records from the SET type field, simply look for rows that contain an empty string or binary value of 0.

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

High database performance with a small storage cost.

MySQL Supported data types (summary)