Whether it is in a small poor free database space or a large-scale e-commerce website, a reasonable design table structure, it is necessary to make full use of space. This requires us to have a good understanding of the common data types of the database system. Below I will write my little experience to share with you. I. Digital type. The digital type is divided into three categories: integer classes, decigrants, and digital classes. I am called "digital classes", which means DECIMAL and NUMERIC, which are the same type. It is strictly not a digital type because they actually save the numbers in string; each of his values (including decimal points) accounts for a byte store, so this type is time-consuming space comparison Big. But a prominent advantage is that the decimal number of digits is fixed, so it is not "distortion" in the calculation, so it is more suitable for "price", "amount", which is very high, but accuracy is very high, but accuracy is very high. The decimal category, that is, the floating point type, depending on the accuracy, there are two kinds of float and double. Their advantage is accuracy, Float can indicate that the absolute value is very small, small to about 1.17E-38 (0.000 ... 0117, 37 zero behind the decimal point), while Double can represent the absolute value of less than about 2.22E-308 (0.000 ... 0222, 307 zero) of the decimal point. Float types and Double types The stored space is 4 bytes and 8 bytes, respectively. If you need to use a field of decimal, accurate requirements are not high, of course, Float! But the sentence is real, we "civil" data, which requires as high as high accuracy? I have not used these two types - I haven't encountered examples suitable for use. The most used, the most worthy calculation, is an integer type. From the Tinyint that only one byte storage space to 8 bytes of Bigint, pick a "sufficient" and occupy the type of storage space to be considered when designing the database. Tinyint, Smallint, Mediumint, int and Bigint occupied storage spaces 1 bytes, 2 bytes, 3 bytes, 4 bytes, respectively, and the maximum integer of these types can be represented by no symbolic integers. Troufter 255,65535,1677,7215,4294,967,295 and 1844,674,4295 and 184,467, 07 If you are used to save the user's age (for example, the age is not desirable), with Tinyint is enough; in the "vertical and horizontal" of the nine cities, the skill values are also enough to use Smallint; if you want to use As an authorified Identify field that is definitely not more than 1600,000 rows, of course, using Mediumint without int, trim, each row saves a byte, 16000000 rows can save more than 10 trillors! Second, the date time type. The date and time type is relatively simple, nothing more than a few types such as Date, Time, DateTime, TimeStamp, and Year. Only sensitive to the date, and use DATE without having to say it; use time to use time; but up to date; but use DateTime. There is no article on the date of time, and it will not be detailed here. Third, the character (string) type.
Don't think 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 is char (10), whether or not your stored data reaches 10 bytes, it accounts for 10 bytes; while Varvhar It is variable length. If a field may be not fixed, we only know that it is not possible to more than 10 characters, defined it as varchar (10) is the most cost-effective, the actual length of the varchar type is it The value of (actual length 1). Why " 1"? This one is used to save how much length actually used! It should also be seen from this " 1". If a field, it may be the longest 10 characters, and in most cases, it is not worth it when used 10 characters. Because of the majority In the case, the actual occupied space is 11 bytes, which is more than one byte than using char (10)! For example, it is a table that stores stock names and code. Most of the stock name is four words, namely 8 bytes; stock code, Shanghai is six digits, Shenzhen is four digits. These are fixed lengths, and the stock name is of course necessary to use char (8); although the stock code is not fixed length, if using Varvhar (6), a Shenzhen stock code actual occupancy is 5 bytes, and a Shanghai The stock code should take up 7 bytes! Considering that the number of stocks in Shanghai is more than Shenzhen, it is better to use VARCHAR (6). Although a char or VARVHAR can get to 255, I think the char than 20 char is almost not available - very much larger than 20 bytes of fixed-length stuff? Not using VARCHAR! VARCHAR greater than 100 is also almost not available - better than this with Text. Tinytext, the maximum length is 255, the space is also (actual length 1); Text, maximum length 65535, occupied space is (actual length 2); MediumText, maximum length 16777215, occupying space is (actual length 3); longtext , Maximum length 4294967295, the occupied space is (actual length 4). Why " 1"? " 2"? " 3"? " 4"? If you still don't know, you should play PP. These can be used in the forum, news, what to save the text of the article. Depending on the actual situation, select different types of small to large. Fourth, enumeration and collection type. Enumeration (ENUM) type, up to 65535 different strings are selected from the selection, only one of them, the occupied storage space is one or two bytes, by the number of enumerated values; (SET) type, up to 64 members, you can choose from zero to unlimited multiple, occupying the storage space is one to eight bytes, determined by the number of possible members.
