Knowledge Walls
John Peter
Pune, Maharashtra, India
How to use set datatype in MYSQL? in Computer software engineer articles of One day One Thing to Know
3146 Views
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 with SET
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 with SET values
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
Filter by SET column
-- 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;
Best Lessons of "One day One Thing to Know"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details