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

Doesn't an empty set of quotes, "" do that?

Answer 2

I'm confused. It looks like you're using mysqli OO (from the tags and style), but the syntax is different than the manual on php.net, which says to do this instead:

$query = "INSERT INTO mytable SET somevarchar = ?";
$value = "";
$prepared = $db->prepare($query);
$prepared->bind_param("s", $value);
$result = $prepared->execute();

Answer 3

This sounds like a problem with the MDB2 API fumbling PHP's duck typing semantics. Because the empty string in PHP is equivalent to NULL, MDB2 is probably mis-treating it as such. The ideal solution would be to find a workaround for it within it's API, but I'm not overly familiar with it.

One thing that you should consider, though, is that an empty string in SQL is not a NULL value. You can insert them into rows declared 'NOT NULL' just fine:

mysql> CREATE TABLE tbl( row CHAR(128) NOT NULL );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tbl VALUES( 'not empty' ), ( '' );
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT row, row IS NULL FROM tbl;
+-----------+-------------+
| row       | row IS NULL |
+-----------+-------------+
| not empty |           0 | 
|           |           0 | 
+-----------+-------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO tbl VALUES( NULL );
ERROR 1048 (23000): Column 'row' cannot be null

If you're unable to find (or implement) a workaround in the MDB2 API, one hackish solution (though slightly better than the one you're currently using) might be to define a user variable for the empty string --

SET @EMPTY_STRING = "";
UPDATE tbl SET row=@EMPTY_STRING;

Finally, if you need to use the empty string in an INSERT statement but find yourself unable to, the default value for string types in MySQL is an empty string. So you could simply omit the column from INSERT statement and it would automatically get set to the empty string (provided the column has a NOT NULL constraint).

Answer 4

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.

Answer 5

I realize this question is pretty much answered and retired, but I found it while looking for answers to a similar situation and I can't resist throwing my hat in the ring.

Without knowing what the NULL/"" column relates to, I can't know how the true significance of an empty string. Does empty string mean something unto itself (like, if I convinced a judge to let me change my name to simply nothing, I would be really irritated if my name showed up on my Driver's License as NULL. My name would be !

However, the empty string (or blank, or the nothingness that is SOMETHING, not simply the lack of anything (like NULL)) could also simply just mean "NOT NULL" or "Nothing, but still not Null". You could even go the other direction and suggest that the absence of the value NULL makes it even LESS something than Null, cuz at least Null has a name you can say aloud!

My point is, that if the empty string is a direct representation of some data (like a name, or what I prefer be inserted between the numbers in my phone number, etc), then your options are either to argue until you're sore for the legitimate use of empty string or to use something that represents an empty string that isn't NULL (Like an ASCII control character or some unicode equivalent, a regex value of some kind, or, even worse, an arbitrary yet totally unused token, like: ◘

If the empty cell really just means NOT NULL, then you could think of some other way of expressing it. One silly and obvious way is the phrase "Not NULL". But I have a hunch that NULL means something like "Not part of this group at all" while the empty string means something like "this guy is cool, he just hasn't gotten his gang tattoos yet". In which case I would come up with a term/name/idea for this situation, like "default" or "rookie" or "Pending".

Now, if by some crazy chance you actually want empty string to represent that which is not even worthy of NULL, again, come up with a more significant symbol for that, such as "-1" or "SUPERNULL" or "UGLIES".

In the Indian Caste System, the lowest Caste are Shudra: Farmers and Laborers. Beneath this caste are the Dalit: "The Untouchables". They are not considered a lower caste, because setting them as the lowest caste would be considered a contamination of the entire system.

So don't call me crazy for thinking empty strings may be WORSE than NULL!

'Til next time.

Answer 6

I found the solution!

MDB2 converts empty strings to NULL because portability option MDB2_PORTABILITY_EMPTY_TO_NULL is on by default (thanks to Oracle which considers empty strings to be null).

Switch this options off when you connect to the database:

$options = array(
    'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL
);
$res= & MDB2::connect("mysql://user:password@server/dbase", $options);