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

Today, I will talk to you about how MySQL chooses data types. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

MySQL provides a large number of data types, and in order to optimize storage and improve database performance, the most accurate data types should be used in all cases.

The previous description mainly describes the data types and their basic characteristics in MySQL, including the types of values they can store and the space they occupy. This section focuses on how to select data types when creating database tables.

It can be said that the string type is a general data type, anything can be saved in a string, numbers and dates can be expressed as strings.

However, you cannot define all columns as string types. For numeric types, a lot of space will be used if they are set to a string type. And in this case, using numeric type columns to store numbers is more efficient than using string types.

It is also important to note that query results vary depending on the way numbers and strings are handled. For example, sorting numbers is not the same as sorting strings.

For example, the number 2 is less than the number 11, but the string'2' is larger than the string '11'. This problem can be solved by placing columns in a numeric context, such as the following SQL statement:

SELECT course+ 0 as num... ORDER BY num

Adding 0 to the course column forces the column to be sorted numerically, but this is obviously unreasonable.

If you let MySQL treat a string column as a numeric column, it will cause serious problems. Doing so forces every value in the column to perform a conversion from a string to a number, which is inefficient. And using such columns in the calculation will cause MySQL not to use any indexes on these columns, which further slows down the query.

So we have to consider the storage, query and overall performance when choosing the data type.

When choosing a data type, first consider what type of value the column holds. In general, use numeric type columns to store numbers, character type columns to store strings, and temporal type columns to store dates and times.

Numerical type

For numeric type columns, use the integer type if the number you want to store is an integer (there is no decimal part); if the number you want to store is a decimal (with a decimal part), you can choose the DECIMAL or floating point type, but generally choose the FLOAT type (one of the floating point types).

For example, if the range of values for a column is an integer between 1 and 99999, the MEDIUMINT UNSIGNED type is the best choice.

MEDIUMINT is an integer type, and UNSIGNED is used to unsymbolize numeric types. For example, the value range of INT type is-2 147 483 648 ~ 2 147 483 647, then the value range of INT UNSIGNED type is 0 ~ 4 294 967 295.

If you need to store some integer values, the range of values determines the type of data you can choose. If the value range is 0,1000, then you can choose any type between SMALLINT~BIGINT. If the range of values exceeds 2 million, SMALLINT cannot be used, and the type you can choose becomes one between MEDIUMINT and BIGINT.

Of course, you can choose the largest data type for the value you want to store. However, if you choose the right data type, you can not only reduce the storage space of the table, but also improve performance. Because shorter columns process faster than longer columns. When reading shorter values, fewer disk reads and writes are required, and more key values can be put into the in-memory index buffer.

If you don't know the range of possible values, you can only guess, or use BIGINT to meet the worst-case scenario. If the type of guess is too small, then it is not hopeless. In the future, you can also use ALTER TABLE to make the column larger.

If the numerical type needs to store data in currency, such as RMB. In the calculation, the values used often have elements and are divided into two parts. They look like floating-point values, but both FLOAT and DOUBLE types have the problem of rounding errors, so they are not suitable. Because people are sensitive to their money, they need a data type that provides perfect accuracy.

Currency can be expressed as a DECIMAL (MMagne2) type, where M is the maximum width of the desired range of values. This type of value can be accurate to 2 decimal places. The advantage of DECIMAL is that there is no rounding error and the calculation is accurate.

Non-numeric characters are used for phone numbers, credit card numbers, and social security numbers. Because spaces and dashes cannot be stored directly in numeric type columns unless non-numeric characters are removed. But even if the non-numeric characters are removed, they cannot be stored as numeric types to avoid losing the starting zero.

Date and time type

MySQL provides data types for different kinds of dates and times, such as YEAR and TIME. If you only need to record the year, you can use the YEAR type; if you only record the time, you can use the TIME type.

If you need to record both the date and time, you can use the TIMESTAMP or DATETIME type. Because the range of values of the TIMESTAMP column is smaller than that of DATETIME, it is best to use DATETIME to store larger dates.

TIMESTAMP also has an attribute that DATETIME does not have. By default, when a record is inserted but the column value TIMESTAMP is not specified, MySQL sets the TIMESTAMP column to the current time. Therefore, it is convenient to use TIMESTAMP when you need to insert records and the current time, and TIMESTAMP is more efficient than DATETIME in space.

MySQL does not provide an optional date type for the time section. DATE does not have a time part, DATETIME must have a time part. If the time part is optional, you can use the DATE column to record the date, and a separate TIME column to record the time. Then, set the TIME column to NULL. The SQL statement is as follows:

CREATE TABLE mytb1 (

Date DATE NOT NULL, # date is required

Time TIME NULL # time optional (possibly NULL)

)

String type

String types do not have a "range of values" like numeric type columns, but they all have the concept of length. If you need to store a string of less than 256 characters, you can use CHAR, VARCHAR, or TINYTEXT. If you need to store longer strings, you can choose VARCHAR or some longer TEXT type.

If a string column is used to represent the value of a fixed collection, consider using the data type ENUM or SET.

Characteristics and Choice between CHAR and VARCHAR

The differences between CHAR and VARCHAR are as follows:

CHAR is a fixed-length character and VARCHAR is a variable-length character.

CHAR automatically removes trailing spaces from inserted data, while VARCHAR does not delete trailing spaces.

CHAR is of fixed length, so its processing speed is faster than that of VARCHAR, but its disadvantage is a waste of storage space. So CHAR type can be used for those with small storage but speed requirements, whereas VARCHAR type can be used to achieve it.

The impact of the storage engine on the selection of CHAR and VARCHAR:

For the MyISAM storage engine, it is best to use fixed-length data columns instead of variable-length data columns. This makes the entire table static, making data retrieval faster and trading space for time.

For the InnoDB storage engine, it is best to use variable-length data columns, because the storage format of InnoDB data tables is regardless of fixed length and variable length, so using CHAR is not necessarily better than using VARCHAR, but because VARCHAR is stored according to the actual length, it saves space, so it is better for disk IGO and the total amount of data storage.

ENUM and SET

ENUM can only take a single value, and its data list is an enumerated collection. Its list of legal values allows a maximum of 65,535 members. Therefore, you can use ENUM when you need to choose one of the multiple values. For example, the gender field is suitable for definition and is of type ENUM, and only one value can be taken from 'male' or 'female' at a time.

SET is preferable to multiple values. Its list of legal values allows a maximum of 64 members. The empty string is also a legal set value. When you need to take multiple values, it is appropriate to use the SET type, for example, to store a person's interests, it is best to use the SET type.

The values of ENUM and SET appear as strings, but internally, MySQL stores them as numeric values.

Binary type

BLOB is a binary string and TEXT is a non-binary string, both of which can store large amounts of information. BLOB mainly stores pictures and audio information, while TEXT can only store plain text files.

After reading the above, do you have any further understanding of how MySQL chooses data types? 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.