How to check if value is in MySQL enum?

Question

I'm trying to write a stored procedure that will select true if the provided parameter is in an enum and false otherwise. This is my current attempt:

DROP PROCEDURE IF EXISTS is_type_of_fruit;

delimiter //
CREATE PROCEDURE is_type_of_fruit (fruit VARCHAR(20))
BEGIN
  DECLARE fruit_types ENUM('APPLE','GRAPE','ORANGE');
  SELECT fruit IN(fruit_types);
END//

CALL is_type_of_fruit('APPLE');

It appears that I can't use the standard IN syntax that I would typically use to check if an item was in a list of options. Is there a different strategy that I need to use for ENUM types?

Answer 1

Came across this very old question trying to do the same thing. Probably not a lot of use to the original poster but have added this answer for the next person along.

DELIMITER //

DROP PROCEDURE IF EXISTS is_type_of_fruit //
CREATE PROCEDURE is_type_of_fruit (fruit VARCHAR(20))
BEGIN
  DECLARE fruit_types ENUM('APPLE','GRAPE','ORANGE');

  SET fruit_types = fruit;

  SELECT IF(fruit_types = fruit, TRUE, FALSE);
END//

DELIMITER ;

mysql> call is_type_of_fruit('cabbage');
+--------------------------------------+
| IF(fruit_types = fruit, TRUE, FALSE) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> call is_type_of_fruit('orange');
+--------------------------------------+
| IF(fruit_types = fruit, TRUE, FALSE) |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

Should mention that this method is not strict mode friendly so you'll have to catch that error yourself if you want to use this.