Does NOT NULL in mysql accept empty string

Question

I have two tables. One with int as primary key and other with a varchar primary key.

When an empty string is inserted ( "" ) i get programming error for int but successfully stores for varchar. How does this work?

Is there a way to make varchar fields not accept empty strings? I want them to give some database error which i can catch.

Yes. `varchar` means **Var**iable-length **char**acter data. — Phylogenesis, Nov 23 '16 at 13:31
Is there a way to make varchar fields not accept empty strings? — ns15, Nov 23 '16 at 13:36
That is a different question entirely, but typically you validate fields using `trigger`s. — Phylogenesis, Nov 23 '16 at 13:40
@shadow0359: Looks like there's already an answer for that: http://stackoverflow.com/questions/2514178/im-looking-for-a-constraint-to-prevent-the-insert-of-an-empty-string-in-mysql — David, Nov 23 '16 at 13:44

David · Answer 1 · 2016-11-23T13:41:59.007

When a empty string is inserted ( "" ) i get programming error for int

Because a string is not an int. And there's no way to automatically convert a non-numeric string to an int.

but successfully stores for varchar

Because a string is a varchar. Columns like CHAR and VARCHAR are for storing character data, which is what a string is.

None of these values are NULL. NULL is the absence of a value. An empty string is still a value.

score 1 · Answer 2 · answered Nov 23 '16 at 13:27

An empty string is different from null. (This is true in MySQL and in many programming languages as well.) Try inserting null into the primary key for the varchar. It should fail on the insert - just like the int.

As @David says above, the empty string cannot be converted to an int, and so MySQL chooses null instead.

score 1 · Accepted Answer · answered Nov 23 '16 at 13:28

1

An empty string is not NULL, in almost every database and by the ANSI definition.

It so happens that Oracle treats empty strings as NULL values, which is generally rather confusing. But that is an exception to the SQL language.

answered Nov 23 '16 at 13:28

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered Nov 23 '16 at 13:28

Not sure what you are trying to do, and we need to see the code to know what is the best way to fix this problem, but you can try this:

CASE WHEN IN_IntValue = '' THEN NULL ELSE IN_IntValue

In general, only Oracle as far as I know look at empty string as NULL. In MySQL they are two different things.

Does NOT NULL in mysql accept empty string

4 Answers4