field type and reasonable selection field type

This blog is a little bit long, it actually includes two content: one is the description of the MySQL field type, and the second is how to correctly select these field types in the MySQL build table process; field Type value

MySQL's numeric data types 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, and each subtype supports data of different sizes, and MySQL allows us to specify whether the value in a numeric field is positive or negative (UNSIGNED) or filled with 0 (zerofill).
Int
The 5 major integer types supported in MySQL are Tinyint,smallint,mediumint,int and BIGINT. These types are largely the same, except that the size of the values they store is not the same.

MySQL expands the SQL standard in the form of an optional display width indicator (such as INT (6), 6 is its width indicator, and the width indicator does not affect the size of the INT column storage field, that is, more than 6 bits it does not intercept automatically and is still stored, Only beyond its own storage range will be intercepted; Here the width indicator is the function of whether the field has zerofill, if there is not satisfied with the 6-bit portion will be filled with zero, so when retrieving a value from the database, you can add this value to the specified length. For example, specifying that a field is of type INT (6) guarantees that values with fewer than 6 contained numbers can be automatically populated with spaces when they are retrieved from a database. It is important to note that using a width indicator does not affect the size of the field and the range of values that it can store.

in case we need to store a number in a field that exceeds the permitted range, MySQL is truncated and then stored according to the one end of the allowable range closest to it. One of the more special areas is that MySQL will automatically change to 0 before the non-compliant value is inserted into the table. The unsigned and Zerofill
unsigned modifiers stipulate that the field only holds positive values, i.e. unsigned, while the MySQL field is signed by default. Because you do not need to save the positive and negative symbols of the numbers, you can save a "bit" of space (that is, doubling) at the time of storage. thereby increasing the range of values that this field can store. Note that this modifier is immediately followed by a numeric type; The
Zerofill modifier specifies a value of 0 (not a space) that can be used to really complement the output. Use this modifier to prevent the MySQL database from storing negative values, and if a column is set to Zerofill, it will automatically unsigned. This value should be used in conjunction with the width indicator for fields such as Int,tinyint,smallint,midiumint; Xxint (m), if there is no Zerofill, the M width indicator is meaningless. (Note that when you test leading 0, you still go to the Black Window test;)

Why is MySQL storing values signed and unsigned? Because of a byte, accounting for 8bit, there are 1 bit 0 and 12 possible, 8 bit is 2^8 = 256 possible, that is, 0~255; but if there is a sign, you have to take a 1bit to store the minus sign, Originally 8bit only left 7bit,2^7 = 128, that is, -128~127 (positive part contains a 0); FLOAT, DOUBLE, and DECIMAL types
The three floating-point types supported by MySQL are the float, DOUBLE, and DECIMAL types. Float numeric types are used to represent single-precision floating-point numbers, whereas double numeric types are used to represent double-precision floating-point numbers.
As with integers, these types also have additional parameters: a display width indicator and a decimal point indicator (you must have an indicator, otherwise you will not find the result, and the width indicator differs from the width indicator of the xxint type, where there is an actual limit width). For example, the statement FLOAT (7,3) specifies that the value displayed will not exceed 7 digits (including decimal places), with 3 digits after the decimal point. For the number of digits after the decimal point beyond the allowable range, MySQL automatically rounds it to the nearest value, and then inserts it.
The DECIMAL data type is used in calculations that require very high precision, which allows you to specify the precision and count method of a numeric value as a selection parameter. The precision here refers to the total number of valid digits saved for this value, while the Count method represents the number of digits after the decimal point. For example, the statement decimal (7,3) specifies that the stored value will not exceed 7 digits and no more than 3 digits after the decimal point.
Float type in a relatively high length, such as float (10,2) and decimal (10,2) at the same time to insert a match (10,2) width of the value, float will appear in the last decimal point some discrepancy;
The UNSIGNED and Zerofill modifiers can also be used by the FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

About Flaot and double
Here I suggest, simply forget that MySQL has double this data type. As for why. Just leave it. String type

MySQL provides 8 basic string types that can store ranges from simple one character to huge chunks of text or binary string data.

BINARY
Binary is not a function, it is a type conversion operator, and it is used to force the string following it to be a binary string, which can be understood to be case-sensitive when comparing strings

SELECT BINARY ' abcd ' = ' abcd ' as COM1, ' abcd ' = ' abcd ' as COM2; --COM1 output for 0,com2 output is 1;

CHAR and VARCHAR types
The CHAR type is used for fixed-length strings and must be defined within parentheses with a size modifier. This size modifier ranges from 0-255. A value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.

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

A variant of the CHAR type is a VARCHAR type. It is a variable-length string type and must also have an indicator with a range of 0-255.

The difference between char and Varchgar is the way the MYSQL database handles this indicator: Char treats the size as a value, and a space is used to make up the case without the length. The VARCHAR type treats it as the maximum and stores the value using only the length that the string actually needs to be stored (adding an extra byte to store the length of the string itself). So a VARCHAR type that is shorter than the indicator length is not padded with spaces, but the value longer than the indicator will still be truncated.

Because varchar types can dynamically change the length of stored values based on the actual content, the use of varchar type can greatly save disk space and improve storage efficiency when it is not possible to determine how many characters a field requires. But if you know exactly the length of the string, for example, between 50~55, then use char because the char type has higher performance than VARCHAR because of its fixed length.

The VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier. TEXT and BLOB types
For cases where the field length requires more than 255, MySQL provides TEXT and BLOB two types. Depending on the size of the stored data, they all have different subtypes. These large data are used to store binary data types such as text blocks or images, sound files, and so on.

the TEXT and BLOB types differ in classification and comparison. BLOB types are case-sensitive, while TEXT is not case-sensitive. The size modifier is not used for various blobs and TEXT subtypes. A value that is larger than the maximum range supported by the specified type is automatically truncated. Time Type

MySQL has 5 different data types to choose from when working with date and time type values.
DATE, time, and year types
MySQL stores simple date values with the date and year types, and time values are stored using the type of times. These types can be described as strings or sequence of integers without delimiters. If the description is a string, the value of the DATE type should be separated by a hyphen, and the value of the time type should be separated by a colon as a delimiter.

Note that the time type value without the colon delimiter will be understood by MySQL as the duration, not the timestamp.

MySQL also maximizes the interpreter of the two numbers in the year of the date, or the two numbers entered in the SQL statement for the years type. Because values of all year types must be stored with 4 digits. MySQL attempted to convert a 2-digit year to a value of 4 digits. Converts values within the range of 00-69 to 2000-2069. Converts the value within the 70-99 range to 1970-1979. If the MySQL auto-converted value does not meet our needs, please enter a 4-digit year. DATETIME and TIMESTAMP types
In addition to the date and time data types, MySQL also supports both DATETIME and TIMESTAMP mixed types. They can store the date and time as a single value. These two types are typically used to automatically store timestamps that contain the current date and time, and can play a role in applications that need to perform a large number of database transactions and audit trails that require a debug and review purpose.

if we have no definite assignment to a field of type TIMESTAMP, or we are assigned a null value. MySQL will automatically populate it with the current date and time of the system. Composite 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 treated as different data types. An ENUM type allows only one value to be obtained from a collection, whereas a set type allows any number of values to be taken from a collection. ENUM type
The ENUM type is somewhat similar to a single option because it allows only one value to be obtained in the collection. It's easy to understand when dealing with data from each other, such as human sexuality. The ENUM Type field can take a value from the collection or use a null value, otherwise the input will cause MySQL to insert an empty string in this field. In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the casing of the inserted value to a value that is consistent with the case in the collection.

The ENUM type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An ENUM type can contain up to 65,536 elements, one of which is retained by MySQL to store the error message, which is represented by index 0 or an empty string.

MySQL considers that the value that appears in the ENUM type collection is a valid input, except that any other input will fail. This means that it is easy to find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0. SET type
The SET type is similar but not the same as the ENUM type. A set type can take any number of values from a predefined collection. And the same as the ENUM type, any attempt to insert a non-predefined value in the SET Type field causes MySQL to insert an empty string. If you insert a record that has a valid element and an illegal element, MySQL retains the legitimate element, removing the illegal element.
A SET type can contain up to 64 elements. The duplicate elements are also removed, so it is not possible to include two identical elements in a SET type.
To find illegal records from the SET type field, simply look for rows that contain an empty string or binary value of 0. Summary of field types

Although many of the field types are listed above, they are most commonly known as varchar (255), char (255), Text,tinyint (4), smallint (6), and Mediumint,int (11).

Composite types we generally use tinyint, faster time to save space and easier to expand

For mobile phone number, it is recommended to use char (one), char (11) to be more efficient on the query, because the phone number is an active field there is a lot of logic involved.

Some examples of characters commonly used segments
Name: char (20)
Price: DECIMAL (7, 3)
Product serial Number: SMALLINT (5) unsigned
Article content: TEXT
Md5:char (32)
Ip:char (15)
Time:int (10)
Email char (+) Reasonable choice of data type

Select the smallest within a reasonable range
We should choose the smallest data range, because this can greatly reduce disk space and disk i/0 read and write overhead, reduce memory consumption, reduce CPU utilization.

Choose a relatively simple data type
Numeric types are much simpler than string types, especially when comparing operations, so we should choose the simplest data types, such as when saving time, because PHP can handle Linux timestamps well so we can save the date as int (10) to be convenient, suitable and fast.

However, as the project becomes more and more difficult to handle in the work, I find that the time type is better than the field type of the time type itself, because MySQL has a rich time function for me to use, so that I can complete a lot of time-related logic, such as monthly leaderboard, weekly leaderboard, hot day, Birthday How many days wait logic

Do not use NULL
Why do you say that, because MySQL does not optimize the null field index, adding more computational difficulty, while saving and handling the Null class shape, also do more work, so in terms of efficiency, it is not recommended to use too much null. There are some values that he may indeed have no value, what to do. The workaround is to use the numeric value to get the integer 0, and the string to define the default values with NULL.

Use of String types
A string data type is a universal data type that can store values, strings, dates, and so on.
Saving a numeric type is best not to use the string data type, so that the storage space is obviously larger, and the string in the sort of 9 is greater than 22, in fact, if the operation of MySQL will convert the string to a numeric type, greatly reducing the effect, and this conversion will not go to the original index.
If the explicit data is in a complete set, such as a male, female, then you can use the set or enum data type, which operates numerically in operations and storage, so the efficiency is better than the string and occupies less space.

varchar and Char
VARCHAR is a variable-length string type, so that if the length is variable, it will use 1, 2 bytes to hold the length of the character, if the length of 255 uses 1 bytes to save the character length, otherwise use 2 characters to save the length. Because varchar saves data based on stored values, disk space can be greatly reduced.
If the data is frequently updated, because varchar is stored based on content, MySQL will do more work to complete the update operation, and if the new data length is greater than the old data length some storage engines will be split. At the same time varchar will completely retain all the internal data, the most typical description is the trailing space.
Char is a fixed-length string save type, and char removes trailing spaces. It is appropriate to use the char type when the data length is similar, such as the password user name MD5 encrypted.
If the data is frequently updated and modified, then char is better because char is fixed in length and fast in performance.

Selection of numeric types
Numeric data types are faster than string execution, data types with smaller intervals take up less space, processing is faster, such as tinyint can be much faster than bigint

When choosing a data type, consider the length of the content, such as whether to save millimeters or meters and choose different numeric types

Integer

Many types of integers, such as tinyint, int, smallint, bigint, and so on, we need to determine the type of data we want to store, and tinyint (10) and tinyint (100) have no difference in storage and computation, The difference is only the display level, but we also have to choose the appropriate data type length. You can view the differences in display by specifying the Zerofill property.

Floating-point numbers and precision values

Floating point float in storage and operating efficiency is better than the precision numeric type decimal, but float and double there will be rounding errors and decimal can provide more accurate scale of the precise operation will not have errors to produce more accurate calculation, suitable for financial type data storage. from:https://www.kancloud.cn/thinkphp/mysql-design-optimalize/39325