1. Number type. Number types are divided into three categories according to my classification method: integer class, decimal class and number class.
What I call "number class" means DECIMAL and NUMERIC, which are the same type. It is strictly not a numeric type because they actually store the number as a string; each bit of its value (including the decimal point) takes up one byte of storage space, so this type consumes a lot of space. However, one of its outstanding advantages is that the number of decimal places is fixed and will not be "distorted" in the operation, so it is more suitable for fields such as "price" and "amount" that require low accuracy but very high accuracy requirements.
The decimal class, that is, floating point number type, has two types according to the accuracy, FLOAT (single precision) and DOUBLE (double precision). Their advantage is accuracy. FLOAT can represent decimals with an absolute value very small, as small as about 1.17E-38 (0.000...0117, 37 zeros after the decimal point), while DOUBLE can represent decimals with an absolute value as small as about 2.22E-308 (0.000...0222, 307 zeros after the decimal point). The FLOAT type and the DOUBLE type occupy 4 bytes and 8 bytes respectively. If you need to use decimal fields and the accuracy requirements are not high, of course you will use FLOAT! But to be honest, how can we use "civil" data with such high accuracy? I haven't used these two types so far-I haven't encountered any examples that are suitable for using them.
The most used and most cost-effective one is the integer type. From TINYINT that only occupies one byte of storage space to BIGINT that occupies 8 bytes, choosing a type that is "enough" and takes up the smallest storage space is something you should consider when designing a database. TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT occupy 1 byte, 2 byte, 3 byte, 4 byte and 8 bytes respectively. As for unsigned integers, the maximum integers that these types can represent are 255, 65535, 16777215, 4294967295 and 18446744073709551615, respectively. If you use it to save the age of the user (for example, it is not advisable to save age in the database), use TINYINT; in Jiucheng's "Zongheng", SMALLINT is enough; if you want to use it as the IDENTIFY field of AUTO_INCREMENT of a table with certainly no more than 16000000 rows, of course, use MEDIUMINT without INT. Just imagine, saving one byte per row, 16000000 rows can save more than 10 megabytes!
2. Date and time type.
The date and time types are relatively simple, and they are nothing more than DATE, TIME, DATETIME, TIMESTAMP and YEAR. It goes without saying that fields that are only sensitive to dates and do not require time are DATE instead of DATETIME; it is often the case when using time alone - TIME is used; but the most common ones are used are DATETIME. There is no article on date and time types, so I won't go into details here.
3. 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 as CHAR(10), it will take up 10 bytes regardless of whether the data you store reaches 10 bytes; while VARVHAR is variable-length. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters. It is most cost-effective to define it as VARCHAR(10). The actual length of the VARCHAR type is its value (actual length + 1). Why "+1"? How long is this byte used to save the actual length? From this "+1", you should also see that if a field has a possible value of up to 10 characters, and in most cases, it is not cost-effective to use VARCHAR: because in most cases, the actual space occupies 11 bytes, which occupies one byte more than using CHAR(10)!
For example, it is a table that stores stock names and codes. Most of the stock names are four characters, that is, 8 bytes; the stock code is six digits in Shanghai and four digits in Shenzhen. These are all fixed lengths, and of course, CHAR(8) should be used in stock names; although the stock code is not fixed length, if VARVHAR(6) is used, the actual space occupied by a Shenzhen stock code is 5 bytes, while the Shanghai stock code takes up 7 bytes! Considering that Shanghai has more stocks than Shenzhen, it is not as cost-effective to use VARCHAR(6).
Although a CHAR or VARVHAR can have a maximum length of 255, I think CHARs larger than 20 are almost useless - there are very few things with fixed lengths greater than 20 bytes, right? If you are not a fixed length, use VARCHAR! VARCHAR greater than 100 is almost impossible to use - it would be better to use TEXT if it is bigger than this. TINYTEXT, the maximum length is 255, and the space occupied is also (actual length +1); TEXT, the maximum length is 65535, and the space occupied is (actual length +2); MEDIUMTEXT, the maximum length is 16777215, and the space occupied is (actual length +3); LONGTEXT, the maximum length is 4294967295, and the space occupied is (actual length +4). Why "+1"? "+2"? "+3"? "+4"? If you don't know, you should play PP. These can be used in forums, news, etc., to save the main text of an article. Depending on the actual situation, choose different types from small to large.
4. Enumeration and collection types.
The enumeration (ENUM) type can define up to 65,535 different strings to choose from. Only one of them can be selected. The storage space is one or two bytes, determined by the number of enum values; the set (SET) type can have up to 64 members, zero to unlimited multiple, and the storage space is one to eight bytes, determined by the number of possible members of the set.
For example, in SQLServer, you can save on using a Bit type to represent gender (male/female), but MySQL does not have Bit, use TINTINT? No, you can use ENUM ('Handsome','Beauty')! There are only two options, so you only need one byte - as big as TINYINT, but you can directly use the strings 'Handsome' and 'Beauties' to access. It's so convenient!
Okay, MySQL's data types are introduced similarly, and my library building strategy will also introduce some of them to you as the data types introduced. But this is only part of it, and the space is limited and cannot be explained in detail; the rest depends on everyone's understanding of data types, and practice and discuss more.