I am reading data from the 3rd party data export.
So in my database which read in the 3rd party data export, I could
1) set up the fields as allow null. So if the 3rd party data is null I read in them as null, if it is empty string I read in as empty string.
2) set up the fields as Not NULL DEFAULT '', so if the 3rd party data is null, I will read in them as emty string.
The problems are
Approach 1)
It is the better one in terms of the database integrity, but the data is a "NULL" or empty string makes no difference to our application in this case. As a matter of fact, in the 3rd party export, if there is no data in the same field, some of them will be "NULL" some of them will be empty string.
But in approach 1 due to these fields are set up as allow null, and then when I call the query to compare these data etc. There are so many simple queries I cannot use due to there are null value fields
Such as I cannot use
SELECT id FROM tb1
WHERE field1, field2, field3, field4 NOT IN
(SELECT field1, field2, field3, field4 FROM tb2)
...
Actually, that would be the most database queries I am going to do on these data.
In approach 2)
My database set all the fields to be NOT NULL with default value '' or default 0 etc.
So when I compare these tables, I would be able to use
SELECT id FROM tb1
WHERE field1, field2, field3, field4 NOT IN
(SELECT field1, field2, field3, field4 FROM tb2)
SELECT id FROM tb3
WHERE field1, field2, field3, field4 NOT IN
(SELECT field1, field2, field3, field4 FROM tb4)
...
And I can used many other queries that will not be allowed if there are null value fields.
But then when I try to call the queries
Such as
INSERT INTO tb1
(field1, field2, field3, field4)
SELECT ... FROM "3rd party data export"
Depends on mysql database version and its set up, if there are null values in the "3rd party data export", the insert query will be either successful, null value will be insert as empty string etc., or in other versions or set up, the insert query will fail and the error is "field1 not allow null value etc".
In simple words
approach 1) my database will have null value fields and I can NOT use many queries that not apply to null value fields.
approach 2) insert null value into the field that not allow null could be successful or could FAIL depends on mysql versions or set up. But if it is successful, it is exactly the data I want, I can use many simple queries which would not apply to null value fields.
in my current database set up, insert null value into the field that not allow null will be ok, the null value will be replaced with empty string. It works perfectly in my case. But it looks like it is due to my mysql version is mysql 4 instead of mysql 5.
My question is that is there a clean approach or set up in mysql5 etc. that would allow
Insert null value to the fields (not null default '')
In MOST other circumstances, I would not require this
Insert null value to the fields (not null default '')
But in this application, allow
Insert null value to the fields (not null default '')
Will make things easy and clean. And in this circumanstance, null or empty string really doesn't make difference due to the 3rd party will export them sometimes in null, sometimes as emty string.
Thanks!