Michael, Thanks for informing me on that one. I was not aware of that problem with multi-row inserts. For the sake of consistency with other DBMS's, I don't use those, and now I'm quite glad. In fact, this attribute of mySQL also applies to UPDATE statements, which allow the same columns to be set to null. It's further evidence that the database is not the place to be validating your data, despite the available syntax. Clients are who need to check the values.
Kevin > > This behavior is not specific to Load Data Infile, it is general to > mysql. You might want to take a look at the docs at > <http://www.mysql.com/doc/en/constraint_NOT_NULL.html>. The > first line > is, "To be able to support easy handling of non-transactional tables, > all fields in MySQL have default values." Mysql > automatically converts > missing or illegal values to column defaults, because in the > non-transactional case, it cannot roll back when you have an illegal > value in the the 300th row of a 500 row insert. > > Kevin's example is actually a special case. Consider: > > mysql> create table testnull (X varchar(10) not null); > Query OK, 0 rows affected (0.51 sec) > > mysql> insert into testnull values (null); > ERROR 1048: Column 'X' cannot be null > > mysql> insert into testnull values ('first'), (null), ('third'); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 1 > > mysql> select * from testnull; > +-------+ > | X | > +-------+ > | first | > | | > | third | > +-------+ > 3 rows in set (0.01 sec) > > As you can see, you only get an error in the single row insert case. > For multiple rows, the illegal NULL is replaced with the > column default > ('' in this case), as you see using LOAD DATA INFILE. > > > The short term solution is to validate the data in the input file > > before it gets imported. I was hoping there was an option > in Load Data > > Infile to prevent it from converting NULL's to '' or 0. I'd rather > > have invalid data rejected than converted. > > > > Mike > > The usual solution is the one you mention: Verify your data before > inserting or loading, rather than counting on mysql to reject > bad data. > Another option might be to initially define the columns to > allow NULL, > as there are NULLs in the data. You could then find the bad data by > searching for the NULLs: > > select * from my_table where should_not_be_null_col IS NULL; > > Then do whatever is appropriate. Of course, which way is easier will > depend on your situation. > > There is an alternative solution, but it may be overkill compared to > what you want. You can build a copy of mysql from source with the > -DDONT_USE_DEFAULT_FIELDS compile option. I have never tried > this, but > as I understand it, this will turn off defaults completely, yielding > errors for missing or illegal values. Note that I believe you cannot > even explicitly set a default for a column if you use this > option, but I > could be wrong. > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
