mysql中set和enum枚举类型插入值和索引比较--笔记

标签：enumc set -- 7308 enum db hlf root localhost

set，enum的数据类型都是字符串类型的对象，其中set最多可以包含64个元素，并且可以任意取到集合中的元素。而enum则是只能取到集合中的木一个元素，最多包含65536个元素，也就是说set是多项选择，enum是单项选择。
set和enum枚举类型必须字符串不能是数字，同时需要避免要使用字符串类型的数字方式的字符串值，容易和索引值混淆。
下面创建数字方式字符串就是不好的方式

set和enum特点比较
相同点：
插入索引为0或超出索引就插入空值
插入null就是null值
都插入不存在的值就插入空值
不同点：
set的索引是二进制（多值就是单个值索引二进制位相加）
enum索引是十进制

1、
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元素索引
'' 0
'beijin' 1
'chengdu' 2
'jianyang' 3
'pingwo' 4
null null

######################################
测试详细过程

[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

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)]>create table tbl_enum(id int(1000) not null primary key,setc set(1,2,3,4),enumc enum(5,6,7,8));
ERROR 1046 (3D000): No database selected
root@localhost: 00:24 [7308][(none)]>
root@localhost: 00:25 [7308][(none)]>use db_hlf;
Database changed
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]>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]>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]>

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]>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]>

标签：enumc,set,--,7308,enum,db,hlf,root,localhost
From： https://www.cnblogs.com/hsjz-xinyuan/p/16772279.html

mysql中set和enum枚举类型插入值和索引比较--笔记

相关文章

赞助商

阅读排行