MySQL data type selection principles

Small but beautiful

Generally speaking, try to use data types that take up as little storage space as possible to store data. Such data types are also generally faster and take up less disk space, memory, and even cache, and consume fewer CPU processing cycles.

However, it is important to accurately estimate the range of data values ​​that you want to store. Because extending data ranges in multiple places in your table structure can be a painful and time-consuming process. If you are hesitant about which data type is appropriate, choose the type with the smallest space that you think will not exceed the range (you can also adjust it in the early stages of the system or when the data table does not contain much data).

Keep it simple

Simpler data types mean fewer CPU cycles to process the data. For example, integers are easier to work with than characters because character sets and collations make comparing characters more complicated. To give two examples: You should use MySQL's built-in types to store times and dates, rather than strings. IP addresses should also be stored using integers.

Avoid null values

Many data tables require nullable columns, although the default value NULL does not need to be stored in the application. Generally speaking, it is better to specify that the column is NOT NULL rather than storing NULL.

MySQL is more difficult to optimize involving nullable columns because nullable columns complicate indexes, index statistics, and value comparisons. Furthermore, nullable columns take up more storage space and require special handling. If an index is specified on a nullable column, this will require an extra byte for each index entry and may even cause the MyISAM engine to convert a fixed-size index to a variable-size index (for example, a single-column index on an integer field). However, the performance improvement of converting NULL columns to NOT NULL columns is usually not large. Therefore, unless you have discovered that NULL columns have a significant impact on performance, do not prioritize changing the existing data table structure. However, if you need to build an index on a column, you should try to avoid allowing the column value to be empty. It is usually a good habit to directly set the column to NOT NULL.

Of course, there are exceptions. For example, in InnoDB, only one bit is used to store NULL values, so it can effectively save space for large amounts of data storage, but this is not the case with the MyISAM engine.

Steps to Select Data Type

The first step in selecting a data type is to decide which common data type to use to represent the data column, whether it is a numeric type, a string type, or a time type. Usually a straight selection is fine, but there are some exceptions (such as amounts, timestamps).

The second step is to choose the specific type. MySQL has multiple storage methods for the same data type, based on the data value range, precision, and physical storage space, and some data types have some special properties.

For example, DATETIME and TIMESTAMP can both store time and date, both with accuracy to the second. However, the TIMESTAMP type requires only half the storage space, includes time zone information, and supports automatic updates. But on the other hand, it stores data over a smaller time frame, and these special characteristics may become a hindrance.

Let's look at the basic data types. MySQL supports aliases for data types, such as INTEGER, BOOL, and NUMERIC. These are just aliases and while they may look confusing, they actually have no impact on performance. If you use an alias data type to create a data table, recall that you used SHOW CREATE TABLE and you can see that MySQL actually converts to the base data type instead of the alias.

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.

Data Type Introduction

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.

Conclusion:

MySQL has many ways to represent data. It is recommended to understand the storage range of common data types, the number of bytes they occupy, and choose the appropriate data type based on the product's estimated data value range or length as much as possible, so as to focus on performance from the beginning of table creation. The cost of making adjustments later often exceeds the time cost of careful thinking at the beginning of the design.

The above is the details of why MySQL needs to choose the appropriate data type. For more information about MySQL data types, please pay attention to other related articles on 123WORDPRESS.COM!