Analysis of MySQL Data Types and related database creation policies

We all know that free database space or large e-commerce websites have a rational design table structure and make full use of the space. These require that we have a full copy of the commonly used database system http://database.51cto.com/art/200379/12568.htmdata. Next I will share some of my experiences with you.

I. 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.

Decimal type, that is, the floating point type. FLOAT and DOUBLE types 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 requirement is not high. Of course, FLOAT is used. But to be honest, how can we use the MySQL Data Type of "civil", which requires a high accuracy? I have never used these two types-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 types

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 from TIME to TIME; however, DATETIME is used most. There is no article on the date and time type, so we will not detail it here.

Iii. character (string) Type

Do not think that the character type is CHAR. The difference between CHAR and VARCHAR is that CHAR is of fixed length, as long as you define a field as CHAR (10 ), therefore, no matter whether the data you store reaches 10 bytes, it occupies 10 bytes of space, while VARCHAR is of a variable length, if the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10, the occupied space of the VARCHAR type is the actual length of its value plus 1.

Why + 1? This byte is used to save the actual length. From this + 1, we can also see that if a field has a maximum of 10 characters, and in most cases, it uses 10 characters, it is not feasible to use VARCHAR: In most cases, the actual occupied space 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 VARCHAR (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 a CHAR or VARCHAR can have a maximum length of 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 with a value greater than 100 is almost useless-it is better to use TEXT with a larger value.

TINYTEXT, the maximum length is 255, the occupied space is also the actual length + 1; TEXT, the maximum length of 65535, the occupied space is the actual length + 2; MEDIUMTEXT, the maximum length of 16777215, the occupied space is the actual length + 3; LONGTEXT, the maximum length is 4294967295, And the occupied space is the 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 SQL server (a powerful database platform on WINDOWS), you can save a BIT type to indicate gender (male/female ), but MySQL (the best combination with PHP) does not have BIT. Does it use TINTINT? No, you can use ENUM ('handsome guy ', 'meimei'). There are only two options, so you only need one byte-as big as TINYINT, however, the string 'handsome guy 'and 'meimei' can be directly used for access. It's so convenient!

Well, the MySQL Data Type of MySQL (the best combination with PHP) is similar, and my database creation policy will introduce the MySQL 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.