CODEWITHSUNDEEP
mysqldatabase
DELETE me
DELETE me

Reputation:

How to use enum in mysql database

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.

Upvotes: 2

Views: 1398

Answers (2)

G__
G__

Reputation: 7111

Use single quotes:

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

Upvotes: 0

Marco Ceppi
Marco Ceppi

Reputation: 7722

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.

Upvotes: 2

Related Questions