MySQL returns all rows with empty string as where clause

Question

I discovered this problem while debugging CodeIgniter active record as shown below:

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

The resulting query is:

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

Even though the empty string is cast to 0, we expect an empty result since table_name.field_name is full of non-empty string values. However, I get the entire table from this query. Anyone understand why? This is not intuitive at all.

I tried the query without the cast to 0 and it works:

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

Why the 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 using following, if you are using codeigniter:

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