How to use enumeration types in mysql

How to use enumeration types in mysql: 1. Insert data, the syntax is [insert into my_enum values ​​(1), (2);]; 2. Error data, the syntax is [insert into my_enum values ​​( 'male');].

Related learning recommendations: mysql tutorial

Enumeration types in mysql Usage method:

Enumeration: enum, realize all possible results are designed, and the actual stored data must be one of the specified data.

How to use enumeration

Definition: enum (list of possible elements);

such as enum ('male', 'female' )

Use: store data, only the data defined above can be stored

The meaning is:

1, the possibility of limiting the value!

2, Fast, faster than ordinary strings!

The reason is that enumeration types are managed using integers and can be managed in 2 bytes!

Each value is an integer identifier, starting from the first option at 1 and increasing one by one!

Management is in the form of integers, which is faster than strings!

There are 2 bytes in total, 0-65535, so there are 65535 options available! ,

Create enumeration table

create table my_enum(
　　gender enum('男','女','保密')
)charset utf8;

One of the functions: standardize the data format, the data can only be one of the specified data

Function 2: Save storage space (enumerations usually have an alias: radio button). Enumerations actually store values, not the strings themselves

In mysql, the system also automatically converts the format Yes, and basically the same as PHP (especially string to number)

Insert data

-- Valid data

insert into my_enum values('男'),('保密');

-- Numerical insertion into enumeration elements

insert into my_enum values (1),(2);

The principle is as follows

Error data

insert into my_enum values('male'); -- 错误：没有该元素

Proof that the field is stored The data is a numerical value: by removing 0 from the data, you can determine whether the original data is stored as a string or a numerical value. If it is a string, the final result will always be 0, otherwise it will be other values

--Change the field result Take it out and perform 0 calculation

select gender + 0,gender from my_enum;

Find out the actual rules of enumerating elements: numbering starts from 1 according to the order in which the elements appear

Principle of enumeration: enumeration When performing data specification (when defining), the system will automatically establish a corresponding relationship between numbers and enumeration elements (the relationship is placed in the log); then when inserting data, the system will automatically convert characters into corresponding numbers. Store, and then when extracting data, the system automatically converts the number into a string for display.

Because my enumeration actually stores numerical values, I can directly insert the numerical values ​​

If you want to learn more about programming, please pay attention to the php training column!

The above is the detailed content of How to use enumeration types in mysql. For more information, please follow other related articles on the PHP Chinese website!