MySQL returns all rows with an empty string as a where clause

Question

I found this problem while debugging an active CodeIgniter entry, as shown below:

$this->db->from("table_name"); $this->db->where("field_name", ""); $result = $this->db->get()->result_array(); 

Summary request:

 SELECT * FROM `table_name` WHERE `field_name` = 0; // Returns all rows in table 

Even if an empty row is cast to 0, we expect an empty result, since table_name . field_name filled with non-empty string values. However, I get the entire table from this query. Does anyone understand why? This is not intuitive.

I tried the request without translating to 0, and it works:

 SELECT * FROM `table_name` WHERE `field_name` = ""; // Empty result 

Why cast to 0?

---

EDIT: The same cast to 0 happens with this alternative CodeIgniter syntax:

 $this->db->query('SELECT * FROM table_name WHERE field_name = ?', array("")); 

Answer 1

Try the following if you are using codeigniter :

 $this->db->where("field_name = ''");