In the previous two articles, we introduced many data types , Do you have eyelashes , We are mysql When creating a data table in , Which data type should I choose ？ This time, let's simply sort it out .

Rules

No matter how we create the data table , First of all, we need a rule , What criteria ？

That is to say , We want to ensure that the most accurate type should be used in any case , That is, in all types that can represent the value of the column , This type uses the least storage .

Integers and floating point numbers

If you don't need a fraction , Use integers to store data ; If necessary, the decimal part , Just use the floating point number type .

For floating-point data columns , The stored values will round the decimal places defined in the column .

What do you mean ？ Come on , for instance ：

Suppose the range of values of a column is 1~99999, If you use integers , be MEDIUMINT UNSIGNED It's the best type ; If you need to store decimals , Then we should use float Type .

Floating point types include float and DOUBLE type .DOUBLE The type accuracy is better than FLOAT Type high , So , When high storage accuracy is required, you should choose DOUBLE type .

Floating point and fixed point numbers

Floating point numbers FLOAT、DOUBLE Relative to the fixed number DECIMAL What are the advantages ？

The advantage is ：

In the case of a certain length , Floating point numbers can represent a larger range of data .

Because floating point numbers are prone to error , Therefore, when the accuracy requirements are high , Fixed point number is recommended DECIMAL To store .

DECIMAL stay mysql Is stored as a string , It is used to define data with high accuracy requirements such as currency .

Date and time type

mysql There are many data types for different kinds of dates and times , such as year and time. If you only need to record the year , Then use year The type is enough ; If you only need to record the time , Then use time The type is enough .

If you need to record the date and time at the same time , Then we can use timestamp perhaps datetime type . because timestamp The value range of column is less than datetime Value range of . Therefore, it is best to use the date with a large storage range datetime type .

CHAR and VARCHAR Between the characteristics and choices

char and varchar The difference is as follows ：

• char It's a fixed length character ;varchar It's a variable length character

• char The trailing space of the inserted data will be deleted automatically ,carchar Does not remove trailing spaces

char It's a fixed length , So his processing speed is faster than varchar It's faster , But the opposite also has disadvantages , That is a waste of storage space , So more storage is not enough , But it can be used if there are requirements on speed char type , Instead, you can use varchar Type to implement .

ENUM and SET

ENUM Only single value can be taken , His data list is an enumerated collection . His list of legal values allows at most 65535 Members .

therefore , When you need to select one from multiple values , have access to ENUM.

such as , The gender field is suitable to be defined as ENUM type , It's only from “ male ” perhaps “ Woman ” Take a value from ;

SET You can take multiple values . His legal value can be at most 64 Members .

An empty string is also a legal SET value . When you need to take multiple values , Suitable for use set type , For example, if you want to store one's hobbies, you can use SET type , For example, one can enjoy playing basketball , You can also like reading books .

BLOB and TEXT

BLOB It's a binary string ,TEXT It's a non binary string , Both can store large amounts of information .BLOB It mainly stores pictures 、 Audio information, etc , and TEXT Only text files can be saved .

In this case , For example, it is a multimedia platform , We're going to use BLOB type , Otherwise, use TEXT The type is enough .

thus , In this paper, the end .

For more information, go to VX official account “ Operation and maintenance home ” , Get the latest article .

------ “ Operation and maintenance home ” ------

------ “ Operation and maintenance home ” ------

------ “ Operation and maintenance home ” ------

linux Interrupt exception ,linuxuml modeling ,linux Compile multiple source files ,linux How to update the time ,linux Paging video tutorial in ,
linux How to decompress gzip,linux Unzip a directory , How to close linux gateway ,linux Document structure diagram ,linux Improve the permission to execute ,
linux What mark is used in the document , Network security linux strengthening ,linux Check email in ,linux Change the background color ,linux Build a website under the environment .