Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use enumerated types in mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The use of enumerated types in mysql: 1, insert data, syntax is [insert into my_enum values (1), (2);]; 2, error data, syntax is [insert into my_enum values ('male');].

Recommended for related study: mysql tutorial

How to use enumerated types in mysql:

Enumeration: enum, the implementation designs all possible results. In fact, the data stored must be one of the specified data.

How enumerations are used

Definition: enum (list of elements that may appear)

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

Use: store data, only the data defined above

The significance lies in:

1, the possibility of the limit value!

2, fast, faster than ordinary strings!

The reason is that enumeration is managed by integers and can be managed in 2 bytes!

Each value, is an integer identification, starting with the first option is 1, incremented one by one!

Management in the form of integers, faster than strings!

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

Create an enumeration table

Create table my_enum (gender enum ('male', 'female', 'confidential') charset utf8

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

Second: save storage space (enumerations usually have an alias: checkbox). Enumerations actually store numeric values, not strings themselves.

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

Insert data

-- valid data

Insert into my_enum values ('male'), ('confidential')

-- numeric insertion of enumerated elements

Insert into my_enum values (1) (2)

The principle is below.

Error data

Insert into my_enum values ('male');-- error: there is no such element

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

-- take out the result of the field for + 0 operation

Select gender + 0 gender from my_enum

The actual law of enumerated elements is found out: numbering starts with 1 according to the order in which the elements appear.

Enumeration principle: when enumerating data specification (when defining), the system will automatically establish a corresponding relationship between numbers and enumerated elements (the relationship is put in the log); then when the data is inserted, the system automatically converts characters into corresponding digital storage, and then when data extraction is carried out, the system automatically converts numbers into strings to display.

Because what I enumerate is actually a numerical value, I can insert the numerical value directly.

The above is all about how to use enumerated types in mysql. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.