MySQL data types and the strategy of database building

Whether in a small, free database space or a large e-commerce site, a reasonable design of the table structure, the full use of space is very necessary. This requires us to have a good understanding of the common data types of database systems. Below I will write a little of my experience to share with you.

One, the number type

The number types are grouped into three categories according to my classification: integer, Decimal, and numeric.

What I call "digital class" means DECIMAL and NUMERIC, which are the same type. It's strictly not a numeric type because they actually save the number as a string, and every bit of his value (including the decimal point) occupies a byte of storage space, so this type of space is much more expensive. But one of its prominent advantages is that the number of decimal digits fixed, in the operation will not "distorted", so the more suitable for the "price", "amount" for the accuracy of the request is not high accuracy requirements of a very high level of the field.

Decimal class, that is, floating point type, according to the accuracy of different, there are float and DOUBLE two species. Their advantage is precision, FLOAT can mean that the absolute value is very small, small to about 1.17E-38 (0.000 ...). 0117, a decimal number with 37 0 after the decimal point, and a DOUBLE can represent an absolute value smaller than about 2.22E-308 (0.000 ...). 0222, the decimal number of 307 0 after the decimal point. The FLOAT type and DOUBLE type occupy storage space of 4 bytes and 8 bytes respectively. If you need to use a decimal field, the precision is not high, of course, with FLOAT. But to say the truth, we "civil" data, which has the requirement of precision so high? These two types have not been used so far-I have not yet encountered cases that are appropriate for using them.

The most used, the most worthy of reckoning, is the integer type. From a TINYINT of only one byte of storage space to a 8-byte BIGINT, it should be considered when designing a database to pick a "sufficient" type that occupies the smallest amount of storage space. TINYINT, SMALLINT, Mediumint, INT, and BIGINT occupy storage space of 1 bytes, 2 bytes, 3 bytes, 4 bytes, and 8 bytes, and for unsigned integers, the largest integers that these types can represent are 255, 65535, 16777215, respectively. 4294967295 and 18446744073709551615. If you are saving the age of the user (for example, the age in the database is not advisable), with TINYINT is enough; in the "vertical and horizontal" of the nine city, the skill value, with SMALLINT also enough; If you want to use a table that is definitely not more than 16000000 rows, auto_ INCREMENT identify field, of course, with Mediumint not INT, imagine, each line to save a byte, 16000000 lines can save 10 trillion more.

Date and Time type

The date and time types are simpler, with a few types of dates, times, DATETIME, TIMESTAMP, and year. Only date sensitive, but not required for time fields, use DATE without a DATETIME is needless to say, the use of time alone is also the case-use times, but the most use or DateTime. There is nothing to do on the date-time type, and it is no longer detailed here.

Three, character (string) type

Don't assume that character types are Char,char and VARCHAR because char is a fixed length, so long as you define a field as char (10), it takes up 10 bytes of space regardless of whether the data you store reaches 10 bytes, and VARCHAR is variable length degree, if the possible value of a field is not fixed length, we only know that it can not exceed 10 characters, define it as VARCHAR (10) is the most cost-effective, VARCHAR type occupies the space is the actual length of its value +1. Why do you need +1? This byte is used to save how much length is actually used. It should also be seen from this +1, if a field, its maximum possible value is 10 characters, and most of the time it is used in the case of 10 characters, VARCHAR is not cost-effective: because in most cases, the actual footprint is 11 bytes, more than using CHAR (10) to occupy a byte.

For example, a table that stores the name and code of a stock, the stock name is mostly four words, 8 bytes; The stock code, Shanghai is six digits, Shenzhen is four digits. These are fixed-length, the stock name of course to use CHAR (8); The stock code is not fixed length, but if the use of VARCHAR (6), a Shenzhen stock code actually occupy space is 5 bytes, and a Shanghai stock code to occupy 7 bytes! Given that there are more shares in Shanghai than in Shenzhen, using VARCHAR (6) is not as good as CHAR (6).

Although the maximum length of a char or VARCHAR can be up to 255, I think char greater than 20 is almost impossible-there are few fixed-length things larger than 20 bytes long? It is not a fixed length to use VARCHAR. VARCHAR, more than 100, is also almost impossible to use-more TEXT than this is good. Tinytext, the maximum length is 255, occupies the space is also the actual length +1;text, the maximum length 65535, occupies the space is the actual length +2;mediumtext, the maximum length 16777215, occupies the space is the actual length +3;longtext, the maximum length 4294967295, occupies the space is the actual length +4. Why + 1, + 2, + 3, +4? If you don't know, you should hit PP. These can be used in forums, news, whatever, to keep the text of the article. Depending on the actual situation, choose from small to large different types.

Iv. enumeration and collection types

Enumeration (enum) type, you can define up to 65535 different strings to choose from, and you can only and must select one of them, which occupies a storage space of one or two bytes, determined by the number of enumerated values; the collection (set) type, which can have up to 64 members, You can select 0 of them to an unqualified number, and occupy a storage space of one to eight bytes, determined by the number of possible members of the collection.

For example, in SQL Server, you can save a bit type to represent gender (male/female), but MySQL has no BIT, with Tintint? No, you can use the ENUM (' Handsome boy ', ' Meimei '), only two choices, so just a byte-as big as TINYINT, but can be directly with the string ' handsome ' and ' Meimei ' to access. It's so convenient!

Well, MySQL data type introduction is similar, my library strategy with the introduction of data types introduced to you. But this is only part of the space which cannot be further discussed, and the rest is based on a lot of practice and discussion on the basis of the understanding of data types.

Original link: http://www.kubiji.cn/topic-id2558.html