SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for setenum -- ---------------------------- DROP TABLE IF EXISTS `setenum`; CREATE TABLE `setenum` ( `id` int(11) NOT NULL AUTO_INCREMENT, `settype` set('we','周','李','孙','钱','赵') DEFAULT NULL, `enumtype` enum('ZZZ','南海','长江','黄河') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of setenum -- ---------------------------- INSERT INTO `setenum` VALUES ('1', 'we,周,钱', '南海'); INSERT INTO `setenum` VALUES ('2', '钱,赵', '黄河'); INSERT INTO `setenum` VALUES ('3', 'we,赵', '南海'); INSERT INTO `setenum` VALUES ('4', '李,孙,钱', '长江'); set('we','周','李','孙','钱','赵')=111111=63 enum('ZZZ','南海','长江','黄河')=100=4 如下表所示:
SET类型:低位(右) → 高位(左) |
we |
周 |
李 |
孙 |
钱 |
赵 |
1 |
1 |
1 |
1 |
1 |
1 |
从右到左排:111111=63 |
|
|
|
|
|
|
we |
周 |
李 |
孙 |
钱 |
赵 |
1 |
1 |
0 |
0 |
1 |
0 |
从右到左排:010011=19 |
|
|
|
|
|
|
we |
周 |
李 |
孙 |
钱 |
赵 |
0 |
0 |
0 |
0 |
1 |
1 |
从右到左排:110000=48 |
|
|
|
|
|
|
we |
周 |
李 |
孙 |
钱 |
赵 |
1 |
0 |
0 |
0 |
0 |
1 |
从右到左排:100001=33 |
|
|
|
|
|
|
we |
周 |
李 |
孙 |
钱 |
赵 |
0 |
0 |
1 |
1 |
1 |
0 |
从右到左排:011100=28 |
ENUM类型 |
ZZZ |
南海 |
长江 |
黄河 |
1 |
2 |
3 |
4 |
|
|
|
|
ZZZ |
南海 |
长江 |
黄河 |
1 |
2 |
3 |
4 |
黄河=4=100 |
|
|
|
|
ZZZ |
南海 |
长江 |
黄河 |
1 |
2 |
3 |
4 |
长江=3=11 |
|
|
|
|
ZZZ |
南海 |
长江 |
黄河 |
1 |
2 |
3 |
4 |
南海=2=10 |
|
|
|
|
ZZZ |
南海 |
长江 |
黄河 |
1 |
2 |
3 |
4 |
ZZZ=1=1 |
mysql> select * from setenum; +----+----------+----------+ | id | settype | enumtype | +----+----------+----------+ | 1 | we,周,钱 | 南海 | | 2 | 钱,赵 | 黄河 | | 3 | we,赵 | 南海 | | 4 | 李,孙,钱 | 长江 | +----+----------+----------+ 4 rows in set mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum; +----------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,钱 | 19 | 10011 | 南海 | 2 | 10 | | 钱,赵 | 48 | 110000 | 黄河 | 4 | 100 | | we,赵 | 33 | 100001 | 南海 | 2 | 10 | | 李,孙,钱 | 28 | 11100 | 长江 | 3 | 11 | +----------+-----------+----------------+----------+------------+-----------------+ 4 rows in set mysql> select * from setenum where settype=33; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ | 3 | we,赵 | 南海 | +----+---------+----------+ 1 row in set mysql> select * from setenum where enumtype=2; +----+----------+----------+ | id | settype | enumtype | +----+----------+----------+ | 1 | we,周,钱 | 南海 | | 3 | we,赵 | 南海 | +----+----------+----------+ 2 rows in set --不支持二进制查询 mysql> select * from setenum where settype=b'010011'; Empty set mysql> select * from setenum where settype=b'10011'; Empty set mysql> select * from setenum where enumtype=b'100'; Empty set mysql> SELECT * FROM setenum WHERE settype LIKE '%赵%'; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ | 2 | 钱,赵 | 黄河 | | 3 | we,赵 | 南海 | +----+---------+----------+ 2 rows in set --与FIND_IN_SET函数同 mysql> SELECT * FROM setenum WHERE FIND_IN_SET('赵',settype)>0; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ | 2 | 钱,赵 | 黄河 | | 3 | we,赵 | 南海 | +----+---------+----------+ 2 rows in set --当查询只是集合某个值的一部分时,与FIND_IN_SET函数不同 mysql> SELECT * FROM setenum WHERE FIND_IN_SET('e',settype)>0; Empty set mysql> SELECT * FROM setenum WHERE settype LIKE '%e%'; +----+----------+----------+ | id | settype | enumtype | +----+----------+----------+ | 1 | we,周,钱 | 南海 | | 3 | we,赵 | 南海 | +----+----------+----------+ 2 rows in set mysql> SELECT * FROM setenum WHERE settype LIKE '赵'; Empty set mysql> SELECT * FROM setenum WHERE settype = '赵'; Empty set mysql> SELECT * FROM setenum WHERE settype LIKE 'we,赵'; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ | 3 | we,赵 | 南海 | +----+---------+----------+ 1 row in set mysql> SELECT * FROM setenum WHERE settype = 'we,赵'; +----+---------+----------+ | id | settype | enumtype | +----+---------+----------+ | 3 | we,赵 | 南海 | +----+---------+----------+ 1 row in set --如果把集合的顺序改一下,照样无效 mysql> SELECT * FROM setenum WHERE settype LIKE '赵,we'; Empty set mysql> SELECT * FROM setenum WHERE settype = '赵,we'; Empty set mysql> SELECT * FROM setenum WHERE enumtype LIKE '%海%'; +----+----------+----------+ | id | settype | enumtype | +----+----------+----------+ | 1 | we,周,钱 | 南海 | | 3 | we,赵 | 南海 | +----+----------+----------+ 2 rows in set mysql> SELECT * FROM setenum WHERE enumtype = '南海'; +----+----------+----------+ | id | settype | enumtype | +----+----------+----------+ | 1 | we,周,钱 | 南海 | | 3 | we,赵 | 南海 | +----+----------+----------+ 2 rows in set mysql> SELECT * FROM setenum WHERE enumtype = '海'; Empty set --------------------UPDATE 语法-------------------- set('we','周','李','孙','钱','赵')=111111=63 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,钱 | 19 | 10011 | 南海 | 2 | 10 | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set mysql> update setenum set settype = 2 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | 周 | 2 | 10 | 南海 | 2 | 10 | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set --修改settype让其'we'、'周'、'李' 成员为真 mysql> update setenum set settype =settype|1|4|6 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 --|1|4|6 表示 二进制的OR运算 1 or 100 or 110 = 111 = 7 --实际与 1|6 结果是一样的:1 or 110 = 111 = 7
we |
周 |
李 |
孙 |
钱 |
赵 |
1 |
1 |
1 |
0 |
0 |
0 |
从右到左排:000111=7 |
mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,李 | 7 | 111 | 南海 | 2 | 10 | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set --实际与 1|6 结果是一样的:1 or 110 = 111 = 7 mysql> update setenum set settype =settype|1|6 where id=1; Query OK, 0 rows affected Rows matched: 1 Changed: 0 Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,李 | 7 | 111 | 南海 | 2 | 10 | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set --settype|1|6 的settype 可以省略,结果一样 mysql> update setenum set settype = 1|6 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +----------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +----------+-----------+----------------+----------+------------+-----------------+ | we,周,李 | 7 | 111 | 南海 | 2 | 10 | +----------+-----------+----------------+----------+------------+-----------------+ 1 row in set -- &表示与运算,1 and 110 = 0,注意0与NULL不同 mysql> update setenum set settype = 1 & 6 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | | 0 | 0 | 南海 | 2 | 10 | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set -- &表示与运算,~表示反运算,6=110,~6=001,1 and 001 = 1 mysql> update setenum set settype = null where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | NULL | NULL | NULL | 南海 | 2 | 10 | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set mysql> update setenum set settype = 1 & ~6 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | we | 1 | 1 | 南海 | 2 | 10 | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set -- 5 and ~1 = 101 and ~1 = 101 and 111110 = 100 = 4 mysql> update setenum set settype = null where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> update setenum set settype = 5 & ~1 where id=1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0
we |
周 |
李 |
孙 |
钱 |
赵 |
0 |
0 |
1 |
0 |
0 |
0 |
从右到左排:000100=4 |
mysql> select settype,settype+0,bin(settype+0),enumtype,enumtype+0,bin(enumtype+0) from setenum where id=1; +---------+-----------+----------------+----------+------------+-----------------+ | settype | settype+0 | bin(settype+0) | enumtype | enumtype+0 | bin(enumtype+0) | +---------+-----------+----------------+----------+------------+-----------------+ | 李 | 4 | 100 | 南海 | 2 | 10 | +---------+-----------+----------------+----------+------------+-----------------+ 1 row in set enum有优点。但个人觉得。。。缺点更多,客观的讲:优点主要是在建数据 库的时候就可以把一些值给规范好。缺点是。。enum不适合PHP。主要是PHP是弱类型,如:你insert into ..... set a= 1,你没法知道你是想 a= '1' 还是 a= 1(a='1'是插入值1,a=1是插入enum的第一个值,尤其php弱类型的,如果int的,很少有人在sql里加双引号。),这是PHP和mysql 在使用enum 最大的问题。所以。。安心点啦。干脆点直接tinyint。 单曲观点: 我觉得没什么优点,对数字型的enum,简直就是梦魇,boolean tinyint(1) 0,1 status tinyint(1) 1,2,3,4,5,6..tinyint欢淫你~~。如:audit_result enum(1,2,3),set audit_result = 1;...容易出现膘哥所说的混淆。 简单观点: 少用,一般都是用tinyint替代。 天枫观点: 我觉得除了状态直观 没什么优点,我一般直接int,tinyint([1or2or3]) 到底有啥区别?(后面会简单探讨下,这里面的1or2or3区别。) 中庸观点: a=1是插入enum的第一个值,尤其php弱类型的,如果int的,很少有人在sql里加双引号,基本上是不加引号的。 竖琴螺观点: 六种情况就:tinyint(1) -1,-2,1,2,3,4 上面各种观点重点集中在PHP这种弱类型语言对引号不重视,程序员不写容易引起插入的语句不是自己想要的结果的问题,容易出现int时没有用引号导致插入了新值而不是定的那个值: 表结构如下:
1
2
3
4
5
|
CREATE
TABLE
`enum2tinyint` (
`switchs` enum(
'none'
,
'success'
,
'fail'
,
'delete'
,
'skip'
,
'1'
)
DEFAULT
NULL
,
`switch` tinyint(1)
NOT
NULL
,
KEY
`switchs` (`switchs`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
|
正常带引号插入enum:
1
2
|
INSERT
INTO
`test`.`enum2tinyint` (`switchs`, `switch`)
VALUES
(
'1'
,
'1'
);
1 1
|
PHP的弱类型问题,特别是对int类型的情况,实践如下: 如果没有带引号插入enum字段后如下(是第一个值none):
1
2
|
INSERT
INTO
`test`.`enum2tinyint` (`switchs`, `switch`)
VALUES
(1,
'1'
);
none 1
|
如果没有带引号插入enum字段后如下(是第2个值success):
1
2
|
INSERT
INTO
`test`.`enum2tinyint` (`switchs`, `switch`)
VALUES
(2,
'1'
);
success 1
|
结论:要插入enum的值,字段必须加引号,不加引号当然是数字,数字就是key,不是value。 规劝: 1)enum是整型这样的错误很容易发生,尤其是php弱类型的,一般新来一个人,没注意enum类型,就会犯错。 2)数据库说明清楚的话,或者可选择的全是字符串的话,还没什么,但是里面有数字,难免有新手犯错,养成加引号的习惯很重要。 最终结论: 历史原因,要把enum改成tinyint程序改动太大了,用了的没必要改·~,以后新建的时候,尽量使用tinyint就好。 这种字段的重复内容过多的,索引建不建,关系不大,这种在mysql叫索引的势太低,其查询效果不太好:(英文是这么翻译的:https://justwinit.cn/post/1405/)。 ———————————————————————————————————————————————————————————————————— tinyint(1)和tinyint(4)一样的,都是-127到128或者0到256。unsigned属性全是正,和c语言unsigned int有点类似: tinyint(1) -128 ~ 127 tinyint(1) unsigned 0 ~ 255 为何表示的最大正数比最小负数的绝对值少1呢? 第一位是符号位,1表示负数 所以负数可以到128,正数只能到127 -128正127 如:0111 1111 正数 ,负数 1111 1111 提问: Mysql里int(1)和int(11)差别很大的,mysql的int,有个属性 ,UNSIGNED ZEROFILL 后面的ZEROFILL,就是有定义的位数不够则用零补齐对齐一下(这儿可能涉及到索引的性能罢):int, 长度(M)=5, 属性=UNSIGNED ZEROFILL(无符号,用0来填充位数),00001,00002。 但这个tinyint呢,tinyint(1)和tinyint(4)一样的? 看这文章后:http://www.jb51.net/article/53424.htm 我估计:mysql这块对一个字节不像int这种四个字节要进行对其前部分进行部分字节索引,如果说tinyint(1)和tinyint(4)是一样的表示范围情况下,但mysql对tinyint数字长度位数作了可设置的限定,从理论上说其存在硬盘里的字节是不一样才是合乎逻辑的,这块估计还是为了对字节进行对齐方便索引等,以提升效率为主罢,l如:ike keyword% 索引有效,如果统一对齐,索引效果会不会更好呢?我也只是猜测。 ———————————————————————————————————————————————————————————————————— 在网上找了下这个enum的mysql的大体实现如下: 一)enum的存储原理我仔细查看了下手册。是这样的: 在建立这个字段时,我们会给他规定一个范围比如enum('a','b','c'),这时mysql内部会建立一张hash结构的map表,类似:0000 -> a,0001 -> b,0002 -> c。 当我插入一条数据,此字段的值位a或b或c时,他存储在里面的不是这个字符,而是对应他的索引,也就是那个0000或0001或0002。 同样,enum在mysql手册上的说明: ENUM('value1','value2',...) 1或2个字节,取决于枚举值的个数(最多65,535个值) 除非enum的个数超过了一定数量,否则他所占的存储空间也总是1字节。 二)tinyint: 类型 字节 最小值 最大值 (带符号的/无符号的) (带符号的/无符号的) TINYINT 1 -128 127 他的最小存储所占空间也是1字节。 最后,Enum,既然要用它,就不必要使用什么0,1,2来代替实际的字符串了。甚至中文字符串。他并不会对数据库性能进行多余开销。因为对于它来说,你使用'0','1','2'和'张三','李四','王五'数据表所占的存储空间一样。但是考虑到我们实际应用时数据需要从db服务器回传到web app,所以在网络传输时,当然还是尽可能的传输小数据比较好。所以如果很在意这些,还是不用它好了。 |