MySQL characters commonly used segment type, introduction and how to use it to build tables experience sharing

Since the work of the company does not have a professional DBA and often need to establish a variety of tables to meet their business logic, so often check the MySQL manual or Google to view the relevant information, so I based on my work experience and relevant information to introduce MySQL various field types and their use. (Because I am also a rookie where the wrong to write, please correct, interested can explore)

MySQL field type

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";

Good to introduce these, I use more of it, there are some similarities, if you need a special look at the MySQL manual, basically almost.

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);

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 the case, because as long as the int is to take up 4 bytes, no matter how much is the amount of surplus out of the waste;

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;

A fixed-length field can be used with a fixed length, and a more compact type can be used in a more compact type. The effect will be much better;

When building a table, the field should be reasonable, not built in a table, nor must be open, the specific situation to see the use of the scene;

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;

Depending on the usage scenario and the business, adding different indexes, indexes are commonly used: Primary key index, unique index, normal 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;

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

If considering the future data growth will be very fast, you can build tables when the table, such as the comment table, can be based on a hash algorithm, set up a number of comment tables, so the query will be much better, in fact, is nothing more than the bulk of the data, scattered open, reduce the large area of the table scan

In fact, there are a lot of MySQL build table and optimization needs to write, because of the time of the problem to write these temporarily, and then have time to continue.

MySQL characters commonly used segment type, introduction and how to use it to build tables experience sharing