What are the types of MySQL fields

int type includes (tinyint, smallint, mediumint, int, bigint)

Tinyint is a 1 byte expression range of 2 8 (-128-128) or (0-255) Many people do not understand why there are two cases, because in the computer there is a statement called unsigned, that is, all positive integers, so in the MySQL Choose the shape of the field must be added unsigned is unsigned, because the negative number is not used.

SmallInt is a 2 byte expression range 2 of 16

SmallInt is a 3 byte expression range 2 of 24

int is 4 byte expression range 2 of 32, remember that this type is not stored mobile phone number, many people use it to save and then direct error, because int up to 10 length, mobile phone number is 11 bits, so if the cell phone number my suggestion is the following char type

The bigint is a 8-byte expression that is 2 of the 64-square.

Char type

Expression range 0-255 Bytes, if it is the letter is 255, because each letter is only one byte, no matter what encoding, if it is stored gbk Chinese is 2 bytes representing a Chinese character, so is 255/2, if the UTF8 code is 255/ 3, because the Chinese in the UTF8 encoding occupies three bytes to represent a Chinese character. Correctly said to be called Unicode encoding, also known as the Universal code, his implementation has UTF8

VARCHAR type

The expression range is 0-65535 bytes, I have an elder colleague, older, he still thinks that varchar max is 0-255, this is wrong, if the version before MySQL5.0.3 is right, Later versions have been changed to 65535 bytes theoretically storing Chinese characters UTF8 is more than 2000.

Text type

The expression range is 65535 bytes, generally used to store the product description, such as more than the number of characters, it also has 4 text types: Tinytext, text, Mediumtext and longtext, storage capacity is different, contains character set.

BLOB type

Storing data in binary form, yes can store pictures. Degrees out remained the picture.

Decimal type

the declaration syntax for the decimal column is decimal (m,d), and M is the maximum number (precision) of the number. The range is 1~65 (in the older MySQL version, the allowable range is 1~254), and D is the number of digits to the right of the decimal point (scale). Its scope is 0~30, but not more than M, is generally used to store money, such as: decimal (10,2), he is a string storage of numbers, does not cause the storage of data is not normal, short weight, the storage accuracy of high demand for the best choice of this field, Those who choose the bigint type to save money really do not know how to think. I don't know if we have this type of field.

Float type

Floating point type, that is, the number of decimal, the effect is general.

Enum type

Enumeration type, which is the number of default characters entered when a field is built, and the store can store only one of these characters. For example: Enum ("Y", "N");

Set type

A collection. There can be between 0 and 64 values, both from the specified content and the enum, but the enum can store only one value, but the set can store multiple values, separated by commas if stored with multiple values.

Date type

Storage date format: ' Yyyy-mm-dd ', Range: ' 1000-01-01 ' to ' 9999-12-31 '.

Time Type

Storage time format: ' HH:MM:SS ';

Datetime

In fact, date and time are combined to store dates and times in the format: ' Yyyy-mm-dd HH:MM:SS ', Range: ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '.

Timestamp type (timestamp)

Format: ' Yyyymmddhhmmss ', ' Yymmddhhmmss ', ' YYYYMMDD ', ' YYMMDD ', Range: ' 1970-01-01 00:00:00 ' to ' 2037-01-01 00:00:00 '.

General storage time or int type, store timestamp

Year Type

Storage year, format "YYYY";

I'll write down some of my understanding and suggestions for building the table:

When you build a table, it is best to have the primary key ID, the primary key is not, but in order to correlate and query the best use of int type as the primary key, remember is unsigned (unsigned);

2, I see a lot of people in order to store dozens of to hundreds of of the number used, int (3) Such a type, in fact, this is not right, because as long as the int is to occupy 4 bytes, no matter how much is the amount of surplus out of the waste;

3, char and varchar one is fixed long one is variable length, general char storage phone number, password fixed long encrypted, why not recommended varchar, because the Varvhar type is variable length, in the search will be slower than the char type, if the user table login and password It is best to choose char so that the effect will be much better, query quickly, other fields can see the situation to choose the varchar type, tell everyone a lot of people do not know things such as: char (10) and varchar (10), both store 10 characters, varchar will occupy a byte more, Because of the need to store the length of the computer in the search time, the fixed length of data search will be faster;

4, can use fixed length field is fixed length, can use more compact type to use more compact type. The effect will be much better;

5, the establishment of the table when the field should be reasonable, not more built in a table, also do not have to be open, the specific situation to see the use of the scene;

6, when the table is built in the most common where the field index, ordinary index is enough, but not necessarily, such as mailbox or mobile phone number, these can add some unique index (uniqie), so that the data can be unique, query faster;

7, according to the use of the scene and business, add a different index, index commonly used are: Primary key index, unique index, ordinary index, and full-text index. You can put multiple fields together to build an index called a federated index, such as I indexed the user name and password, in fact, this is not recommended, because when searching, it is best not to select * from table where username= "Usename" and password= " Password ", but to select * from table where username=" username ", query out and then compare the results and password;

8, the query data can use the primary key, use the primary key, so that the query MySQL does not need to scan the table but like a person to turn the directory, immediately find out, in fact, all the index of MySQL is for this "directory", can be found in the query immediately;

9, if considering the future data growth will soon, can be in the table when the tables, such as the comment table, can be based on a hash algorithm, the establishment of a number of comment tables, so the query will be much better, in fact, is nothing more than the large chunks of data, scattered, reduce the size of the table scan

What are the types of MySQL fields