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

This article mainly introduces the application of MySQL enumeration and collection types, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the application of MySQL enumeration and collection types.

1. Background

* MySQL database provides two special data types for string storage: enumerated type ENUM and collection type SET. Collection type data types can give us more skills to improve performance, reduce storage capacity and reduce program code understanding.

two。 Enumeration and collection types belong to the space and the maximum number of members

Type occupancy byte

Maximum number of members ENUM1 or 2, depending on the number of enumerated members 65535SET1 | 2 | 3 | 4 | 8, depending on the number of collection members 64

3. Application of enumeration and set constraint checking

* create table edistribus

Mysql > CREATE TABLE eBay (- > uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> sex ENUM ('male',' female') NOT NULL->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.06 sec)

* clear constraints check SQL_MODE

Mysql > set sql_mode='';Query OK, 0 rows affected (0.01 sec)

* insert normal data [value in enumeration member]

Mysql > INSERT INTO eBay's SELECT null, 'male';Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0

* insert abnormal data [value is not in enumeration member] [just warning]

Mysql > INSERT INTO eBay's SELECT null, 'males';Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Duplicates: 0 Warnings: 1

* View the warning inserted with abnormal data

Mysql > SHOW WARNINGS +-+ | Level | Code | Message | +- -+ | Warning | 1265 | Data truncated for column 'sex' at row 1 | +-+ 1 row in set (0.00 sec)

* View all the data in the eBay table [illegally inserted ENUM columns will be displayed as empty]

Mysql > SELECT * FROM ejaculsThere is a combination of rows in set + | uid | sex | +-+-+ | 1 | male | | 3 | +-+-+ 2 sec)

* set SQL_MODE constraint check

Mysql > SET sql_mode = 'STRICT_TRANS_TABLES';Query OK, 0 rows affected (0.00 sec)

* insert abnormal data [value is not in enumeration member] [error returned]

Mysql > INSERT INTO eBay's SELECT null, 'males';ERROR 1265 (01000): Data truncated for column' sex' at row 1

4. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

After reading the above about the application of MySQL enumeration and set types, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.