CODEWITHSUNDEEP
mysqlenums
Ramalingam Perumal
Ramalingam Perumal

Reputation: 1427

Connot select rows without double quote using ENUM variable type in MySQL

How to select rows without using the double quotes("1"). I am design the status column in enum type.

status
enum('0', '1')

My Query: 

 SELECT * FROM `user` WHERE activation_key='123456' AND status="1";

Result : 

 Display 1 row

I am Try :

 SELECT * FROM `user` WHERE activation_key='123456' AND status=1;

Result :

Display 0 row

Is it possible to get the data without double or single quotes in status column? I am a beginner of MYSQL, Sorry for my bad question!

Upvotes: 0

Views: 603

Answers (1)

undefined_variable
undefined_variable

Reputation: 6218

An enumeration value must be a quoted string literal. And to query a string you must enclose it in quotes.

If you make enumeration values that look like numbers, it is easy to mix up the literal values with their internal index numbers.

numbers ENUM('0','1','2')

If you store 2, it is interpreted as an index value, and becomes '1' (the value with index 2). If you store '2', it matches an enumeration value, so it is stored as '2'. If you store '3', it does not match any enumeration value, so it is treated as an index and becomes '2' (the value with index 3).

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

More details ENUM in MySQL

Upvotes: 1

Related Questions