Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 适配问题
There is a field of type int in a table, and the received data in that place is an empty value (not null). When inserted, it becomes 0, which does not meet business requirements. Now I want it to be inserted as null. How can this be achieved? Should I change the sql_mode?
Testing & Explanation:
How to insert a Null in the middle id field during such an insertion?
Wouldn’t it be better to change the data type?
Did you convert this in the program? If this field is not inserted, it will be null, right?
If it is a fixed column, add the following judgment:
LOAD DATA LOCAL INFILE 'example_data1.txt'
INTO TABLE ttt
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(@col1, col2, @col3, col4)
SET col1 = IF(@col1 = '', 11, @col1), col3 = IF(@col3 = '', NULL, @col3);
Either change the data type or check during the insert.
Yes, the initial feeling is that before inserting into the database, the null values in the data are being converted to 0 when loaded.
If it’s loaddata, you can test it as mentioned above. It will convert empty values to null, not to 0.
Yes, it looks like you can do it this way with LOAD DATA.
How is the SQL written in the program?
Is there a general way to handle the situation where loaddata imports many tables, and it’s uncertain which fields in which tables are null values?
Import data using loaddata
Currently, there is no good solution. You can first check which ones are available, and then piece together individual loaddata statements for each table.
There are many ways to solve this, but in financial scenarios, the requirements are quite strict. This is not allowed, and that is not allowed either. Currently, we can only use if statements under each loaddata.
Is the current issue resolved by using loaddata?
Currently, the first method: batch replace empty values between delimiters with \N in the txt file; the second method: like the previous user mentioned, add a logical judgment at the end of loaddata to convert empty values to null. These two methods are due to the inability to set the generation of upstream txt files and the inability to use other import methods besides loaddata.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: BraveChen
|
username: 
|
username: 
|
username: 
|
username: 
|
username: