Empty string in not-null column in MySQL?

By : davr
Source: Stackoverflow.com
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.

By : davr


Answers

While 0 and empty strings are variables NULL is the absence of data. And trust me, it's a lot easier to write a query to

SELECT * from table where mything IS NULL than to try to query for empty strings :/



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:p[email protected]/dbase", $options);
By : ahhon


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.

By : Anthony


This video can help you solving your question :)
By: admin