Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the data types supported by Mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

one。 Numerical type

Mysql supports and extends all numeric types in standard SQL, including strict data types (INTEGER,SMALLINT,DECIMAL,NUMBERIC) and approximate numeric data types (FLOAT,REAL,DOUBLE PRESISION). (recommended course: MySQL tutorial)

After the expansion, three kinds of shaping with different lengths of TINYINT,MEDIUMINT,BIGINT are added, and the BIT type is added to store bit data.

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

TINYINT 1 byte (- 128127) (0255) small integer value

SMALLINT 2 bytes (- 32 768, 32 767) (0, 65,535) large integer values

MEDIUMINT 3 bytes (- 8 388 608) (0777 215) large integer values

INT or INTEGER 4 bytes (- 2 147 483 648) (0,294 967 295) large integer values

BIGINT 8 bytes (- 9 233 372 036 854 775 808) (0meme 18 446 744 073 709 551 615) maximum integer value

FLOAT 4 bytes (- 3.402 823 466 Esquo 38), 0, (1.175 494 351 Elam 38), 0, (1.175 494,351 Elam 38 3.402 823 466 Elu38) single precision floating point values

DOUBLE 8 bytes (1.797 693 134 862 315 7 Ecolors 308), 0, (2.225 073 858 507 201 4 Elysees 308), 0, (2.225 073 858 507 201 4 Elysees 308) 0, (2.225 073 858 507 201 4 Ecolors 308) double-precision floating-point values

DECIMAL for DECIMAL (M < D), if M > D, it is M > D, otherwise, it depends on M and D. the values of M and D depend on the decimal values of M and D.

INT type:

The five main integer types supported in MySQL are TINYINT,SMALLINT,MEDIUMINT,INT and BIGINT. These types are largely the same, except for the size of the values they store.

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

You can ensure that values that contain less than 6 numbers can be automatically filled 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 it can store.

In case we need to store a number for a field that is out of the permitted range, MySQL truncates it according to the end closest to it before storing it. Another special thing is that

MySQL is automatically changed to 0 before irregular values are inserted into the table.

The UNSIGNED modifier states that only positive values are saved in the field. Because there is no need to save the positive and negative symbols of numbers, you can save a "bit" space when storing. This increases the range of values that this field can store.

The ZEROFILL modifier specifies the value that 0 (not a space) 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 FLOAT, DOUBLE, and DECIMAL. The FLOAT numeric type is used to represent single-precision floating-point values, while the DOUBLE numeric type is used to represent double-precision floating-point values.

Like integers, these types have additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT (7 and 3) specifies that the value displayed should not exceed 7 digits, followed by 3 digits after the decimal point.

If the number of digits after the decimal point exceeds the allowable range, MySQL automatically rounds it to the value closest to it, and then inserts it.

The DECIMAL data type is used in very precision-demanding calculations, and this type allows you to specify the precision of the numeric value and the counting method as a selection parameter. Precision here refers to the total number of valid digits saved for this value

The counting method represents the number of digits after the decimal point. For example, the statement DECIMAL (7 and 3) specifies that the stored value should not exceed 7 digits and no more than 3 digits after the decimal point.

Ignoring 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 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 the INT data type.

two。 String type

MySQL provides eight basic string types, such as CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM and other SET types.

Storage can range from simple one character to large blocks of text or binary string data.

String type byte size description and storage requirements

CHAR 0-255byte fixed length string

VARCHAR 0-255byte variable length string

TINYBLOB 0-255Bytes binary string with no more than 255characters

TINYTEXT 0-255byte short text string

BLOB 0-65535 byte long text data in binary form

TEXT 0-65535 bytes of long text data

MEDIUMBLOB 0-16777215-byte binary medium length text data

MEDIUMTEXT 0-16777215 bytes of medium length text data

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

LONGTEXT 0-4 294 967 295 bytes of extremely large text data

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

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

CHAR and VARCHAR types

The CHAR type is used for fixed-length strings and must be defined with a size modifier within parentheses. The range of this size modifier is 0-255. Values larger than the specified length will be truncated, while values smaller than the specified length will be filled with spaces.

CHAR types can use the BINARY modifier. When used for comparison operations, this modifier allows CHAR to participate in operations in a binary way, rather than in the traditional case-sensitive way.

One 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 of 0-255. The difference between CHAR and VARCHGAR is that MYSQL database processing

The way this indicator works: CHAR treats this size as the size of the value and completes it with spaces if it is not long enough. The VARCHAR type treats it as the maximum and uses only the length actually required to store the string

(add an extra byte to store the length of the string itself) to store the value. So VARCHAR types that are shorter than the indicator length will not be filled with blanks, but values longer than the indicator will still be truncated.

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

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

TEXT and BLOB types

For cases where more than 255fields are required, MySQL provides two types: TEXT and BLOB. 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.

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

Values larger than the maximum range supported by the specified type are automatically truncated.

three。 Date and time type

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

According to the required precision, subtypes can be used in each subtype, and MySQL has built-in features to turn a variety of input formats into a standard format.

Type size (bytes) range format usage

DATE 4 1000-01-01 YYYY-MM-DD date value

TIME 3'- 838 59 59 HH:MM:SS time value or duration

YEAR 1 1901x2155 YYYY year value

DATETIME 8 1000-01-01 00 YYYY-MM-DD HH:MM:SS mixed date and time values

TIMESTAMP 4 1970-01-01 00 YYYYMMDD HHMMSS mixed date and time values, time stamp

DATE, TIME, and TEAR types

MySQL uses DATE and TEAR types to store simple date values, and TIME types to store time values. These types can be described as strings or sequences of integers without delimiters. If the description is a string

Values of type DATE should be separated by hyphens, while values of type TIME should be separated by colons.

It is important to note that a TIME type value without a colon separator will be understood by MySQL as a duration rather than a timestamp.

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

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

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

DATEYIME and TIMESTAMP types

In addition to the date and time data types, MySQL also supports two mixed types, DATEYIME and TIMESTAMP. They can store dates and times as individual values.

These two types are typically used to automatically store timestamps containing 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 a value to a field of type TIMESTAMP, or if we are assigned a null value. MySQL automatically populates it with the current date and time of the system.

Compound 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 considered different data types.

An ENUM type allows only one value to be fetched from a collection; the SET type allows any number of values to be fetched from a collection.

ENUM Typ

The ENUM type is somewhat similar to a single option because only one value is allowed in the collection. It is easy to understand when dealing with mutually exclusive data, such as human gender. An ENUM type field can get a value from the collection or use a null value

Other input 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 value in the collection, MySQL automatically converts the case of the inserted value to a value that matches the case in the collection.

ENUM types can be stored as numbers within the system and indexed by numbers starting at 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error messages

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

MySQL believes that the values that appear in the collection of ENUM types are legitimate inputs, and any other input will fail. This shows that the location of the error record can be easily found by searching for a row that contains an empty string or a corresponding numeric index of 0.

SET Typ

The SET type is similar to but not the same as the ENUM type. The SET type can get any number of values from a predefined collection. And like the ENUM type, any attempt to insert a non-predefined value into the SET type field will result in

MySQL inserts an empty string. If you insert a record with 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. The value in the SET element is stored as a separate sequence of "bits" that represent its corresponding elements. "bits" is a simple and effective way to create a collection of ordered elements.

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

To find illegal records from SET type fields, all you need to do is look for lines that contain an empty string or a binary value of 0.

Have a general understanding of the purpose, physical storage, presentation scope, etc., of each data type. In this way, in the face of specific applications, we can select the appropriate data types according to the corresponding characteristics, so that we can strive to meet the application on the basis of

Lower storage cost in exchange for higher database performance.

This is the end of sharing the data types supported by Mysql. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.