how to use enum in mysql database

Question

i use enum in the table of mysql database

how can i get all stuff where his type = a , b,c

simply if i use varchar then i can use

SELECT * FROM stuff WHERE type IN ("a","b","c")

but in enum case it is not worked.

how can i get all stuff where type = a , b , c

note:type is used as enum not char or varchar.

Answer 1

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.

Answer 2

Use single quotes:

SELECT * FROM stuff WHERE type IN ('a', 'b', 'c');