Advantages and disadvantages of MySQL int char bit set Enum and Its Application

In many cases, int char bit set Enum can be replaced. The advantages and disadvantages of these types are described below.

Take the gender field used to create a user table in the database as an example. What are the advantages and disadvantages of the following types?

1. INT: Gender tinyint unsigned not null

The advantage of defining gender columns is that the storage space is small (1 byte) and the scalability is good (range 0 ~ 255)

The disadvantage is that there is no strong constraint (it can be controlled in the Code), the readability is not good, and there is no way to know what 0 1 2 actually means

2. Char (1): Gender char (1) not null

Similar to int

3. Bit (1): Gender bit (1) not null

In fact, bit has no advantages in storage space. MyISAM will make the bit a round, for example, bit (17) will become 3 bytes storage. InnoDB and memory choose the int type with the smallest capacity to store this column.

4. Set: This is useless.

5. Enum: Gender Enum ("male", "female") not null

The advantage of Enum is obvious, that is, the storage space is small and there is a corresponding string ing relationship. Its data stores an integer index of 2 bytes in size, and the real string value is stored in. in the frm file, convert each query and insertion.

The disadvantage is that the scalability is poor. If you want to expand the enum value every time, you need to use alter table.

As far as gender is concerned, I think the best way is to create a code table and separate the Gender column into a table, as shown below:

Create Table gender (

Id int unsigned not null primary key,

Text varchar (8) Not null

)

Then, you can reference gender IDS as foreign keys in the User table.

Make a cache for the query of the Gender table, for example

Select * from gender;

Select * from gender where id = ?;

...

In this way, gender information can be easily queried (for example, when the gender select option of the page is generated) without affecting the efficiency, you only need to insert or update the Gender table to add table items for extension. This is much better than alter table and does not affect program code (for example, the option code on the page is generated ), to achieve code decoupling from the database, you only need to flush the cache once after updating the table items. Generally, the ORM framework also has a delay in loading and so on. Using these technologies can further improve the efficiency.

If you do not consider the needs of these projects, I think it is best to use Enum, but if you need to get the enum value in the project, the program code is indeed a troublesome thing, you can consider loading the enum value to the static variable in the project at project startup. Restart the project each time the value is updated, however, the Code for dynamically generating table items needs to be compiled by yourself, and it is troublesome to expand Enum (alter table ).

 

The second example is the privilege column in the User table. There are two options:

1. Set: privilege set ("read", "write", "EXE") not null

Similar to Enum, the difference is that set can have multiple values at the same time, such as read, write, read, EXE, and so on.

The disadvantage is also the same as that of enum. alter table is required for expansion,

2. INT: privilege tinyint unsigned not null

This method is the same as the permission control in Linux. You can define the permission as a binary number, for example

Read = 1 <0;

Write = 1 <1;

EXE = 1 <2;

It is easy to judge the permission. For example, to have a permission p1 = 3 and know what permissions he has, you only need to perform & calculation.

If (P1 & read! = 0 ){...}

You can see that P1 has the read permission.

In combination with projects, you can also create tables for privilege like gender:

Create table Privilege (

Id tinyint unsigned not null primary key,

Text varchar (6) Not null

)

It is the same as gender in use.