MySQL data types and database building strategy analysis

 

Number type. According to my classification method, number types are divided into three categories: integer, decimal and number.  

By "number class," I mean DECIMAL and NUMERIC, which are of the same type. It's not strictly a number type, because they actually keep Numbers as strings; Each digit of its value (including the decimal point) takes up one byte of storage space, so this type takes up a lot of space. However, one of its outstanding advantages is that the decimal number is fixed, in the operation will not "distortion", so it is more suitable for the "price", "amount" such as the accuracy requirements are not high but the accuracy requirements are very high field.  

The decimal class, or floating point number type, has FLOAT (single precision) and DOUBLE (double precision), depending on the precision. Their advantage is accuracy, FLOAT can mean the absolute value is very small, down to about   1.17 E-38   (0.000... 0117,   has 37 zeros behind the decimal point, while DOUBLE has the absolute value as small as about   2.22 E-308   (0.000... 0222,   has 307 zeros behind the decimal point. The FLOAT type and DOUBLE type take up 4 and 8 bytes of storage, respectively. If you need to use the decimal field, precision requirements are not high, of course, FLOAT! But say a sentence positive words, our "civil" data, which has the request accuracy so high? I haven't used either of these yet -- I haven't come across a case where they are suitable for use.  

The most used, and most worth calculating, is the integer type. From TINYINT, which takes only one byte of storage, to BIGINT, which takes eight bytes, picking a "sufficient" type with minimal storage footprint is something you should consider when designing a database. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT take up 1 byte, 2 bytes, 3 bytes, 4 bytes, and 8 bytes respectively. The largest integers that these types can represent in terms of unsigned integers are 255, 65535, 16777215, 4294967295, and 18446744073709551615. If you want to save the age of the user (for example, it is not advisable to save the age in the database), TINYINT is sufficient. In the "latitude" of the nine cities, the skill values, SMALLINT is enough; If you want to use AUTO_INCREMENT's IDENTIFY field as a table that will definitely not exceed 16 million rows,   MEDIUMINT   of course,   INT   is not   INT  .  

Ii. Date and time type.  

Date and time types are simple,   DATE, TIME, DATETIME, TIMESTAMP and YEAR. For fields that are only date sensitive and not time sensitive, DATE instead of DATETIME is a no-brainer. Time alone also happens -- TIME; But most of them are DATETIME. There is nothing to do with the date time type, so I won't go into details here.  

Character (string) type.  

Don't think 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), it takes up 10 bytes of space, whether or not you store 10 bytes of data. While VARVHAR is of variable length, if the possible value of a field is of variable length, we only know that it cannot exceed 10 characters. It is best to define it as   VARCHAR(10). The actual length of VARCHAR   type is its value (actual length +1). Why +1? How much of this byte is actually used to save it! You should also see from this "+1" that if a field has a maximum possible value of 10 characters, and most of the time that means 10 characters, VARCHAR is not good: because in most cases, the actual space is 11 bytes, which is one byte more than CHAR(10)!  

For example, it is a table to store the names and codes of stocks. Most stock names are four words, that is, eight bytes. The stock symbol is six digits in Shanghai and four digits in shenzhen. These are all fixed lengths, so the name of the stock, of course, is   CHAR(8)  ; Although the stock symbol is of variable length, if VARVHAR (6) is used, the actual space occupied by a shenzhen stock symbol is 5 bytes, while a Shanghai stock symbol takes up 7 bytes! Given that there are more shares in Shanghai than in shenzhen, VARCHAR (6) is not as cost-effective as CHAR (6).  

While the maximum length of an CHAR or VARVHAR can be up to 255, I think CHAR greater than 20 is rarely used - there are few fixed lengths greater than 20 bytes, right? VARCHAR instead of fixed length! VARCHAR larger than 100 is also almost unused-a larger TEXT would be fine. TINYTEXT, the maximum length is 255, and the occupied space is also (actual length +1). TEXT, maximum length 65535, occupied space is (actual length +2); MEDIUMTEXT, maximum length 16777215, occupied space is (actual length +3); LONGTEXT, maximum length 4294967295, occupied space is (actual length +4). Why +1? "+ 2"? "+ 3"? "+ 4"? If you don't know, you should call PP. These can be used in forums, news, etc., to save the body of the article. Depending on the actual situation, choose different types from small to large.

Enumerations and collection types.  

Enumeration (ENUM) type, can define up to 65535 different strings to choose from, only and must choose one of them, the storage space is one or two bytes, depending on the number of enumeration values; Collection (SET) type, which can have up to 64 members, of which you can choose from zero to more than unlimited, occupies one to eight bytes of storage, depending on the number of possible members of the collection.  

For example, in SQLServer, you can save to use an Bit type for gender (male/female), but MySQL doesn't have Bit, TINTINT? No, you can use ENUM! There are only two options, so it only takes one byte -- the same size as TINYINT -- but can be accessed directly from the strings' handsome 'and' pretty '. How convenient!  

Okay, so MySQL's data types are pretty much the same, and my library building strategy is a little bit more along with the data types. But this is only part of it, and space is not long enough to go into it; The rest, depending on the understanding of the data types on the basis of more practice, more discussion.