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 and SET definitions. 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
  • 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"=>['!=' => '']]
  • 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.

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