Detailed analysis of MySQL Data Types and database creation policies

1. digit type. The number types are classified into three types by my classification: integer, decimal, and number.

My so-called "number classes" refer to DECIMAL and NUMERIC. They are of the same type. Strictly speaking, it is not a numeric type, because they actually save numbers as strings; Each digit of his value (including the decimal point) occupies a byte storage space, therefore, this type consumes a lot of space. However, one of its outstanding advantages is that the number of digits in decimal places is fixed and will not be "distorted" in the computation ", therefore, it is suitable for "price" and "amount" fields with low precision requirements but high accuracy requirements.

Decimals, that is, floating point numbers. FLOAT (single precision) and DOUBLE (DOUBLE Precision) are supported based on the precision. Their advantage is accuracy. FLOAT can indicate that the absolute value is very small, as small as about 1.17E-38 (0. 000... 0117, 37 digits after the decimal point), and DOUBLE can indicate that the absolute value is smaller than about 2.22E-308 (0. 000... 0222, with a decimal point followed by 307 zeros. The storage space occupied by FLOAT and DOUBLE types is 4 bytes and 8 bytes respectively. If you need to use decimal fields, the precision is not high, of course, FLOAT is used! But to be honest, how does our "Civil" data require high accuracy? I have never used these two types so far-I have not encountered any examples that are suitable for using them.

The most commonly used, most cost-effective, is the integer type. From TINYINT, which occupies only one byte of storage space, to BIGINT, which occupies eight bytes, selecting a type that is "adequate" and occupies the smallest storage space should be considered during database design. The storage space occupied by TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT is 1 byte, 2 byte, 3 byte, 4 byte, and 8 byte, respectively. For unsigned integers, the maximum Integers of these types are 255, 65535, 16777215, 4294967295, and 18446744073709551615. If it is used to save the user's age (for example, it is not advisable to store the age in the database), TINYINT is enough. In 9-city's vertical bar, skill values, SMALLINT is enough. If you want to use it as the IDENTIFY field of AUTO_INCREMENT for a table with no more than 16000000 rows, of course, MEDIUMINT is not used. Imagine that each row saves one byte, 16000000 rows can save more than 10 MB!

Ii. Date and Time type.

The DATE and TIME types are simple. They are only DATE, TIME, DATETIME, TIMESTAMP, and YEAR types. It is unnecessary to use DATE instead of DATETIME for fields that are only sensitive to DATE and have no requirements for TIME. TIME is also used occasionally; however, DATETIME is used most. There is no article on the date and time type, so we will not detail it here.

3. character (string) type.

Do not assume that the character type is CHAR! The difference between CHAR and VARCHAR is that CHAR is a fixed length. As long as you define a field as CHAR (10), no matter whether the data you store reaches 10 bytes, it occupies 10 bytes of space, while VARVHAR is variable length. If a field may have an unfixed length, we only know that it cannot exceed 10 characters, defining it as VARCHAR (10) is the most cost-effective. The actual length of the VARCHAR type is its value (actual length + 1 ). Why "+ 1? How long is this Byte actually used for saving! From this "+ 1", we can also see that if a field has a maximum value of 10 characters, and in most cases, 10 characters are used, VARCHAR is not suitable: In most cases, the actual space occupied is 11 bytes, occupying one more byte than CHAR (10!

For example, a table stores the stock name and code. The stock name is mostly in four words, that is, 8 bytes. The stock code is a six-digit number in Shanghai, shenzhen has four digits. These are fixed length, and the stock name must use CHAR (8). Although the stock code is not fixed length, if VARVHAR (6) is used ), A stock code in Shenzhen actually occupies 5 bytes, while a stock code in Shanghai occupies 7 bytes! Considering that Shanghai has more shares than Shenzhen, VARCHAR (6) is not as good as CHAR (6.

Although the maximum length of a CHAR or VARVHAR can be up to 255, I think that a CHAR larger than 20 is almost useless-there are very few fixed lengths larger than 20 bytes, right? Use VARCHAR if it is not a fixed length! VARCHAR of more than 100 is almost useless-it is better to use TEXT that is larger than this. TINYTEXT, the maximum length is 255, the occupied space is also (actual length + 1); TEXT, the maximum length is 65535, the occupied space is (actual length + 2); MEDIUMTEXT, the maximum length is 16777215, the occupied space is (actual length + 3); LONGTEXT, maximum length of 4294967295, and occupied space is (actual length + 4 ). Why "+ 1 "? "+ 2 "? "+ 3 "? "+ 4 "? If you do not know, you should play PP. These can be used in forums, news, or something to save the text of an article. Select different types from small to large based on the actual situation.

Iv. Enumeration and set types.

Enumeration (ENUM) type. You can select a maximum of 65535 different strings. You can only select one of them and the storage space is one or two bytes, it is determined by the number of enumerated values. The SET type can have a maximum of 64 members. You can select zero to multiple unlimited members, the storage space is one to eight bytes, which is determined by the number of possible members of the set.

For example, in SQLServer, you can save a Bit type to indicate the gender (male/female), but MySQL does not have a Bit, using TINTINT? No, you can use ENUM ('handsome guy ', 'meimei ')! There are only two options, so only one byte is needed-it is as big as TINYINT, but it can be accessed directly using strings 'handsome guy 'and 'meimei. It's so convenient!

Well, the Data Type of MySQL is almost introduced. My database creation policy will also introduce the data type to you. However, this is only part of it. It is limited in length and cannot be further elaborated. Others rely on the understanding of data types, practices, and discussions.