NULL and zero-length string

This is a copy from
http://forums.mysql.com/read.php?10,73797,73797#msg-73797.
Perhaps someone on this group will know the answer.

Given the following table:
CREATE TABLE foo(field VARCHAR(20) NOT NULL);

The following query *can not* be executed:
INSERT INTO foo VALUES(NULL);

That is the behaviour I expected. But why the following query *can* be
executed:
UPDATE foo SET field=3DNULL;

By executing the UPDATE query, fields in all rows become zero-length
strings. I would like the error to be thrown instead.

Is there a way to instruct MySQL, at run-time, to make UPDATE queries
strict, without implicit conversion?


I appreciate your answers.
=8Eeljko

Re: NULL and zero-length string

wrote in message
news:1141466973.782118.89830@u72g2000cwu.googlegroups.com...
> Is there a way to instruct MySQL, at run-time, to make UPDATE queries
> strict, without implicit conversion?

In MySQL versions prior to 5.0.2, it seems that there is no choice. A
string column defined as NOT NULL but without an explicit DEFAULT clause
uses the empty string '' as an implicit default value.

In MySQL 5.0.2 and later, you can set "strict mode" which has a number of
effects, but one of which is to disable implicit defaults.

SET GLOBAL SQL_MODE = 'STRICT_ALL_TABLES';

You can also set the sql_mode as a command-line option to mysqld, or in the
my.cnf configuration file.

For more information, see:
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.ht ml
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Regards,
Bill K.

Re: NULL and zero-length string

> In MySQL versions prior to 5.0.2, it seems that there is no choice. A
> string column defined as NOT NULL but without an explicit DEFAULT clause
> uses the empty string '' as an implicit default value.
>
> In MySQL 5.0.2 and later, you can set "strict mode" which has a number of
> effects, but one of which is to disable implicit defaults.
>
> SET GLOBAL SQL_MODE = 'STRICT_ALL_TABLES';
>
> You can also set the sql_mode as a command-line option to mysqld, or in the
> my.cnf configuration file.
>
> For more information, see:
> http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.ht ml
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

I am using MySQL 4.1 and therefore can not set SQL_MODE variable. I
guess I should find some workaround solution until the server is
upgraded.

When I UPDATEd such column, MySQL implicitly converted NULL value to
zero-length string. (I wish it has thrown an error instead) But when I
wanted to INSERT that row from one table to another (INSERT INTO...
SELECT .... FROM ....) error occured. I wish the same behaviour was on
both, the INSERT and the UPDATE query, and not the way it is. :)

Thank you for your reply.


Zeljko