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

Today, I will talk to you about the numerical types in MySQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Numerical type

The numeric data types of MySQL can be roughly divided into two categories, one is an integer and the other is a floating point or decimal. Many different subtypes are available for each of these categories, each subtype supports different sizes of data, and MySQL allows us to specify whether the values in the numeric field are positive or negative or filled with zeros.

The table lists the various numeric types as well as their allowable range and memory footprint.

Types

Size

Range (signed)

Range (unsigned)

Use

TINYINT

1 byte

(- 128127)

(0255)

Small integer value

SMALLINT

2 byt

(- 32 768, 32 767)

(0pr 65 535)

Large integer value

MEDIUMINT

3 bytes

(- 8 388 608, 8 388 607)

(0pr 16 777 215)

Large integer value

INT or INTEGER

4 byt

(- 2 147 483 648 and 2 147 483 647)

(0pr 4 294 967 295)

Large integer value

BIGINT

8 byte

(- 9 233 372 036 854 775 808 9223 372 036 854 775 807)

(0pr 18 446 744 073 709 551 615)

Maximum integer value

FLOAT

4 byt

(- 3.402 823 466 Eparties 38), 0, (1.175 494 351 Elysees 38)

0, (1.175 494 351 Emuri 38 jurisdiction 3.402 823 466 Ecolors 38)

Single precision

Floating point value

DOUBLE

8 byte

(1.797 693 134 862 315 7 Ecolors 308 people 2.225 073 858 507 201 4 Elysees 308), 0, (2.225 073 858 507 201 4 Elysees 308 mins 1.797 693 134 862 315 7 Ecolors 308)

0, (2.225 073 858 507 201 4 Elysee 308 Magistrates 1.797 693 134 862 315 7 Ecolors 308)

Double precision

Floating point value

DECIMAL

For DECIMAL (M < D), if M > D, if M > D, it is M > D; otherwise, it is M > D.

Values that depend on M and D

Values that depend on M and D

Decimal value

INT Typ

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 retrieving a value from, it can be lengthened to a specified length. For example, specifying a field of type INT (6) ensures that values with fewer than six numbers are 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 feature 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, while 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.

String type

MySQL provides eight basic string types that can be stored from simple one character to large blocks of text or binary string data.

Types

Size

Use

CHAR

0-255 byt

Fixed length string

VARCHAR

0-255 byt

Variable length string

TINYBLOB

0-255 byt

A binary string of not more than 255 characters

TINYTEXT

0-255 byt

Short text string

BLOB

0-65 535 byt

Long text data in binary form

TEXT

0-65 535 byt

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 bytes

Extremely large text data in binary form

LONGTEXT

0-4 294 967 295 bytes

Extremely large text data

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. CHAR differs from VARCHGAR in the way the MuSQL database handles this indicator: CHAR treats this size as the size of the value, filling 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 (adding 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 binary data types such as images and 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.

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.

Types

Size

(bytes)

Range

Format

Use

DATE

three

1000-01-01 Universe 9999-12-31

YYYY-MM-DD

Date value

TIME

three

'- 838 rip 59 purl 59 fret racing 838 rig 59 purl 59'

HH:MM:SS

Time value or duration

YEAR

one

1901/2155

YYYY

Year value

DATETIME

eight

1000-01-01 0012-31 23:59:59

YYYY-MM-DD HH:MM:SS

Mixed date and time valu

TIMESTAMP

eight

1970-01-01 00 Vol 0012 00B 2037 sometime

YYYYMMDD HHMMSS

Mixed date and time values, 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 described as 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. The ENUM type field can get a value from the collection or use the null value, and other input will cause MySQL to insert an empty string in the 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, which are 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 cause MySQL to insert 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.

After reading the above, do you have any further understanding of the numerical types in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.