mysql enum和set_mysql enum和set字段类型的使用

CREATE TABLE test_enum(

id TINYINT,

e enum('FISH','APPLE','DOG','HAMBURG') NOT NULL

);

INSERT INTO test_enum VALUES(1,'APPLE');

INSERT INTO test_enum VALUES(1,2); -- 2代表'APPLE'

因其他数据库不一定有这个字段类型，转移到其他数据库会比较麻烦！

CREATE TABLE test_set (

perms SET('CAN_READ','CAN_WRITE','CAN_DELETE') NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

INSERT INTO test_set VALUES('CAN_READ,CAN_WRITE');

INSERT INTO test_set VALUES('CAN_WRITE,CAN_DELETE');

SELECT * FROM test_set WHERE perms like '%READ%';

SELECT * FROM test_set WHERE FIND_IN_SET('CAN_READ',perms);

ALTER TABLE test_set MODIFY perms SET('CAN_READ','CAN_WRITE','CAN_DELETE','CAN_ALL');

SET列不能使用索引