Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the data types of mysql, Xiaobian thinks it is very practical, so share it with you to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to have a look.

MySQL numeric data types can be roughly divided into two categories, one for integers and the other for floating-point numbers or decimals. A number of different subtypes are available for each of these categories, each subtype supports different sizes of data, and MySQL allows us to specify whether values in numeric fields are positive or negative or padded with zeros.

Table lists the various numeric types along with their allowable ranges and memory footprint.

Type Size Range (Signed) Range (Unsigned) Purpose TINYINT1 byte (-128,127)(0,255) Small integer value SMALLINT2 bytes (-32 768，32 767)(0, 65 535) Large integer value MEDIUMINT3 bytes (-8 388 608，8 388 607)(0, 16 777 215) Large integer value INT or INTEGER4 bytes (-2 147 483 648，2 147 483 647)(0, 4 294 967 295) Large integer value BIGINT8 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 FLOAT4 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 DOUBLE8 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

Floating-point value DECIMAL versus DECIMAL(M,D) M+2 if M>D otherwise D+2 depending on M and D values depending on M and D fractional values

The five main integer types supported in MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. These types are largely identical, 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, specifying INT(6) as the type of a field ensures that values containing fewer than six digits are automatically filled 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 for a field that is outside the allowable range, MySQL truncates it based on the end of the allowable range closest to it. Another special feature is that MySQL automatically modifies the value to 0 before inserting an irregular value into the table.

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

The ZEROFILL modifier specifies that 0 (not a space) can be used to true complement the output value. Use this modifier to prevent MySQL databases from storing negative values.

FLOAT, DOUBLE, and DECIMAL types The three floating point types supported by MySQL are FLOAT, DOUBLE, and DECIMAL types. 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.

As with integers, these types come with additional parameters: a display width indicator and a decimal point indicator. For example, FLOAT(7,3) specifies that the displayed value does not exceed 7 digits, with 3 digits after the decimal point.

For values with more digits than allowed after the decimal point, 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 allows the precision and counting method of values to be specified as selection parameters. Precision here refers to the total number of significant digits saved for this value, while the counting method indicates the number of digits after the decimal point. For example, DECIMAL(7,3) specifies that the stored value must not exceed 7 digits and must not exceed 3 digits after the decimal point.

Ignoring the precision and count method modifiers for the DECIMAL data type causes the MySQL database to set precision to 10 and calculation method to 0 for all fields identified as this data type.

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

MySQL provides 8 basic string types that can store data ranging from simple single characters to huge blocks of text or binary strings.

Type Size Purpose CHAR0-255 bytes Fixed length string VARCHAR0-255 bytes Variable length string TINYBLOB0-255 bytes Binary string of no more than 255 characters TINYTEXT0-255 bytes Short text string BLOB0- 65 535 bytes Long text data in binary form TEXT0- 65 535 bytes Long text data MEDIUMBLOB0-16 777 215 bytes Medium text data in binary form MEDIUMTEXT0- 16 777 215 bytes medium length text data LOGNGLOB 0-4 294 967 295 bytes very large text data in binary form LONGTEXT0-4 294 967 295 bytes very large text data

CHAR and VARCHAR types CHAR types are 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 are truncated, and values smaller than the specified length are padded with spaces.

CHAR types can use the BINARY modifier. When used in comparison operations, this modifier causes CHAR to participate in the operation in binary rather than in the traditional case-sensitive manner.

A variant of the CHAR type is VARCHAR. It is a variable-length string type and must also have a pointer in the range 0-255. CHAR differs from VARCHGAR in the way MuSQL databases handle this indicator: CHAR treats this size as the size of the value and supplements it with spaces if it is short. VARCHAR takes it as the maximum value and stores the value only as long as it actually needs to store the string (adding an extra byte to store the length of the string itself). So VARCHAR types shorter than the pointer length are not padded with spaces, but values longer than the pointer are still truncated.

Because VARCHAR types can dynamically change the length of stored values based on actual content, using VARCHAR types when you are unsure how many characters a field needs can significantly save disk space and improve storage efficiency.

VARCHAR types are identical to CHAR types when BINARY modifiers are used.

TEXT and BLOB types MySQL provides TEXT and BLOB types for field lengths greater than 255. They all have different subtypes depending on the size of the stored data. These large data are used to store binary data types such as text blocks or images, sound files, etc.

TEXT and BLOB types differ in classification and comparison. BLOB types are case-sensitive, while TEXT is case-insensitive. The size modifier is not used for the various BLOB and TEXT subtypes. Values greater than the maximum range supported by the specified type are automatically truncated.

When working 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 hybrid date and time types. Subtypes can be used in each subtype depending on the precision required, and MySQL has built-in functionality to convert diverse input formats into a standard format.

type size

(Byte) Range Format Usage DATE31000-01-01/9999-12-31 Y-MM-DD Date Value TIME3'-838: 59:59'/'838:59:59'HH:MM:SS Time Value or Duration YEAR11901/2155YYYY Year Value DATETIME81000-01-01 00:00/9999-12-31 23:59:59 Y-MM-DD HH:MM:SS Mixed Date and Time Value TIMESTAMP81970-01 00:00:00/2037 Sometime YMMDD HMMSS Mixed Date and Time Value, Timestamp

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 a hyphen, and values of type TIME should be separated by a colon.

Note that TIME values without a colon separator will be interpreted by MySQL as a duration, not a timestamp.

MySQL also translates the value of two digits in the year of a date, or two digits entered in SQL statements for TEAR types, to the maximum extent possible. Because all TEAR values must be stored with 4 digits. MySQL tries 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 MySQL automatically converts a value that does not match our needs, enter a 4-digit year.

DATEYIME and TIMESTAMP types In addition to date and time data types, MySQL supports two hybrid 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 work well in applications that need to perform a large number of database transactions and 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 assign null to it. MySQL automatically populates it with the current date and time of the system.

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 taken from a set; a SET type allows any number of values to be taken from a set.

ENUM type ENUM types are somewhat similar to single-choice types because they allow only one value to be taken from a collection. It is easy to understand when dealing with mutually exclusive data, such as human gender. The ENUM type field can take a value from the collection or use a null value, and any other input will cause MySQL to insert an empty string in this field. Also, if the case of the inserted value does not match the case of the values in the collection, MySQL automatically converts the inserted value to a value that matches the case in the collection.

ENUM types can be stored internally as numbers and indexed numerically starting with 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error information, which is represented by the index 0 or an empty string.

MySQL considers values appearing in the ENUM type set to be valid inputs, and any other input will fail. This shows that the error record can be easily located by searching for rows containing empty strings or corresponding numeric indices of 0.

SET Type SET types are similar to ENUM types but not identical. SET types can take any number of values from a predefined set. And the same as ENUM is that any attempt to insert a non-predefined value in a SET type field causes MySQL to insert an empty string. If you insert a record with both legal and illegal elements, MySQL will keep the legal elements and eliminate the illegal elements.

A SET type can contain up to 64 elements. The value in a SET element is stored as a separate sequence of "bits" that represent the element to which it corresponds. Bit is a simple and efficient way to create an ordered collection of elements. It also removes duplicate elements, so it is impossible for a SET type to contain two identical elements.

The above is what mysql data types are, Xiaobian believes that some knowledge points may be what we see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.