MySQL的枚举类型的特点和用法

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

mysql> USE testdb;
Database changed
mysql> CREATE TABLE sample (
-> name VARCHAR(40),
-> size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO sample (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO sample (name, size) VALUES ('suit',5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sample (name, size) VALUES ('pants','x-small');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sample (name, size) VALUES ('skirt','xxl');
ERROR 1265 (01000): Data truncated for column 'size' at row 1
mysql> INSERT INTO sample (name, size) VALUES ('hat',null);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT name, size ,size + 0 FROM sample;
+-------------+---------+----------+
| name | size | size + 0 |
+-------------+---------+----------+
| dress shirt | large | 4 |
| t-shirt | medium | 3 |
| polo shirt | small | 2 |
| suit | x-large | 5 |
| pants | x-small | 1 |
| hat | NULL | NULL |
+-------------+---------+----------+
6 rows in set (0.00 sec)

mysql>

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE t (size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES ('medium');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE t_noenum (size VARCHAR(200));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_noenum VALUES ('medium');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+--------+
| size |
+--------+
| medium |
+--------+
1 row in set (0.00 sec)

mysql> select * from t_noenum;
+--------+
| size |
+--------+
| medium |
+--------+
1 row in set (0.00 sec)

mysql> select @@datadir;
+---------------------+
| @@datadir |
+---------------------+
| /data/mysqldb/data/ |
+---------------------+
1 row in set (0.00 sec)

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 22
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> SELECT name, size ,size + 0 FROM sample ORDER BY size DESC;
+-------------+---------+----------+
| name | size | size + 0 |
+-------------+---------+----------+
| suit | x-large | 5 |
| dress shirt | large | 4 |
| t-shirt | medium | 3 |
| polo shirt | small | 2 |
| pants | x-small | 1 |
| hat | NULL | NULL |
+-------------+---------+----------+
6 rows in set (0.00 sec)

mysql> SELECT MAX(size) FROM sample;
+-----------+
| MAX(size) |
+-----------+
| x-small |
+-----------+
1 row in set (0.00 sec)

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 25
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> DROP TABLE t;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t (size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES ('medium');
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE t CHANGE size size ENUM('x-s', 's', 'm', 'l', 'x-l');
ERROR 1265 (01000): Data truncated for column 'size' at row 1
mysql> DESC t;
+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| size | enum('x-small','small','medium','large','x-large') | YES | | NULL | |
+-------+----------------------------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> TRUNCATE TABLE t;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t CHANGE size size ENUM('x-s', 's', 'm', 'l', 'x-l');
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC t;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| size | enum('x-s','s','m','l','x-l') | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> DESC t;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| size | enum('x-s','s','m','l','x-l') | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t CHANGE size size ENUM('x-s', 's', 'm' , 'x-m', 'l', 'x-l');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT size,size + 0 FROM t;
+------+----------+
| size | size + 0 |
+------+----------+
| x-s | 1 |
| s | 2 |
| m | 3 |
| l | 5 |
| x-l | 6 |
+------+----------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE t CHANGE size size ENUM('x-s', 's', 'l', 'x-l');
ERROR 1265 (01000): Data truncated for column 'size' at row 3
mysql> DELETE FROM t WHERE size = 3;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE t CHANGE size size ENUM('x-s', 's', 'l', 'x-l');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> DESC t;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| size | enum('x-s','s','l','x-l') | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> SELECT size,size + 0 FROM t;
+------+----------+
| size | size + 0 |
+------+----------+
| x-s | 1 |
| s | 2 |
| l | 3 |
| x-l | 4 |
+------+----------+
4 rows in set (0.00 sec)

mysql>