Excerpt from: http://zuo.ai.xiao.blog.163.com/blog/static/6079155320121293750732/
1. Number Type
Signed unsigned storage (bytes)
tinyint-128 to 127 0 to 255 1 = 2^8 (one byte equals 8 bit binary)
smallint-32768 to 32767 0 to 65535 2
mediumint-8388608 to 8388607 0 to 16777215 3
int-2147483648 to 2147483647 0 to 4294967295 4
bigint-9223372036854775808 to 9223372036854775807
0 to 18446744073709551615 8
float-3.402823466e+38 to -1.175494351E-38
, 0 and 1.175494351E-38 to 3.402823466E+38 4
double-1.7976931348623157e+308 to -2.2250738585072014E-308
, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308 8
Decimal (M,D) is suitable for storing prices, and M is the total number of decimal digits (precision)
(Max 65, default), D is the number of digits after the decimal point (Scale) (max 30, default 0) m+2
2. Character Type
Range
CHAR 0 to 255 (2^8-1)
Tinytext 0 to 255 (2^8-1)
VARCHAR 0 to 65535 (2^16-1)
TEXT 0 to 65535 (2^16-1)
Mediumtext 0 to 16777215 (2^24–1)
Longtext 4,294,967,295 or 4GB (2^32–1)
3. Date type
Range format Storage (bytes)
DATE ' 1000-01-01 ' to ' 9999-12-31 ' yyyy-mm-dd ' 3
Time ' -838:59:59 ' to ' 838:59:59 ' HH:MM:SS ' 3
TIMESTAMP ' 1970-01-01 00:00:00 ' to 2037 timestamp (1330502234) 4
DATETIME ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ' yyyy-mm-dd HH:MM:SS ' 8
4. Other Types
Range Storage
bit[(M)] 1 to (m+7)/8 bytes
SET ("v1", "v2" ...) Maximum 64 values 1,2,4 or 8 bytes
ENUM ("v1", "v2" ...) Maximum 65,535 values 1 or 2 bytes
Number Type
(Note the following sources of information: Http://blog.zxlm.cn/2006/05/mysql-%E7%9A%84%E6%95%B0%E6%8D%AE%E7%b1%bb%e5%9e%8b%e5%92%8c%e5%bb%ba%e5 %ba%93%e7%ad%96%e7%95%a5.html)
Down to: "MySQL performance tuning and architecture design"
Date Type:
Other types
First, the number type
Number types fall into three categories according to my classification: integer, Decimal, and number classes.
What I call the "number class" means DECIMAL and NUMERIC (In MySQL, there's no difference between a decimal and a numeric.NUMERIC is a synonym for decimal, as the MySQL manual says "the DECIMAL and NUMERIC types is implemented as the same type by MySQL"), they are the same type. It's strictly not a number type, because they actually save numbers as strings; each bit of his value (including the decimal point) occupies one byte of storage space, so this type is much more space-consuming. However, it is a prominent advantage is that the number of decimal places fixed, in the operation will not be "distorted", so it is more suitable for "price", "amount" such as the accuracy of the requirements of the very high accuracy requirements of the field. The
Decimal class, the floating-point number type, has float and DOUBLE two depending on the precision. Their advantage is precision, and FLOAT can represent very small, small to about 1.17E-38 (0.000 ... 0117, the decimal point is followed by 37 0) of the decimal, and the DOUBLE can represent an absolute value of about 2.22E-308 (0.000 ... 0222, a decimal number with 307 0 after the decimal point). The FLOAT type and DOUBLE type occupy storage space of 4 bytes and 8 bytes, respectively. If you need to use a decimal field, the accuracy is not high, of course, with FLOAT. But to say a word, we "civilian" data, which has the requirements of high precision? I have not used these two types so far-I have not yet encountered cases that are appropriate to use them. The most, and most prudent, of the
is the integer type. From TINYINT, which accounts for only one byte of storage, to the 8-byte BIGINT, picking a type that is "enough" and taking up the least amount of storage space should be considered when designing the database. TINYINT, SMALLINT, Mediumint, INT, and BIGINT occupy storage spaces of 1 bytes, 2 bytes, 3 bytes, 4 bytes, and 8 bytes respectively, These types The largest integers that can be represented are 255, 65535, 16777215, 4294967295, and 18446744073709551615, respectively. If it is used to save the user's age (for example, it is not advisable to save the age in the database), the TINYINT is sufficient; in the nine-city "aspect", the skill value, with SMALLINT also enough; If you want to use it as a auto_ for a table that is definitely not more than 16000000 rows INCREMENT IDENTIFY field, of course, with Mediumint without INT, imagine, each row savings of one byte, 16000000 rows can save 10 trillion more.
Ii. Date and Time type
The total class of time storage format is not too many, we commonly used mainly is DATETIME, DATE and TIMESTAMP these three kinds. from the storage space TIMESTAMP (FC Note:) The minimum, four bytes, and the other two data types are eight bytes, a bit more than a second. The disadvantage of TIMESTAMP is that he can only store time since 1970, while the other two types of time can be stored as early as 1001. If there is a need to store the time before 1970, we must discard the TIMESTAMP type, but as long as we do not need to use the time before 1970, it is best to use TIMESTAMP to reduce the storage footprint. (Come to: MySQL performance tuning and architecture design )
Three, character (string) type
Do not assume that the character type is the difference between Char,char and VARCHAR is that char is a fixed length, as long as you define a field is char (10), then regardless of whether your stored data reaches 10 bytes, it takes up 10 bytes of space, and VARCHAR is a variable Length, if a field possible value is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10).The type of VARCHAR that occupies space is the actual length of its value +1. Why do you want +1? This byte is used to hold the length that is actually used. It should also be seen from this +1 that if a field has a maximum value of 10 characters and, in most cases, 10 characters, it is not advantageous to use VARCHAR: because in most cases, the actual footprint is 11 bytes, which consumes one more byte than CHAR (10).
For example, a table that stores the name and code of a stock, the stock name is mostly four characters, or 8 bytes; Stock code, Shanghai is six digits, Shenzhen is four digits. These are fixed-length, the stock name of course to use CHAR (8); Although the stock code is not fixed length, if using VARCHAR (6), a Shenzhen stock code actually occupies 5 bytes, and a Shanghai stock code to occupy 7 bytes! Given the number of shares in Shanghai more than in Shenzhen, then VARCHAR (6) is not as good as CHAR (6).
Although the maximum length of a char or VARCHAR can be up to 255, I think a char greater than 20 is almost impossible-there are few fixed-length things that are more than 20 bytes long? It is not fixed-length to use VARCHAR. A VARCHAR greater than 100 is almost impossible to use-a larger TEXT is better than that. Tinytext, the maximum length is 255, the occupied space is also the actual length +1;text, the maximum length 65535, occupies the space is the actual length +2;mediumtext, the maximum length 16777215, occupies the space is the actual length +3;longtext, the maximum length 4294967295, the space occupied is the actual length of +4. Why + 1, + 2, + 3, +4? You should have hit PP if you didn't know it. These can be used in forums, news, whatever, to save the text of the article. Depending on the actual situation, choose from small to large different types.
Iv. Enumerations and collection types
Enum (enum) type, you can define up to 65535 different strings to choose from, only and must select one, occupy storage space is one or two bytes, determined by the number of enumeration values; set type, up to 64 members. You can select more than 0 of these to be unqualified, and occupy storage space is one to eight bytes, determined by the number of possible members of the collection.
For example, in SQL Server, you can save to a bit type to represent gender (male/female), but MySQL has no BIT, with Tintint? No, you can use ENUM (' Handsome ', ' Meimei '), there are only two options, so just a byte-as big as TINYINT, but can be directly with the string ' handsome ' and ' Meimei ' to access. It's so convenient!
MySQL field Storage type
