MySQL data types full analysis

Data Type: The basic rules that define what data can be stored in a column and how that data is actually stored.

Data types are used for the following purposes:

1. Allows you to restrict the data that can be stored in a column. For example, a column with a numeric data type can only accept numeric values.

2. Allows for more efficient storage of data internally. For example: store numeric values ​​and date and time values ​​in a format that is more concise than text strings.

3. Allow changing the sorting order. For example, if all data is treated as strings, 1 comes before 10, and 10 comes before 2 (strings are sorted in lexicographical order, compared from the left, one character at a time); as a numeric data type, the values ​​can be sorted correctly.

1. String data type

The most commonly used data type, storing strings such as names, addresses, phone numbers, etc.

There are two basic types of strings: fixed-length strings and variable-length strings.

Fixed-length string: accepts a string of fixed length, whose length is specified when the table is created. Fixed-length columns do not allow more than the specified number of characters; they allocate as much storage space as specified. Such as: CHAR.

Variable-length string: stores text of variable length. Some variable-length data types have a maximum fixed length, while others are completely variable-length. Regardless of the type, only the specified data will be saved (extra data will not be saved), such as TEXT.

PS: MySQL processes fixed-length columns much faster than variable-length columns. And MySQL does not allow indexing of variable-length columns (or variable parts of a column).

Data type description:

CHAR: A fixed-length string of 1 to 255 characters. The length must be specified at creation time, otherwise MySQL assumes it is CHAR(1).

ENUM: accepts a string from a predefined set of up to 64K strings.

LONGTEXT: Same as TEXT, but with a maximum length of 4GB.

MEDIUMTEXT: Same as TEXT, but with a maximum length of 16K.

SET: Accepts zero or more strings from a predefined set of up to 64 strings.

TEXT: variable-length text with a maximum length of 64 KB.

TINYTEXT: Same as TEXT, but with a maximum length of 255 bytes.

VARCHAR: variable length, no more than 255 bytes. If VARCHAR(n) is specified when creating the data, it can store variable-length strings of 0 to n characters (where n ≤ 255).

PS:

1. Quotation marks: No matter which form of string data type is used, the string value must be enclosed in quotation marks (usually single quotation marks).

2. Basic rules to be followed: If the value is used in calculations (sum, average, etc.), it is stored in a numeric data type column. If the value is used as a string, it is stored in a string data type column. For example, if you store the postal code 01234 in a numeric field, the value 1234 is saved, with one digit missing.

2. Numeric Data Types

Stores a numeric value. MySQL supports multiple numeric data types, each storing a value with a different range of values.

The larger the supported value range, the more storage space is required. Additionally, some numeric data types support the use of decimal points (and fractions), while others support only integers. Table D-2 lists the commonly used MySQL numeric data types.

PS:

1. All numeric data types (except BIT and BOOLEAN) can be signed or unsigned. Signed numeric columns can store positive or negative values, while unsigned numeric columns can only store positive numbers.

2. The default is signed. If you do not need to store negative values, you can use UNSIGNED, which will allow you to store values ​​of twice the size.

3. Unlike strings, values ​​should not be enclosed in quotes.

4. There is no data type specifically for storing currency in MySQL. DECIMAL(8, 2) is generally used.

Data type description:

BIT: bit field, 1 to 64 bits. Prior to MySQL 5, BIT was functionally equivalent to TINYINT.

BIGINT: integer value, supporting -9223372036854775808 to 9223372036854775807. If it is UNSIGNED, it is a number from 0 to 18446744073709551615.

BOOLEAN (or BOOL): Boolean flag, which is either 0 or 1, mainly used for on/off flags.

DECIMAL (or DEC): A floating-point value with variable precision.

DOUBLE: Double-precision floating point value

FLOAT: single-precision floating point value

INT (or INTEGER): integer value, supports -2147483648 to 2147483647, UNSIGNED is the same as above.

MEDIUMINT: integer value, supports -8388608 to 8388607, UNSIGNED is the same as above.

REAL: 4-byte floating point value.

SMALLINT: integer value, supports -32768 to 32767, UNSIGNED is the same as above.

TINYINT: integer value, supports -128 to 127, UNSIGNED is the same as above.

3. Date and time data types

Data type description:

DATE: indicates the date from 1000-01-01 to 9999-12-31, in the format of YYYY-MM-DD.

DATETIME: A combination of DATE and TIME.

TIMESTAMP: The function is the same as DATETIME, but the range is smaller.

TIME: The format is HH:MM:SS.

YEAR: 2 digits, ranging from 70 to 69 (1970 to 2069); 4 digits, ranging from 1901 to 2155

4. Binary Data Types

Can store any data (even binary information), such as images, multimedia, word processing documents, etc.

Data type description:

BLOB: The maximum length of a Blob is 64KB.

MEDIUMBLOB: The maximum blob length is 16 MB.

LONGBLOB: The maximum length of a blob is 4GB.

TINYBLOB: The maximum length of a blob is 255 bytes.

The above is the detailed content of the full analysis of MySQL data types. For more information about MySQL data types, please pay attention to other related articles on 123WORDPRESS.COM!