MySQL using notes (iv)

First, select the appropriate data type

1. CHAR vs Vchar
Char is a fixed-length character type, and varchar is a variable-length character type. CHAR (m) in the data column, each value occupies M bytes, if a length less than M,mysql will be on its right with a space character to top up, in the retrieval operation when the last non-whitespace character to the right of all the characters will be removed, so note that Removing the right-hand space may not only remove the top-up space, but it may also remove the blank space on the right side of the original string !
In the data column of varchar (M), each value occupies just enough bytes plus a byte to record its length (that is, the total length is l+1 bytes).
Because char fixed length, so processing speed is faster than Vchar, but occupy space is bigger than Vchar, need specific case to analyze when use, general principle is:
(1) For MyISAM tables, use char as much as possible, especially for MyISAM and ISAM data sheets that often require modification and are prone to fragmentation;
(2) For InnoDB table, use varchar as far as possible.

2. TEXT vs BLOB  
      Save a small number of strings using char or varchar, while saving large text, select text or BLOB. The difference is that BLOBs can also hold binary data, compared to slices, while text can only hold plain text data. Text and blobs are subdivided into text,mediumtext,longtext and blob,mediumblob,longblob three different length types respectively.  
    blob and text values can cause some performance problems, especially when a large number of deletions are performed.   the
delete operation leaves a large "void" in the data table, and subsequent entries into these "holes" will have an effect on the performance of the insert. To improve performance, it is recommended that you periodically defragment such tables using the Optimize table feature to avoid the "hole" that is causing performance problems.  
     You can use the index of a composition to improve query performance for large text fields (blob or text). A composite index is a hash value based on the contents of a large text field, stored in a separate data column, and then a data row can be found based on the retrieved hash value . However, this technique can only be used for exact matching queries (hash values for similar <, >=, and so on range search is not useful). &NBSP;
You can use MD5 (), SHA1 (), CRC32 (), and so on to generate hash values. such as

    CREATE TABLE T (ID varchar (+), context blob, hash_value varchar);    INSERT into t values (1, repeate (' Beijing ', 2), MD5 (context)),    (2, repeate (' Beijing ', $), MD5 (context)),    (100, Repeate (' HelloWorld '), MD5 (context));    SELECT * FROM t where HASH_VALUE=MD5 (repeat (' Beijing ', 2));

If you need to blur the Blob or text field, MySQL provides the prefix index, which is the prefix%, for example select * from t where context=‘hello%‘ .

3. Floating point vs. fixed number
(1) floating point number presence error, fixed-point numbers are stored with a string, high precision
(2) data that is sensitive to monetary accuracy should be stored using fixed-point numbers
(3) In the programming, if the use of floating point number, pay special attention to the error problem, and try to avoid doing floating-point comparison
(4) Pay attention to the handling of some special values in floating-point numbers

4. Date type selection
(1) Select the date type that meets the minimum storage for the application, depending on the actual need. For example, if you only need to record years, use 1 bytes to store the year without considering the 4-byte date
(2) If you want to record the date and time, and the year of record is long, it is best to use datetime instead of timestamp, because the range of datetime is wider than timestamp
(3) If the recorded date needs to be used by users in different time zones, it is best to use timestamp because timestamp matches the actual time zone

second, the character set in MySQL

Common Character Set comparisons:

Character Set	is the negation of the long	Encoding Method	Other Notes
Ascii	Is	Single-byte 7-bit encoding	The earliest groundbreaking character
Ios-8859-1/latin1	Is	Single-byte 8-bit encoding	Western European Character Set
GB2312	Is	Double-byte encoding	Early standard, not recommended
GBK	Is	Double-byte encoding	Many system support
GB18030	Whether	2-byte or 4-byte encoding	Start with some support, but the database is not common
UTF-32	Is	Four-byte encoding	UCS-4 raw code, now rarely used
UCS-2	Is	2-byte encoding
UTF-16	Whether	2-byte or 4-byte encoding	Internal use such as Java and Windows Xp/nt
UTF-8	Whether	1~4 byte encoding	Widely supported Unicode character sets for the internet and Unix/linux

Of these, gbk each character occupies 2 bytes, while UTF-8 each Chinese character occupies 3 bytes.

MySQL using notes (iv)