MySQL data type and construction library strategy
1.Number type.Number types are divided into three categories according to my classification method: integer type, decimal type and number type.
The so-called "number type" refers to DECIMAL and NUMERIC, they are the same type.Strictly speaking, it is not a type of numbers, because they actually store numbers in the form of strings; each digit of its value (including the decimal point) occupies one byte of storage space, so this type consumes more space big.But one of its outstanding advantages is that the number of decimal places is fixed, and it will not be "distorted" in calculations, so it is more suitable for fields such as "price" and "amount" that require low accuracy but very high accuracy requirements.
Decimal type, that is, floating-point number type, according to the different precision, there are FLOAT (single precision) and DOUBLE (double precision).Their advantage is accuracy.FLOAT can represent decimals with very small absolute values, as small as about 1.17E-38 (0.000...0117, 37 zeros after the decimal point), and DOUBLE can even represent absolute values as small as about 2.22E-308 (0.000...0222, 307 zeros after the decimal point) decimal.FLOAT type and DOUBLE type occupy 4 bytes and 8 bytes of storage space respectively.If you need to use the decimal field, the precision is not high, of course use FLOAT! But to be honest, how can our "civilian" data require such high precision? I haven't used these two types so far-I haven't encountered a case that is suitable for using them.
The most used, and the most worthy of careful calculation, is the integer type.From TINYINT, which only occupies one byte of storage space, to BIGINT, which occupies 8 bytes, choosing a type that is “enough” and occupies the smallest storage space should be considered when designing a database.The storage space occupied by TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT is 1 byte, 2 bytes, 3 bytes, 4 bytes and 8 bytes respectively.For unsigned integers, the largest integers that these types can represent are respectively These are 255, 65535, 16777215, 4294967295, and 18446744073709551615.If it is used to save the age of the user (for example, it is not advisable to save the age in the database), use TINYINT; in Ninetowns "Longitudinal", use SMALLINT for each skill value; if you want to use Make the IDENTIFY field of AUTO_INCREMENT of a table that will definitely not exceed 16 million rows.Of course, use MEDIUMINT instead of INT.Just imagine that each row saves one byte, 16 million rows can save more than 10 trillion!
Second, the date and time type.
The date and time types are relatively simple.They are nothing more than DATE, TIME, DATETIME, TIMESTAMP, and YEAR.For fields that are only sensitive to dates, and do not require time, use DATE instead of DATETIME.Needless to say; the use of time alone sometimes happens-use TIME; but the most commonly used is DATETIME.There is nothing to do about date and time types, so I won’t go into details here.
Three, 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), no matter whether the data you store reaches 10 bytes, it will take up 10 bytes of space; and VARVHAR It is variable length.If the possible value of a field is not a fixed length, we only know that it cannot exceed 10 characters.It is the most cost-effective to define it as VARCHAR(10).The actual length of the VARCHAR type is it The value of (actual length + 1).Why "+1"? This one byte is used to store the actual length used! It should also be seen from this "+1" that if a field, its possible value is up to 10 characters, and in most cases, when 10 characters are used, it is not economical to use VARCHAR: because in most cases In this case, the actual occupied space is 11 bytes, which is one more byte than CHAR(10)!
For example, it is a table that stores stock names and codes.Most stock names are four characters, that is, 8 bytes; for stock codes, Shanghai is a six-digit number, and Shenzhen is Four digits.These are all fixed lengths.Of course, the stock name must use CHAR(8); although the stock code is not of fixed length, if VARVHAR(6) is used, the actual space occupied by a Shenzhen stock code is 5 bytes, and a Shanghai stock code The stock code takes up 7 bytes! Considering that there are more stocks in Shanghai than in Shenzhen, VARCHAR(6) is not as cost-effective as CHAR(6).
Although the maximum length of a CHAR or VARVHAR can be up to 255, I think CHAR larger than 20 is almost useless-there are few fixed-length stuff larger than 20 bytes, right? Use VARCHAR if it is not a fixed length! VARCHAR larger than 100 is also almost unused-TEXT is better for larger ones.TINYTEXT, the maximum length is 255, and the occupied space is also (actual length + 1); TEXT, the maximum length is 65535, and the occupied space is (actual length + 2); MEDIUMTEXT, the maximum length is 16777215, and the occupied space is (actual length + 3); LONGTEXT , The maximum length is 4294967295, and the occupied space 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 body of the article.According to the actual situation, choose different types from small to large.
Four, enumeration and collection types.
Enumeration (ENUM) type, you can define up to 65535 different strings to choose from, you can only and must choose one of them, and the storage space is one or two bytes , Is determined by the number of enumerated values; set (SET) type, can have up to 64 members, you can choose from zero to an unlimited number of them, occupying storage space is one to eight bytes, possible by the set The number of members is determined.
For example, in SQL Server, you can save to use a Bit type to represent gender (male/female), but MySQL does not have Bit, so use TINTINT? No, you can use ENUM (handsome guy, crush)! There are only two options, so it only needs one byte-the same size as TINYINT, but it can be accessed directly with the string handsome guy and crush.It's so convenient!
The so-called "number type" refers to DECIMAL and NUMERIC, they are the same type.Strictly speaking, it is not a type of numbers, because they actually store numbers in the form of strings; each digit of its value (including the decimal point) occupies one byte of storage space, so this type consumes more space big.But one of its outstanding advantages is that the number of decimal places is fixed, and it will not be "distorted" in calculations, so it is more suitable for fields such as "price" and "amount" that require low accuracy but very high accuracy requirements.
Decimal type, that is, floating-point number type, according to the different precision, there are FLOAT (single precision) and DOUBLE (double precision).Their advantage is accuracy.FLOAT can represent decimals with very small absolute values, as small as about 1.17E-38 (0.000...0117, 37 zeros after the decimal point), and DOUBLE can even represent absolute values as small as about 2.22E-308 (0.000...0222, 307 zeros after the decimal point) decimal.FLOAT type and DOUBLE type occupy 4 bytes and 8 bytes of storage space respectively.If you need to use the decimal field, the precision is not high, of course use FLOAT! But to be honest, how can our "civilian" data require such high precision? I haven't used these two types so far-I haven't encountered a case that is suitable for using them.
The most used, and the most worthy of careful calculation, is the integer type.From TINYINT, which only occupies one byte of storage space, to BIGINT, which occupies 8 bytes, choosing a type that is “enough” and occupies the smallest storage space should be considered when designing a database.The storage space occupied by TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT is 1 byte, 2 bytes, 3 bytes, 4 bytes and 8 bytes respectively.For unsigned integers, the largest integers that these types can represent are respectively These are 255, 65535, 16777215, 4294967295, and 18446744073709551615.If it is used to save the age of the user (for example, it is not advisable to save the age in the database), use TINYINT; in Ninetowns "Longitudinal", use SMALLINT for each skill value; if you want to use Make the IDENTIFY field of AUTO_INCREMENT of a table that will definitely not exceed 16 million rows.Of course, use MEDIUMINT instead of INT.Just imagine that each row saves one byte, 16 million rows can save more than 10 trillion!
Second, the date and time type.
The date and time types are relatively simple.They are nothing more than DATE, TIME, DATETIME, TIMESTAMP, and YEAR.For fields that are only sensitive to dates, and do not require time, use DATE instead of DATETIME.Needless to say; the use of time alone sometimes happens-use TIME; but the most commonly used is DATETIME.There is nothing to do about date and time types, so I won’t go into details here.
Three, 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), no matter whether the data you store reaches 10 bytes, it will take up 10 bytes of space; and VARVHAR It is variable length.If the possible value of a field is not a fixed length, we only know that it cannot exceed 10 characters.It is the most cost-effective to define it as VARCHAR(10).The actual length of the VARCHAR type is it The value of (actual length + 1).Why "+1"? This one byte is used to store the actual length used! It should also be seen from this "+1" that if a field, its possible value is up to 10 characters, and in most cases, when 10 characters are used, it is not economical to use VARCHAR: because in most cases In this case, the actual occupied space is 11 bytes, which is one more byte than CHAR(10)!
For example, it is a table that stores stock names and codes.Most stock names are four characters, that is, 8 bytes; for stock codes, Shanghai is a six-digit number, and Shenzhen is Four digits.These are all fixed lengths.Of course, the stock name must use CHAR(8); although the stock code is not of fixed length, if VARVHAR(6) is used, the actual space occupied by a Shenzhen stock code is 5 bytes, and a Shanghai stock code The stock code takes up 7 bytes! Considering that there are more stocks in Shanghai than in Shenzhen, VARCHAR(6) is not as cost-effective as CHAR(6).
Although the maximum length of a CHAR or VARVHAR can be up to 255, I think CHAR larger than 20 is almost useless-there are few fixed-length stuff larger than 20 bytes, right? Use VARCHAR if it is not a fixed length! VARCHAR larger than 100 is also almost unused-TEXT is better for larger ones.TINYTEXT, the maximum length is 255, and the occupied space is also (actual length + 1); TEXT, the maximum length is 65535, and the occupied space is (actual length + 2); MEDIUMTEXT, the maximum length is 16777215, and the occupied space is (actual length + 3); LONGTEXT , The maximum length is 4294967295, and the occupied space 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 body of the article.According to the actual situation, choose different types from small to large.
Four, enumeration and collection types.
Enumeration (ENUM) type, you can define up to 65535 different strings to choose from, you can only and must choose one of them, and the storage space is one or two bytes , Is determined by the number of enumerated values; set (SET) type, can have up to 64 members, you can choose from zero to an unlimited number of them, occupying storage space is one to eight bytes, possible by the set The number of members is determined.
For example, in SQL Server, you can save to use a Bit type to represent gender (male/female), but MySQL does not have Bit, so use TINTINT? No, you can use ENUM (handsome guy, crush)! There are only two options, so it only needs one byte-the same size as TINYINT, but it can be accessed directly with the string handsome guy and crush.It's so convenient!
Tags
Technical otaku
Sought technology together
0 Comments