Mysql data types and database strategy analysis _mysql

One, the number type. The number types are grouped into three categories according to my classification: integer, Decimal, and numeric.

What I call "digital class" means decimal and numeric, which are the same type. It's strictly not a numeric type because they actually save the number as a string, and every bit of his value (including the decimal point) occupies a byte of storage space, so this type of space is much more expensive. But one of its prominent advantages is that the number of decimal digits fixed, in the operation will not "distorted", so the more suitable for the "price", "amount" for the accuracy of the request is not high accuracy requirements of a very high level of the field.

Decimal class, that is, floating point type, according to the accuracy of different, there are float (single precision) and double (double precision) two. Their advantage is precision, float can mean that the absolute value is very small, small to about 1.17E-38 (0.000 ...). 0117, a decimal number with 37 0 after the decimal point, and a double can represent an absolute value smaller than about 2.22E-308 (0.000 ...). 0222, the decimal number of 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 precision is not high, of course, with float! But to say the truth, we "civil" data, which has the requirement of precision so high? These two types have not been used so far-I have not yet encountered cases that are appropriate for using them.

The most used, the most worthy of reckoning, is the integer type. From a tinyint of only one byte of storage space to a 8-byte bigint, it should be considered when designing a database to pick a "sufficient" type that occupies the smallest amount of storage space. TINYINT, SMALLINT, mediumint, int, and bigint occupy storage space of 1 bytes, 2 bytes, 3 bytes, 4 bytes, and 8 bytes, and for unsigned integers, the largest integers that these types can represent are 255, 65535, 16777215, respectively. 4294967295 and 18446744073709551615. If you are saving the age of the user (for example, the age in the database is not advisable), with tinyint is enough; in the "vertical and horizontal" of the nine city, the skill value, with smallint also enough; If you want to use a table that is definitely not more than 16000000 rows, auto_ Increment identify field, of course, with Mediumint not INT, imagine, each line to save a byte, 16000000 lines can save 10 trillion more!

Date and Time type.

The date and time types are simpler, with a few types of dates, times, DATETIME, timestamp, and year. Only date sensitive, but not required for time fields, use date without a datetime is needless to say, the use of time alone is also the case---use times, but the most use or DateTime. There is nothing to do on the date-time type, and it is no longer detailed here.

Three, character (string) type.

Do not assume that the character type is char! The difference between char and varchar is that char is a fixed length, so long as you define a field as char (10), it takes up 10 bytes of space regardless of whether the data you store is 10 bytes, and Varvhar is variable length, If the possible value of a field is an unfixed length, we only know that it cannot be more than 10 characters, and defining it as VARCHAR (10) is the most cost-effective, and the actual length of the VARCHAR type is its value (actual length + 1). Why "+1"? This byte is used to save how much length is actually used! From this "+1" you should also see that if a field, its maximum possible value is 10 characters, and in most cases, when it comes to 10 characters, it is not cost-effective to use varchar: because in most cases, the actual footprint is 11 bytes, which takes up a byte more than char (10)!

For example, a table that stores the name and code of a stock, the stock name is mostly four words, 8 bytes; The stock code, Shanghai is six digits, Shenzhen is four digits. These are fixed-length, the stock name of course to use CHAR (8); The stock code is not fixed length, but if the use of Varvhar (6), a Shenzhen stock code actually occupy space is 5 bytes, and a Shanghai stock code to occupy 7 bytes! Given that there are more shares in Shanghai than in Shenzhen, using varchar (6) is not as good as char (6).

Although the maximum length of a char or varvhar can be up to 255, I think char greater than 20 is almost impossible--there are few fixed-length things larger than 20 bytes long? It's not fixed-length, it's varchar!. varchar, which is greater than 100, is also almost impossible to use-it is better to use text than this. Tinytext, the maximum length is 255, Occupy space is also (actual length + 1); TEXT, maximum length 65535, occupy space is (actual length +2); mediumtext, maximum length 16777215, occupy space is (actual length +3); Longtext, Maximum length 4294967295, occupy space is (actual length +4). Why "+1"? "+2"? "+3"? "+4"? If you don't know, you should hit pp. These can be used in forums, news, whatever, to keep the text of the article. Depending on the actual situation, choose from small to large different types.

Four, enumerations, and collection types.

Enumeration (enum) type, you can define up to 65535 different strings to choose from, and you can only and must select one of them, which occupies a storage space of one or two bytes, determined by the number of enumerated values; the collection (set) type, which can have up to 64 members, You can select 0 of them to an unqualified number, and occupy a storage space of one to eight bytes, determined by the number of possible members of the collection.

For example, in SQL Server, you can save a bit type to represent gender (male/female), but MySQL has no bit, with Tintint? No, you can use enum (' Handsome Boy ', ' Meimei ')! There are only two options, so just a byte--as big as tinyint, but it can be accessed directly with the string ' handsome ' and ' Meimei '. It's so convenient!

Well, MySQL data type introduction is similar, my library strategy with the introduction of data types introduced to you. But this is only part of the space which cannot be further discussed, and the rest is based on a lot of practice and discussion on the basis of the understanding of data types.