|
Why SET datatype in MYSQL
Set datatype used to store more than one value from set of values in one data. It is just opposite to enum datatype. In enum datatype allows the user to store one value from set of values.
|
CREATE TABLE userinfo
(id int not null auto_increment primary key,
username varchar(20) not null,
languages set('c','cpp','java','mysql') not null); |
INSERT INTO userinfo(username,languages) values('John','java,c,cpp');
INSERT INTO userinfo(username,languages) values('Peter','mysql,c,mysql');
INSERT INTO userinfo(username,languages) values('Anthony','java,cpp'); |
|
SELECT ALL
SELECT * FROM userinfo;
|
id |
username |
languages |
|
1 |
John |
c,cpp,java |
|
2 |
Peter |
c,mysql |
|
3 |
Anthony |
cpp,java |
|
-- absolute value match
SELECT * FROM userinfo where languages = "c,mysql";
-- like comparision as like strings
SELECT * FROM userinfo where languages like "%c,cpp%";
-- single value lookup in set field
-- it returns position of value 1,2,3 and if not exist return 0
SELECT * FROM userinfo where FIND_IN_SET('cpp',languages) > 0; |
