MySQL ODBC Connector - Regarding NULL and '' (null string) treatment in MYSQL

Author

Regarding NULL and '' (null string) treatment in MYSQL

2005-03-30, 9:41 am

Hi,

MySQL treats NULL, '' (empty string) as different.=0D

I mean when I select from/insert into a table, its behaviour is
different. =0D

Select * from table1 where name=3D''; =0D

is different from=0D

Select * from table1 where name=3DNULL; =0D

Similarly

Insert into table1(name) values('') is different from=0D

Insert into table1(name) values(NULL) =0D

Please suggest me here, how to overcome this '' (null string)
problem.

Scenario:=0D

I have two tables, one is parent and one is child.

Child is referencing 3 fields in parent table.

Parent table records are empty. Now I am trying to insert into the child
table with '' (null string) values into these 3 fields (which are
referencing to the parent table). Actually insert into the child table
in this scenario should be successful, but I am not able to make it
success because of the problem described above ( Treatment of NULL, ''
are different).

Note: If the parent is not having any records, we can insert into the
child. BCS, in this case referential integrity won't work.=0D
=0D
Ps: In oracle, it is not the case it treats NULL,'' as same.

Please suggest me how to proceed here.

Thanks,
Narasimha

Confidentiality Notice=0D

The information contained in this electronic message and any attachments to=
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or=
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or=
Mailadmin@wipro.com immediately
and destroy all copies of this message and any attachments.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2005-03-30, 9:41 am

Narasimha,

In programming languages, a 'null string' is empty, but in ANSI SQL,
NULL means unknown, _not_ empty, so
in ANSI SQL, NULLs are never equal to anything, not even themselves: the
expressions NULL=NULL, NULL<>NULL and NULL='' all evaluate to NULL.

Microsoft SQL has an 'ansi_nulls' setting which turns off this feature.
With ansi_nulls turned off, NULL works as you want it to (thus breaking
a lot of traditional SQL code). Perhaps Oracle also has such a setting
too. MySQL doesn't.

Referential integrity (RI) is meant to work oppositely to your
description: absent a matching key value in the parent table, a child
row cannot be inserted. What you describe looks like what's often called
the 'zeroth row' workaround: create a parent row with an empty key
value, then add matching child rows. It wrecks RI. If you absolutely
must add child rows before adding the parent row, use empty values, not
NULLs, but most DBAs would insist that you to revise the design such
that empty parent key values are not permitted.

Peter Brawley
http://www.artfulsoftware.com

-----

lakshmi.narasimharao@wipro.com wrote:

>Hi,
>
> MySQL treats NULL, '' (empty string) as different.
>
>
> I mean when I select from/insert into a table, its behaviour is
>different.
>
>
> Select * from table1 where name='';
>
>
> is different from
>
>
> Select * from table1 where name=NULL;
>
>
> Similarly
>
> Insert into table1(name) values('') is different from
>
>
> Insert into table1(name) values(NULL)
>
>
> Please suggest me here, how to overcome this '' (null string)
>problem.
>
> Scenario:
>
>
> I have two tables, one is parent and one is child.
>
> Child is referencing 3 fields in parent table.
>
>Parent table records are empty. Now I am trying to insert into the child
>table with '' (null string) values into these 3 fields (which are
>referencing to the parent table). Actually insert into the child table
>in this scenario should be successful, but I am not able to make it
>success because of the problem described above ( Treatment of NULL, ''
>are different).
>
>Note: If the parent is not having any records, we can insert into the
>child. BCS, in this case referential integrity won't work.
>
>
>
>Ps: In oracle, it is not the case it treats NULL,'' as same.
>
>
>Please suggest me how to proceed here.
>
>
>Thanks,
>Narasimha
>
>
>
>Confidentiality Notice
>
>
>The information contained in this electronic message and any attachments to this message are intended
>for the exclusive use of the addressee(s) and may contain confidential or privileged information. If
>you are not the intended recipient, please notify the sender at Wipro or Mailadmin@wipro.com immediately
>and destroy all copies of this message and any attachments.
>
>
>

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 3/27/2005

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Shankar Unni

2005-03-30, 7:04 pm

Peter Brawley wrote:

> Perhaps Oracle also has such a setting too. MySQL doesn't.

As a matter of fact, Oracle goes the other way in that if you store ''
into a VARCHAR field, it actually stores a NULL there. But it's
inconsistent in that doesn't consider a NULL varchar column to be = ''
(a literal '', I mean; or even a PL/SQL variable assigned a '' value).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links