I have used ENUM in a table of a MySQL database. How can I get all stuff where type = a, b, c?
Simply, if I use VARCHAR, then I can use:
SELECT * FROM stuff WHERE type IN ("a","b","c")
But using ENUM it does not work.
How can I get all stuff where type = a, b, c?
Note: Type is used as ENUM not CHAR or VARCHAR.
You can use FIELD like so:
SELECT * FROM `stuff` WHERE FIELD(`type`, "a", "b", "c");
That should be the proper syntax to apply the logic from your question. It all boils down to weird type casting issues with the way ENUM lists work - Ideally the best way to go is setup forign key relation ship with a table that houses something like:
TypesTbl
+---------------+
| AK | Value |
+---------------+
| 1 | a |
| 2 | b |
| 3 | c |
+---------------+
Stuff
+------------------+
| PK | * | type |
+------------------+
| 1 | | 1 |
| 2 | | 2 |
| 3 | | 1 |
| 4 | | 3 |
+------------------+
SELECT s.* FROM `Stuff` AS s LEFT JOIN `TypesTbl` t ON s.type = t.AK WHERE t.Value IN ('a', 'b', 'c');
That way you use a left join (or whichever fashion) in your query and you don't have to update the DDL just to add a new ENUMerated value. The first part of the answer I believe supports what you're trying to do. The second part was just additional information.
Use single quotes:
SELECT * FROM stuff WHERE type IN ('a', 'b', 'c');
