How to use enumeration type in mysql

How to use the enumeration type 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( & # 39; male & # 39;);].

Related learning recommendation: mysql tutorial

How to use the enumeration type in mysql:

Enumeration: enum, to achieve all possible results are designed, in fact, the 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 lies in:

1, the possibility of limiting the value!

2, fast, faster than ordinary strings!

The reason is that the enumeration type is managed by integers, which can be managed by 2 bytes!

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

Management in the form of integers, 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('Male', 'Male', 'Female', 'Confidential')
 )charset utf8;

The first function: standardize the data format, the data can only be one of the specified data

The second function: save storage space (number The enumeration usually has an alias: radio box), the actual storage of the enumeration is the value, not the string itself

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

insert data

— valid data

insert into my_enum values('male'),('confidential');

— value insert enumeration element

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

The principle is below

Error Data

insert into my_enum values('male'); -- error: no such element

Prove that the data stored in the field is a value: remove the +0 from the data to determine whether the original data is stored as a string or a value. If it is a string, the final result will always be 0, otherwise It is other values

— take out the field result and perform +0 operation

select gender + 0,gender from my_enum;
select gender + 0,gender from my_enum;
select gender + 0,gender from my_enum;
Find out the actual law of the enumeration elements: according to the order in which the elements appear, numbering starts from 1

Enumeration principle: enumeration is in the process of data specification  When (when defined), the system will automatically establish a corresponding relationship between numbers and enumerated elements (the relationship will be placed in the log); then when data is inserted, the system will automatically convert the characters into corresponding digital storage, and then perform  When data is extracted, the system automatically converts numbers into strings for display.  
Because my enumeration actually stores values, you can insert values ​​directly

For more programming learning, please pay attention to the php training column!  

The above is the detailed content of how the enumeration type is used in mysql. For more information, please pay attention to other related articles on 1024programmer.com!