# The SET Datatype

**This page analyzes the SET data type with some examples. This page assumes that Simple Lookup Assoc Arrays are understood.**

A SET is a collection of 0+ possible values (1+ if the column is required) where any number of these values can be enabled on a row at any given time.

MySQL explains sets pretty well in their docs: https://dev.mysql.com/doc/refman/5.7/en/set.html

## Some Notes about Sets

**Sets are case-insensitive and auto-trim.**You can use an**ARRAY**of set data or a**CSV**string of set data anywhere**interchangeably**. Set data will be automatically sorted and converted to the correct case as defined in the Column's PossibleValues from the data type definition.**LIMITS**- "A table can have**no more than 255**unique`ENUM`

anddefinitions. Columns with identical element lists are considered the same against this limt." - https://dev.mysql.com/doc/refman/5.7/en/limits-frm-file.html`SET`

**NULL/empty array/empty string**are all valid as long as the Column is not required in the Schema definitions.**Looking up NULL/empty**elements within a set- Null/empty string cannot sit alongside *any* valid set values.
**Ex:**A lookup assoc like:**['a','b','']**is**INVALID**and will throw an exception. - To match on an empty set along with other values,
**use an empty array instead of null or empty string: ['a','b',[]]** - To match on
***only***an empty set, you can use either empty array, empty string, or null:*$lookupAssoc = ["AND"=>['!=' => []]]**$lookupAssoc = ["AND"=>['!=' => null]]**$lookupAssoc = ["AND"=>['!=' => '']]*

- Null/empty string cannot sit alongside *any* valid set values.
**SET values should be UNIQUE**- no SET value should be a**subset**of another SET value.- Ex:
*['tree','apple tree']*is invalid because looking up*"LIKE %tree%"*in the DB will match for 'apple tree'. We avoid this altogether upfront and throw an exception if this case is found.

- Ex:

## Searching Sets - Some lookup assoc examples

#### Rows with both "a AND c" EXACTLY (no other elements can be in the set)

$lookupAssoc = "a,c" // (outer arrays default to "AND" and "=")

$lookupAssoc = ["AND" => ["a", "c"] //same thing. CSVs and Arrays can be used interchangeably

These are equivalent. Both internally yield: "...WHERE set = 'a,c'"**Note:** "...WHERE UserTypeSet = 'a' AND UserTypeSet = 'c'" -- has ZERO RESULTS ALWAYS in MySQL!! So we adjust this array to always mean the first case.

#### Rows with "ONLY EXACTLY a" OR "ONLY EXACTLY c" (no others elements can be in the set)

`$lookupAssoc = "OR"=>['a','c'] // (outer arrays default to "=")`

...WHERE set = 'a' OR set = 'c'

#### Rows with AT LEAST "a AND c" both (others can be in set)

$lookupAssoc = "LIKE"=>['a','c'] // equally valid

$lookupAssoc = "LIKE"=>'%a%,%c%' // equally valid. this has slight performance boost

...WHERE set LIKE '%a%' and set LIKE '%c%'

...WHERE set LIKE '%a%,%c%'; //this has slight performance boost

#### Rows with at LEAST 'a' OR 'c' (others can be in set)

`$lookupAssoc = "LIKE"=>["OR"=>['a','c'] `

...WHERE set LIKE '%a%' OR set LIKE '%c%';

#### NO ROWS containing sets of "ONLY EXACTLY a" OR "ONLY EXACTLY b" (no other elements)

`$lookupAssoc = ["AND"=>['!=' => ['a','b']]] // seems wrong, but remember that "!=" negates the results`

...SELECT * FROM `User` where (UserTypeSet != 'a' AND UserTypeSet != 'b') OR UserTypeSet IS NULL

#### NO ROWS containing sets of ONLY EXACTLY "a AND b" (no other elements)

$lookupAssoc = ["OR"=>['!=' => ['a','b']]] // seems wrong, but remember that "!=" negates the results

...SELECT * FROM `User` where UserTypeSet != 'a,b' OR UserTypeSet IS NULL**Note:** "ONLY EXACTLY a" AND "ONLY EXACTLY b" is NEVER TRUE. For example: "SELECT * FROM `User` where (UserTypeSet != 'a' OR UserTypeSet != 'b') OR UserTypeSet IS NULL" -- will filter NOTHING!! So we adjust this case accordingly....

#### NO ROWS containing EITHER 'a' OR 'b' anywhere in the set

`$lookupAssoc = ["AND"=>['NOT LIKE' => ['a','b']]] // seems wrong, but remember that "NOT LIKE" negates the results`

...SELECT * FROM `User` where (UserTypeSet not like '%a%' AND UserTypeSet not like '%b%') OR UserTypeSet IS NULL

#### NO ROWS containing BOTH "a AND b" anywhere in the set

$lookupAssoc = ["OR"=>['NOT LIKE' => ['a','b']]] // seems wrong, but remember that "NOT LIKE" negates the results

...SELECT * FROM `User` where (UserTypeSet not like '%a%' OR UserTypeSet not like '%b%') OR UserTypeSet IS NULL