MySQL data types and library building policies

  • 2020-05-07 20:32:09
  • OfStack

1. 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 bit 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, with 37 zeros behind the decimal point), while DOUBLE can indicate the absolute value as small as 2.22 E-308 (0.000... 0222, which 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 with 1 byte of storage space to BIGINT with 8 bytes of storage space, picking a "sufficient" type with minimal storage space is something you should consider when designing a database. TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT occupy 1 byte, 2 bytes, 3 bytes, 4 bytes, and 8 bytes, respectively. In terms of unsigned integers, the largest integers that these types can represent are 255, 65535, 16777215, 4294967295, and 18446744073709551615. If it is used to save the user's age (for example, it is not advisable to keep the age in the database), TINYINT is sufficient. In the aspect of 9 cities, SMALLINT is enough for all skill values; If you want to use AUTO_INCREMENT as the IDENTIFY field of AUTO_INCREMENT for a table that will definitely not exceed 16 million rows, MEDIUMINT is not INT. Imagine saving 1 byte per row, which saves more than 10 megabytes for 16 million rows.
2. Date time type.

Date and time types are simple, with DATE, TIME, DATETIME, TIMESTAMP, YEAR, and so on. For fields that are only date sensitive and not time sensitive, DATE instead of DATETIME goes without saying. 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.

3. 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 1 field is of variable length, we only know that it cannot exceed 10 characters. It is best to define it as VARCHAR(10), and the actual length of VARCHAR type is its value (actual length +1). Why +1? How much of this 1 byte is actually used to store it! You should also see from this "+1" that the maximum possible value for a field is 10 characters, and most of the time when 10 characters are used, VARCHAR doesn't work: in most cases, it takes 11 bytes, one byte more than CHAR(10)!

For example, it is a table to store the stock name and code, the stock name is mostly four words, that is, eight bytes; Stock symbol, Shanghai is six digits, shenzhen is four digits. 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 1 shenzhen stock symbol is 5 bytes, while 1 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 greater than 100 is also almost useless -- anything larger than that would be fine with TEXT. 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.

4. Enumeration and collection types.

Enumeration (ENUM) type, you can define up to 65535 different strings to choose from, only and must choose one of them, the storage space is occupied by 1 or 2 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 storage space of 1 to 8 bytes, depending on the number of possible members of the collection.

For example, in SQLServer, you can save up to 1 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 1 byte -- about the size of TINYINT1 -- but you can access it directly using strings like hotboys and girls. How convenient!

Related articles: