On 2/6/03 9:05 AM, "Doug Beyer" <[EMAIL PROTECTED]> wrote: > I attempted to search various places for my answer but with no luck. So I'm > posting here. > > > > I create the following table: > > create table t1 ( id varchar(5) not null, name varchar(5) not null ); > > > I insert the following row: > > insert into t1 ( id ) values ( "1234" ); > > > I do the following selects: > > select count(*) from t1 where name is null; --> Result = 0 > > select count(*) from t1 where name = ""; --> Result = 1 > > > Questions: > 1) Why did the insert succeed since the "name" field is not null and I didn't > provide a value? > 2) Why does MySql think it's correct to substitute an empty string for a > non-provided value? >
This was discussed recently on the list, but there is also an answer in the manual, but as it so often happens to my self I miss to look at the right place - thus here it his: You defined the column as NOT NULL and VARCHAR, then you inserted a NULL into that field, thus mysql DEFAULTs to an empty string. All together you get exactly what you asked for, if you don't like this behavior you need to script around it in your application layer. Best/h <http://www.mysql.com/doc/en/CREATE_TABLE.html> -----cited from above URL --------------------- # A DEFAULT value has to be a constant, it can not be a function or an expression. If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: * For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence. * For date and time types other than TIMESTAMP, the default is the appropriate zero value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See section 6.2.2 Date and Time Types. * For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value (if you haven't explicitly specified another default value with the DEFAULT directive). ________end of cite____________________________________ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
