MySQL Select field data type

MySQL supports a number of data types, and choosing the right data type is critical to getting high performance. There is a simple principle in choosing to help make better choices .

The simple principle:

A, usually the smallest is the best

This can significantly reduce the IO overhead by using less disk, content, and CPU caches.

B, Simple is good

Simple data type operations typically require less CPU cycles. For example, an integer is less expensive than a word Fu Cao, because character sets and collation rules (collations) make it more complex than integral types. For example, you should use the built-in MySQL type instead of using the character type to store the date and time.

C, try to avoid using null

Null is the default property of the column, which is usually specified as NOT NULL. A null column value makes indexing, index statistics, and value comparisons more complex. A nullable column uses more storage space, which is also required for special handling in MySQL, and when a nullable column is indexed, an extra byte is required for each index, and in MyISAM it is more likely to cause a fixed-size index to become a variable-size index, using a separate bit in InnoDB ( BIT) stores null values.

1, Integer type

Several integer types: TINYINT (8-bit), SMALLINT (16-bit), Mediumint (24-bit), INT (32-bit), BIGINT (64-bit), and their range is-2 (n-1) to 2 (n-1) The second party-1. If unsigned is selected for non-negative, it can increase the integer maximum by one times, with the same performance as signed and unsigned using the same storage space.

Specifying a width for an integral type, such as int (one), is the same for storage, int (1) and int (20) , which does not limit the legal range of values, but specifies how many bits the MySQL interaction with the client should display, such as when you insert a 123456 value into int (1) , the database has actually been deposited in 123456, just for the client to find out is 1.

2. Real type

A number is a fraction of a number, but it is not just for storing fractional parts, but also for using decimal to store integers larger than bigint, and the float and double types support rounding using standard floating-point operations. Decimal is used to store exact decimals. Float occupies 4 bytes, Dobule occupies 8 bytes, it occupies less space than the decimal, the computation floats more quickly, so in the non-storage of financial data, you can consider other types, such as magnified N times after the Save as bigint.

3. String type

MySQL supports a variety of string types, each of which can define its own character set and collation, or proofing rules, that affect performance to a large extent.

A, varchar, and char types

The varchar type is used to store the variable-length string, which removes all whitespace at the end, which is more space-saving than a fixed-length string because it uses only the necessary space (the shorter the string takes up), and varchar records the length of the string with 1 or 2 extra bytes, VarChar saves space and is therefore useful for performance, but because the rows are longer, the string length is recalculated each time it is update, and the extra work is done more than the fixed length. Therefore, it is not said that varchar will be able to improve performance, but also to use the scene, if the maximum length of the string is much larger than the average length, and the column update is very small, it is appropriate to use varchar. Note that InnoDB will store too long varchar as blobs, usually people like to save the IP address as a varchar, in fact, the IP address is a 32-bit binary representation, you can be converted to decimal notation, and therefore not a string, Using decimals is to divide it into four paragraphs for easy reading.

Char is fixed-length, each column regardless of the length of the string will be allocated a constant storage space, char suitable for storing characters is very short, or all values close to a length, such as storage MD5 code, SESSION_ID, etc., for frequently changed data char is better than varchar, Another is that for very short columns, char is more space-saving than varchar, such as using char (1) and varchar (1) to store Y or N, Char requires one character, and varchar requires two, because there is also a length byte

B, blob, and text type

They are all designed to store big data, and blobs are binary/text with character storage. MySQL cannot index both the BLOB and the text column (only the previous length of the index, so you cannot query SQL with a before-like fuzzy match, you cannot go to the index), and you cannot use these indexes to sort.

Tip: Using enumerations instead of string types, enumerations can store duplicate strings as a predefined collection, and MySQL is very compact when storing enumerations, and MySQL holds the values in the column as an integer in the enumeration. The worst of the enumeration is that the string is fixed, and adding or removing must use alter TABLE. So for strings that will change in the future, using enumerations is not a good idea, unless you can accept adding elements at the end of the enumeration, because enumerations have a mapping transformation process, so enumerations can reduce storage space, but they also add additional overhead.

4. Time and Period type

DATETIME: This type can be saved for 1001-9,999 years with a precision of seconds, independent of the time zone, 8 bytes of storage, and the storage format is encapsulated as an integer of YYYYMMDDHHMMSS, so it is a sort of sortable type, Displays the date and time representation of the method as defined by the ANSI standard.

TIMESTAMP: It holds the number of seconds since midnight January 1, 1970, which is known as the timestamp, which is stored using 4 bytes and depends on the time zone . In addition to special circumstances, usually we should try to use timestamp, because it is more space than datetime, some people in reality like to use int to store the timestamp (including me when I came out of work), which does not bring any benefits, it is inconvenient to save the timestamp with an integer, It is not recommended to do this because the program needs to be converted after it is removed.

5. Bit data type

MySQL has a few storage types that use compact bits to store data. Regardless of how the underlying storage format is handled, it is technically a character type.

the Bit:bit (1) represents a length that defines a single bit, bit (2) represents 2 bits, and so on, the bit column has a maximum length of 64 bits, and MySQL handles the bit as a string instead of a number, when retrieving the value of bit (1). The result is a string containing binary 0 or 1, not ASCII 0 or 1, such as 00111001, whose binary equals 57, and retrieving it with a character code of 57, which is the ASCII code 57 corresponding to the character 9. It is generally best to avoid using bit in the application, preferably for most applications.

MySQL Select field data type