Empty string in not-null column in MySQL?

Question

I used to use the standard mysql_connect(), mysql_query(), etc statements for doing MySQL stuff from PHP. Lately I've been switching over to using the wonderful MDB2 class. Along with it, I'm using prepared statements, so I don't have to worry about escaping my input and SQL injection attacks.

However, there's one problem I'm running into. I have a table with a few VARCHAR columns, that are specified as not-null (that is, do not allow NULL values). Using the old MySQL PHP commands, I could do things like this without any problem:

INSERT INTO mytable SET somevarchar = '';

Now, however, if I have a query like:

INSERT INTO mytable SET somevarchar = ?;

And then in PHP I have:

$value = "";
$prepared = $db->prepare($query, array('text'));
$result = $prepared->execute($value);

This will throw the error "null value violates not-null constraint"

As a temporary workaround, I check if $value is empty, and change it to " " (a single space), but that's a horrible hack and might cause other issues.

How am I supposed to insert empty strings with prepared statements, without it trying to instead insert a NULL?

EDIT: It's too big of a project to go through my entire codebase, find everywhere that uses an empty string "" and change it to use NULL instead. What I need to know is why standard MySQL queries treat "" and NULL as two separate things (as I think is correct), but prepared statements converts "" into NULL.

Note that "" and NULL are not the same thing. For Example, SELECT NULL = ""; returns NULL instead of 1 as you'd expect.

Answer 1

Thanks to some of the answers, I realized that the problem may be in the MDB2 API, and not in the PHP or MYSQL commands themselves. Sure enough, I found this in the MDB2 FAQ:

• Why do empty strings end up as NULL in the database? Why do I get an NULL not allowed in NOT NULL text fields eventhough the default value is ""?
  • The problem is that for some RDBMS (most noteably Oracle) an empty string is NULL. Therefore MDB2 provides a portability option to enforce the same behaviour on all RDBMS.
  • Since all portability options are enabled by default you will have to disable the feature if you dont want to have this behaviour: $mdb2->setOption('portability', MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL);

Thanks to everyone who provided thoughtful answers.