MySQL中set和enum枚举类型插入值和索引比较--笔记
set，enum的数据类型都是字符串类型的对象，其中set最多可以包含64个元素，并且可以任意取到集合中的一个元素或多个原始。而enum则是只能取到集合中的一个元素，最多包含65536个元素，也就是说set是多项选择，enum是单项选择。
set和enum枚举类型必须字符串不能是数字，同时需要避免要使用字符串类型的数字方式的字符串值，容易和索引值混淆。
下面创建数字方式字符串就是不好的方式
set和enum特点比较
相同点：
插入索引为0或超出索引就插入空值
插入null就是null值
都插入不存在的值就插入空值
不同点：
set的索引是二进制（多值就是单个值索引二进制位相加）
enum索引是十进制
1、set类型
| Set元素 | 十进制索引 | 二进制索引 |
| '' | 0 | 0000 |
| 'a' | 1 | 0001 |
| 'b' | 2 | 0010 |
| 'a,b' | 3 | 0011 |
| 'c' | 4 | 0100 |
| 'a,c' | 5 | 0101 |
| 'b,c' | 6 | 0110 |
| 'a,b,c' | 7 | 0111 |
| 'd' | 8 | 1000 |
| 'a,d' | 9 | 1001 |
| 'b,d' | 10 | 1010 |
| null | null | null |
2、enum类型
| enum元素 | 索引 |
| '' | 0 |
| 'beijin' | 1 |
| 'chengdu' | 2 |
| 'jianyang' | 3 |
| 'pingwo' | 4 |
| null | null |
3、测试详细过程
[mysql@localhost ~]$ mysql.7308.login
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 812
Server version: 5.6.47-87.0-log huanglingfei make install Percona Server
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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.
root@localhost: 01:16 [7308][(none)]>
root@localhost: 20:46 [7308][(none)]>create database db_hlf;
Query OK, 1 row affected (5.00 sec)
root@localhost: 20:46 [7308][(none)]>
root@localhost: 00:25 [7308][(none)]>use db_hlf;
Database changed
root@localhost: 00:31 [7308][db_hlf]>
测试set和enum只支持字符串不支持数字
create table tbl_setenum(id int(11) not null primary key,setc set(1),enumc enum(5));
create table tbl_setenum(id int(11) not null primary key,setc set('1'),enumc enum(5));
create table tbl_setenum(id int(11) not null primary key,setc set(1),enumc enum('5'));
root@localhost: 00:31 [7308][db_hlf]>create table tbl_setenum(id int(11) not null primary key,setc set(1),enumc enum(5));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1),enumc enum(5))' at line 1
root@localhost: 00:31 [7308][db_hlf]>create table tbl_setenum(id int(11) not null primary key,setc set('1'),enumc enum(5));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5))' at line 1
root@localhost: 00:31 [7308][db_hlf]>create table tbl_setenum(id int(11) not null primary key,setc set(1),enumc enum('5'));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1),enumc enum('5'))' at line 1
root@localhost: 00:31 [7308][db_hlf]>
root@localhost: 00:31 [7308][db_hlf]>create table tbl_setenum(id int(11) not null primary key,setc set('1'),enumc enum('5'));
Query OK, 0 rows affected (0.01 sec)
root@localhost: 00:31 [7308][db_hlf]>
root@localhost: 00:32 [7308][db_hlf]>show create table tbl_setenum\G
*************************** 1. row ***************************
Table: tbl_setenum
Create Table: CREATE TABLE `tbl_setenum` (
`id` int(11) NOT NULL,
`setc` set('1') DEFAULT NULL,
`enumc` enum('5') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@localhost: 00:32 [7308][db_hlf]>drop table tbl_setenum;
Query OK, 0 rows affected (0.01 sec)
create table tbl_setenum(id int(11) not null primary key,setc set('a','b','c','d'),enumc enum('beijin','chengdu','jianyang','pingwo'));
root@localhost: 00:32 [7308][db_hlf]>create table tbl_setenum(id int(11) not null primary key,setc set('a','b','c','d'),enumc enum('beijin','chengdu','jianyang','pingwo'));
Query OK, 0 rows affected (0.01 sec)
root@localhost: 00:33 [7308][db_hlf]>show create table tbl_setenum\G
*************************** 1. row ***************************
Table: tbl_setenum
Create Table: CREATE TABLE `tbl_setenum` (
`id` int(11) NOT NULL,
`setc` set('a','b','c','d') DEFAULT NULL,
`enumc` enum('beijin','chengdu','jianyang','pingwo') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@localhost: 00:33 [7308][db_hlf]>
id 数字类型不指定默认插入是0
insert into tbl_setenum(setc,enumc)value(1,2);
root@localhost: 00:33 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)value(1,2);
Query OK, 1 row affected, 1 warning (0.01 sec)
root@localhost: 00:34 [7308][db_hlf]>select * from tbl_setenum;
+----+------+---------+
| id | setc | enumc |
+----+------+---------+
| 0 | a | chengdu |
+----+------+---------+
1 row in set (0.00 sec)
root@localhost: 00:34 [7308][db_hlf]>
root@localhost: 00:34 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)value('1,2',4);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
root@localhost: 00:56 [7308][db_hlf]>
id改为自增字段再次测试
create table tbl_setenum(id int(11) not null primary key auto_increment,setc set('a','b','c','d'),enumc enum('beijin','chengdu','jianyang','pingwo'));
root@localhost: 01:10 [7308][db_hlf]>create table tbl_setenum(id int(11) not null primary key auto_increment,setc set('a','b','c','d'),enumc enum('beijin','chengdu','jianyang','pingwo'));
Query OK, 0 rows affected (0.01 sec)
root@localhost: 01:10 [7308][db_hlf]>show create table tbl_setenum\G
*************************** 1. row ***************************
Table: tbl_setenum
Create Table: CREATE TABLE `tbl_setenum` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`setc` set('a','b','c','d') DEFAULT NULL,
`enumc` enum('beijin','chengdu','jianyang','pingwo') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@localhost: 01:10 [7308][db_hlf]>
插入索引和null
说明：
索引0时插入空值
插入null就是null
超出索引也是插入空值
需要特别注意是set的索引是二进制方式，enum枚举的索引是十进制
root@localhost: 01:36 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(1,1),(2,2),(3,3),(4,4),(5,5),(null,null),(0,0);
Query OK, 7 rows affected, 2 warnings (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 2
root@localhost: 01:36 [7308][db_hlf]>show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'enumc' at row 5 |
| Warning | 1265 | Data truncated for column 'enumc' at row 7 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)
root@localhost: 01:36 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
+----+------+----------+
7 rows in set (0.00 sec)
root@localhost: 01:36 [7308][db_hlf]>
插入实际值
说明：
不存在值就是插入空值
root@localhost: 01:44 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values('a','beijin'),('b','chengdu'),('c','jianyang'),('d','pingwo');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@localhost: 01:45 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
| 8 | a | beijin |
| 9 | b | chengdu |
| 10 | c | jianyang |
| 11 | d | pingwo |
+----+------+----------+
11 rows in set (0.00 sec)
root@localhost: 01:45 [7308][db_hlf]>
root@localhost: 01:51 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values('z','zhongguo');
Query OK, 1 row affected, 2 warnings (0.01 sec)
root@localhost: 01:51 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
| 8 | a | beijin |
| 9 | b | chengdu |
| 10 | c | jianyang |
| 11 | d | pingwo |
| 13 | | |
+----+------+----------+
12 rows in set (0.00 sec)
再次插入超出范围的索引测试
root@localhost: 01:51 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(100,100);
Query OK, 1 row affected, 2 warnings (0.00 sec)
root@localhost: 01:51 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
| 8 | a | beijin |
| 9 | b | chengdu |
| 10 | c | jianyang |
| 11 | d | pingwo |
| 13 | | |
| 14 | c | |
+----+------+----------+
13 rows in set (0.00 sec)
root@localhost: 01:51 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(1000,1000);
Query OK, 1 row affected, 2 warnings (0.00 sec)
root@localhost: 01:52 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
| 8 | a | beijin |
| 9 | b | chengdu |
| 10 | c | jianyang |
| 11 | d | pingwo |
| 13 | | |
| 14 | c | |
| 15 | d | |
+----+------+----------+
14 rows in set (0.00 sec)
root@localhost: 01:52 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(10000,10000);
Query OK, 1 row affected, 2 warnings (0.01 sec)
root@localhost: 01:52 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
| 8 | a | beijin |
| 9 | b | chengdu |
| 10 | c | jianyang |
| 11 | d | pingwo |
| 13 | | |
| 14 | c | |
| 15 | d | |
| 16 | | |
+----+------+----------+
15 rows in set (0.00 sec)
root@localhost: 01:52 [7308][db_hlf]>
root@localhost: 01:53 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(10,10);
Query OK, 1 row affected, 1 warning (0.01 sec)
root@localhost: 01:54 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(100,100);
Query OK, 1 row affected, 2 warnings (0.00 sec)
root@localhost: 01:54 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(1000,1000);
Query OK, 1 row affected, 2 warnings (0.00 sec)
root@localhost: 01:54 [7308][db_hlf]>select * from tbl_setenum;
+----+------+----------+
| id | setc | enumc |
+----+------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | NULL | NULL |
| 7 | | |
| 8 | a | beijin |
| 9 | b | chengdu |
| 10 | c | jianyang |
| 11 | d | pingwo |
| 13 | | |
| 14 | c | |
| 15 | d | |
| 16 | | |
| 17 | | |
| 18 | b,d | |
| 19 | c | |
| 20 | d | |
+----+------+----------+
19 rows in set (0.00 sec)
root@localhost: 01:54 [7308][db_hlf]>
再次验证set是索引二进制位想加
root@localhost: 02:16 [7308][db_hlf]>truncate table tbl_setenum;
Query OK, 0 rows affected (0.05 sec)
root@localhost: 02:17 [7308][db_hlf]>insert into tbl_setenum(setc,enumc)values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(null,null),(0,0);
Query OK, 12 rows affected, 7 warnings (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 7
root@localhost: 02:18 [7308][db_hlf]>show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'enumc' at row 5 |
| Warning | 1265 | Data truncated for column 'enumc' at row 6 |
| Warning | 1265 | Data truncated for column 'enumc' at row 7 |
| Warning | 1265 | Data truncated for column 'enumc' at row 8 |
| Warning | 1265 | Data truncated for column 'enumc' at row 9 |
| Warning | 1265 | Data truncated for column 'enumc' at row 10 |
| Warning | 1265 | Data truncated for column 'enumc' at row 12 |
+---------+------+---------------------------------------------+
7 rows in set (0.00 sec)
root@localhost: 02:18 [7308][db_hlf]>select * from tbl_setenum;
+----+-------+----------+
| id | setc | enumc |
+----+-------+----------+
| 1 | a | beijin |
| 2 | b | chengdu |
| 3 | a,b | jianyang |
| 4 | c | pingwo |
| 5 | a,c | |
| 6 | b,c | |
| 7 | a,b,c | |
| 8 | d | |
| 9 | a,d | |
| 10 | b,d | |
| 11 | NULL | NULL |
| 12 | | |
+----+-------+----------+
12 rows in set (0.00 sec)
root@localhost: 02:18 [7308][db_hlf]>
