Mysql data type instructions _ MySQL

FLOAT and DOUBLE types support the use of standard floating point operations for approximate calculation. The DECIMAL type is used to store precise decimals. Because the cpu does not support direct DECIMAL computing, in MySQL and later versions, the MYSQL server implements high-precision FLOAT and DOUBLE computation of DECIMAL type. standard floating point computation is supported for approximate computation.

The DECIMAL type is used to store precise decimals.

Because the cpu does not support direct DECIMAL computation, in MYSQL and later versions, the MYSQL server itself implements high-precision DECIMAL computation. Relatively speaking, the cpu directly supports native floating point computing, so the floating point operation meditation is faster.

Precision can be specified for both the floating point and DECIMAL types. For a DECIMAL column, you can specify the maximum number of digits before and after the DECIMAL point. This affects column space consumption. Mysql5.0 and later pack the numbers and save them to a binary string (9 numbers are stored every 4 bytes ). For example, DECIMAL () stores 9 numbers on both sides of the DECIMAL point and uses 9 bytes in total: the number of the DECIMAL point is 4 bytes, and the number after the DECIMAL point is 4 bytes, the decimal point occupies one byte.

The decimal type in MYSQL 5.0 and later versions allows a maximum of 65 numbers. In earlier versions of Mysql, this limit is 254 numbers and is saved as uncompressed strings (one byte for each number ). However, these (earlier) versions do not actually use such a large number in computing, because DECIMAL is only a storage format, and DECIMAL will be converted to the DOUBLE type in computing.

When the floating point type stores values of the same range, it usually uses less space than DECIMAL. FLOAT is stored in 4 bytes. DOUBLE occupies 8 bytes, which has a higher precision and a greater range than FLOAT. Like the certificate type, only the storage type can be selected. Mysql uses DOUBLE as the internal floating point computing type.

Because extra space and computing overhead are required, we recommend that you use DECIMAL only when accurately calculating DECIMAL places-for example, to store financial data. However, when the data volume is large, you can consider using BIGINT to take the iron DECIMAL, and multiply the unit of currency to be stored by the number of digits of the Uncle point by the corresponding multiples. If you want to store financial data accurate to one thousandth of a minute, you can multiply all amounts by 100, and then store the results in BIGINT, this prevents the inaccuracy of floating point storage and the high cost of precise DECIMAL computing.

String type

VARCHAR and CHAR types

VARCHAR and CHAR are two main string types. Unfortunately, it is hard to explain exactly how these values are stored in disk and memory because they are related to the specific implementation of the storage engine.

VARCHAR

The VARCHAR type is used to store variable-length strings. it is the most common string data type. It saves more space than the fixed-length type because it only uses the necessary space. One exception is that if a MYSQL table is created using ROW_FORMAT = FIXED, each row uses a FIXED-length storage, which is a waste of space.

VARCHAR requires 1-2 additional bytes to record the length of the string: if the maximum length of the record is less than or equal to 255, only one byte is required; otherwise, two bytes are used. Assuming that the latin1 character set is used, a varchar (10) column requires 11 bytes of storage space. VARCHAR (1000) requires 1002 bytes, because two bytes are required to store length information.

VARCHAR saves storage space, so it also helps with performance. However, because the row length is longer, the row may be longer during UPDATE, which leads to additional work. If the space occupied by a row increases and no more storage space on the page can be stored, different storage engines use different methods. For example, MYISAM splits rows into different fragments for storage, while INNODB splits the pages so that the rows can be put into the pages.

In the following cases, it is appropriate to use VARCHAR: the maximum length of a string column is much larger than the average length; the column is updated rarely, so fragmentation is not a problem; a complex character set like UTF-8 is used, each character is stored in different bytes.

In Mysql 5.0 or later versions, the end space is reserved during storage and retrieval. However, in mysql 4.1 or earlier versions, a space is raised at the end.

INNODB is more flexible. it can store long varchar as blob. we will discuss this issue later.

CHAR

The CHAR type is fixed: MYSQL always allocates enough space according to the defined string length. When CHAR is stored, MYSQL deletes all trailing spaces. CHAR is filled with spaces as needed to facilitate comparison.

Char is suitable for storing short strings, or all values have an approximate length. For example, char is very suitable for storing the MD5 value of the password. For frequently changed data, CHAR is also better than VARCHAR, because the char type of the field is not prone to fragmentation. For very short columns, char is more efficient than varchar in storage. For example, char (1) is used to store values of only Y and N. If the single-byte character set requires only one byte, but varchar (1) requires two bytes, because there is an additional byte of record length.

Padding and block spaces are the same in different storage engines, because they are processed at the mysql server layer.

Similar to char and varchar, there are binary and varbinary types that store binary strings. A binary string is very similar to a regular string, but a binary string stores bytecode rather than characters. Fill is different: MYSQL fills binary with \ 0 (zero byte) instead of space, and the fill value will not be removed during retrieval.

These types are useful when you need to store binary data and want mysql to use bytecode instead of bytecode for comparison. The advantages of exquisite comparison are not only in case sensitivity. When MYSQL compares a binary string, each time it is compared by a byte, it is compared based on the value of this byte. Therefore, binary is much simpler than character, so it is faster.

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------

Tips: the overhead for storing 'hello' using varchar (5) and varchar (200) is the same. What are the advantages of using shorter columns for name?

Facts have proved to have great advantages. Longer columns consume more memory, because mysql usually allocates a fixed size of memory to keep the internal value. It is especially bad to sort or operate tables in the memory. The sorting is also bad when the disk is used for temporary tables.

Therefore, the best strategy is to allocate only the space actually needed.

BLOB and TEXT

BLOB and TEXT are both string data types designed to store large amounts of data. they are stored in binary and string modes, respectively.

In fact, they belong to two different data types: TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, and LONGTEXT; the binary types are TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. BLOB is a synonym for SMALLBLOB and TEXT is a synonym for SMALLTEXT.

Different from other types, MYSQL treats each BLOB and TEXT value as an independent object. The storage engine usually performs special processing during storage. When blob and text values are too large, INNODB uses a dedicated "external" storage area for storage. Therefore, each value needs to store a pointer in 1-4 bytes within the row, then the century value is stored in the external storage area.

The differences between the BLOB and TEXT families are that the BLOB type stores binary data without sorting rules or character sets, while the text type has character sets and sorting rules.

MYSQL sorts BLOB and TEXT columns differently from other types: it only sorts the max_sort_length byte on the top of each column rather than the entire string. If you only need to rank a small part of the character set, you can reduce the configuration of max_sort_length, or use order by sustring (column, length ).

Mysql cannot index all strings of the blob and text columns or use these indexes to eliminate sorting.

Use enumeration (ENUM) instead of string type

Sometimes you can use enumerative columns to replace common string types. The enumerated column stores non-repeated strings into a predetermined set. Mysql is very compact in storage enumeration and will be compressed to one or two bytes based on the number of values in the list. Mysql internally saves each value in the list as an integer, and saves the 'number-string' ing lookup table in the. frm file of the table.

Another surprising thing is that enumeration fields are sorted by internal integers rather than defined strings.

The worst part of enumeration is that the string list is fixed, and the alter table must be used to add or delete strings. Therefore, it is not a good idea to use enumeration for a series of strings that may be changed in the future. we can accept adding elements only at the end of the list.

Myslq saves each enumerated value as an integer and must be searched to convert it to a string. Therefore, enumeration columns have some overhead. Generally, the enumerated list is relatively small, so the overhead can be controlled, and the egg cannot always be so. Under specific circumstances, associating the char/varchar column with the enumeration column may be slower than directly associating the char/varchar column.

Date and time type

DATETIME

This type can save a large range of values, from 1001 to 9999, with a precision of seconds. It encapsulates the date and time into various integers that are YYYYMMDDHHMMSS, and uses 8 bytes of storage space irrelevant to the time zone.

By default, mysql displays datetime values in a sorted and unambiguous manner, for example, '2017-11-17 23:20:00 '. This is the representation of dates and times defined by ANSI.

TIMESTAMP

Like its name, the TIMESTAMP type stores the number of seconds since midnight, January 1, January 1, 1970 (Greenwich Mean Time), which is the same as the Unix TIMESTAMP. TIMESTAMP only uses 4 bytes of storage space. Therefore, it ranges from January 1, 1970 to January 1, 2038, which is much smaller than datetime. Mysql provides the FROM_UNIXTIME () function to convert a Unix timestamp to a date, and provides the UNIX_TIMESTAMP () function to convert a date to a UNIX timestamp.

The value displayed by timestamp also depends on the time zone. Mysql servers, operating systems, and client connections all have time zone settings.

Therefore, the timestamp with a storage value of 0 is displayed in the US east time zone as a time difference of 5 hours between 19:00:00 and Greenwich mean. It is necessary to emphasize the difference: if data is stored or accessed in multiple time zones, the behavior of timestamp and datetime will be very different. The value provided by the former has a relationship with the time zone, and the latter retains the date and time expressed in text.

Timestamp also has special attributes not available for datetime. By default, if the value of the first timestamp column is not specified during insertion, mysql sets the value of this column to the current time. when inserting a row of records, mysql will also refine the value of the first timestamp by default (unless the value is explicitly specified in the update statement ). You can configure any timestamp column insertion and update behavior. The default value of the timestamp column is not null, which is different from other data types.

In addition to special behaviors, we should also try to use timestamp as much as possible, because it is more efficient than datetime space. Sometimes, people store Unix timestamps as integers, but this does not bring any benefit. It is usually not convenient to use the integer to save the timestamp format, so we do not recommend this.

What should I do if I need to store date and time values smaller than the second? Myslq currently does not provide a suitable data type, but you can use your own storage format: you can use the bigint type to store the time stamp of a delicate level or use double to store the fractional part after seconds. Alternatively, you can use MariaDB instead of mysql.

BIT data type (BIT)

The maximum length of a BIT column is 64 characters.

MYSQL treats bit as a string rather than a number. When retrieving the value of bit (1), the result is a string containing binary 0 or 1, rather than '0' or '1' of ASCII code '. However, in the case of digital context retrieval, it is recorded that the bitstring is converted into a number. If you need to compare the result with another value, remember this. For example, if you store a binary value of B '000000' of 57, go to the bit (8) column, and retrieve it, the content is a string with a string code of 57. That is to say, the ascii code is 57 characters "9 ". However, in the digital context scenario, the number is 57.

This is rather confusing, so we should be careful when using the bit type. It is best to avoid this type for most applications.

If you want to rough a true or false value in a bit bucket, another method is to create a char (0) column that can be empty. Columns can be changed to NULL or a string with a length of 0 (empty string ).