Detailed introduction to the data types supported by Mysql

This article brings you a detailed introduction to the data types supported by Mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. Numerical types

Mysql supports all numerical types in standard SQL, including strict data types (INTEGER, SMALLINT, DECIMAL, NUMBERIC), and approximate numerical types Data type (FLOAT, REAL, DOUBLE PRESISION), and expand on this basis. (Recommended course: MySQL Tutorial)

After expansion, three integers with different lengths, TINYINT, MEDIUMINT, and BIGINT, are added, and a BIT type is added to store bit data.

Integer type Bytes 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) Big 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 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 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 For DECIMAL(M,D), if M>D, it is M 2 otherwise it is D 2 Depends on the value of M and D Depends on the value of M and D Decimal value

INT Type:

The 5 main integer types supported in MySQL are TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT. These types are largely the same, only the size of the values ​​they store differ.

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

ensures that values ​​containing less than 6 digits are automatically padded with spaces when retrieved from the database. Note that using a width indicator does not affect the size of the field or the range of values ​​it can store.

In case we need to store a number in a field that exceeds the allowed range, MySQL will truncate it according to the end of the allowed range closest to it before storing it. Another special thing is that

MySQL will automatically change the illegal value to 0 before inserting it into the table.

The UNSIGNED modifier specifies that the field only holds positive values. Because there is no need to save the positive and negative signs of the numbers, one "bit" of space can be saved during storage. This increases the range of values ​​that this field can store.

The ZEROFILL modifier specifies that 0 (not spaces) can be used to complement the output value. 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. The FLOAT numeric 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 take additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT(7,3) specifies that the displayed value will not exceed 7 digits, with 3 digits after the decimal point.

For a value with more digits after the decimal point than the allowed range, MySQL will automatically round it to the nearest value and then insert it.

The DECIMAL data type is used in calculations that require very high precision. This type allows you to specify the precision and counting method of the value as selection parameters. Precision here refers to the total number of significant digits saved for this value, while counting method indicates 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 will not exceed 3 digits after the decimal point.

Omitting the precision and counting 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 counting method to 0.

UNSIGNED and ZEROFILL modifiers can also be used with the FLOAT, DOUBLE and DECIMAL data types. And the effect is the same as the INT data type.

2. String type

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

Can store data ranging from as simple as a single character to huge blocks of text or binary strings.

String type Byte size Description and storage requirements

CHAR 0-255 bytes Fixed-length string

VARCHAR 0-255 bytes Variable-length string

TINYBLOB 0-255 bytes Binary string not exceeding 255 characters

TINYTEXT 0-255 bytes Short text string

BLOB 0-65535 bytes Binary form Long text data

TEXT 0-65535 bytes Long text data

MEDIUMBLOB 0-16 777 215-byte medium-length text data in binary form

MEDIUMTEXT 0-16 777 215 bytes medium length text data

LOGNGBLOB 0-4 294 967 295 bytes very large text data in binary form

LONGTEXT 0-4 294 967 295 bytes very large text data

Varbinary (m) Allow a fixed-length byte string of 0-m byte, the length of the value 1 byte

# Binary (m) m allows length 0-m byte byte Fixed-length byte strings

CHAR and VARCHAR types

The CHAR type is used for fixed-length strings and must be defined with a size modifier within parentheses. This size modifier ranges from 0-255. Values ​​larger than the specified length will be truncated, while values ​​smaller than the specified length will be padded with spaces.

The CHAR type can use the BINARY modifier. When used in comparison operations, this modifier causes CHAR to participate in the operation in binary form, rather than in the 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 in the range 0-255. The difference between CHAR and VARCHGAR is the way the MYSQL database handles the

indicator: CHAR treats this size as the size of the value, and fills it with spaces if the length is not insufficient. The VARCHAR type, on the other hand, treats this as the maximum value and stores the value using only the length

actually required to store the string (plus an extra byte to store the length of the string itself). So VARCHAR types shorter than the indicator length will not be padded with spaces, but values ​​longer than the indicator will still be truncated.

Because the VARCHAR type can dynamically change the length of the stored value based on the actual content, using the VARCHAR type can greatly save disk space and improve storage efficiency when you are not sure how many characters a field requires.

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

TEXT and BLOB types

For cases where the field length requirement 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 are used to store text blocks or binary data types such as images and

sound files.

There are differences in classification and comparison between TEXT and BLOB types. The BLOB type is case-sensitive, while TEXT is not case-sensitive. Size modifiers are not used on various BLOB and TEXT subtypes.

Values ​​larger than the maximum range supported by the specified type will be automatically truncated.

3. Date and time types

When dealing with date and time type values, MySQL comes with 5 different data types to choose from. They can be divided into simple date and time types, and mixed date and time types.

Subtypes can be used within each subtype depending on the required precision, and MySQL has built-in functionality to convert diverse input formats into a standard format.

Type Size (bytes) Range Format Purpose

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 values ​​

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

DATE, TIME and TEAR types

MySQL use The DATE and TEAR types store simple date values, and the TIME type stores time values. These types can be described as strings or sequences of integers without delimiters. If described as a string, values ​​of type

DATE should be separated by hyphens as delimiters, and values ​​of type TIME should be separated by colons as delimiters.

It should be noted that a TIME type value without a colon separator will be understood by MySQL as a duration, not a timestamp.

MySQL also interprets to the maximum extent the value of two digits in the year of a date, or two digits entered for the TEAR type in an SQL statement. Because all TEAR type values ​​must be stored with 4 numbers.

MySQL attempts to convert a 2-digit year to a 4-digit value. Converts values ​​in the range 00-69 to the range 2000-2069. Converts values ​​in the range 70-99 to 1970-1979.

If the value automatically converted by MySQL does not meet our needs, please enter the year represented by 4 digits.

DATEYIME and TIMESTAMP types

In addition to date and time data types, MySQL also supports two mixed types, DATEYIME and TIMESTAMP. They can store date and time as a single value.

These two types are commonly used to automatically store timestamps containing the current date and time, and can work well 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 a value to a field of TIMESTAMP type, or it is assigned a null value. MySQL automatically populates it with the system's current date and time.

Composite Types

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 only allows one value to be obtained from a collection; while the SET type allows any number of values ​​to be obtained from a collection.

ENUM type

The ENUM type only allows one value to be obtained in the collection, which is somewhat similar to a single option. Easier to understand when dealing with mutually exclusive data, such as human gender. ENUM type fields can get a value from a collection or use a null value.

Input other than this will cause MySQL to insert an empty string in this field. In addition, if the case of the inserted value does not match the case of the values ​​in the collection, MySQL will automatically use the case of the inserted value to convert it to a value consistent with the case of the collection.

The ENUM type can be stored as a number internally in the system, and is indexed starting from 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error information.

This error value is represented by index 0 or an empty string.

MySQL considers the values ​​appearing in the ENUM type collection to be legal input, and any other input will fail. This shows that the location of the erroneous record can be easily found by searching for rows that contain 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. The 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 a SET type field will cause

MySQL to insert an empty string. If you insert a record that contains both legal and illegal elements, MySQL will retain the legal elements and remove the illegal elements.

A SET type can contain up to 64 elements. In a SET element the value is stored as a discrete sequence of "bits" that represent its corresponding element. Bits are a simple and efficient way to create ordered collections of elements.

And it also removes duplicate elements, so it is impossible for the SET type to contain two identical elements.

If you want to find illegal records from a SET type field, just look for rows that contain empty strings or binary values ​​of 0.

By having a general understanding of the purpose, physical storage, representation range, etc. of each data type. In this way, when faced with specific applications, we can choose the appropriate data type according to the corresponding characteristics, so that we can strive to achieve higher storage costs with smaller storage costs on the basis of satisfying the application. Database performance.

The above is the detailed content of Detailed introduction to the data types supported by Mysql. For more information, please follow other related articles on the PHP Chinese website!