MySQL insert multiple rows with some values missing

If you want the same behavior as if the column was omitted in the insert use default:

INSERT INTO tbl VALUES (1,default,default),(4,default,6)...

If you want empty values just use null:

INSERT INTO tbl VALUES (1,null,null),(4,null,6)...

In that case you can insert null instead of missing :

INSERT INTO tbl_name (a,b,c) VALUES(1,null,null),(4,null,6),(7,8,9);

This solution will work only if the target fields are nullable or it will throw error

First, about what you specified:

Note, table may contain data instead for "missing" values, and it should not be overwritten with "null" or else.

In this regard, if you use only 'INSERT` statements, then it doesn't matter if some data exists already in some form, because an 'INSERT' statement inserts (!) new records, doesn't update records.

More of it: let's say you have an id column as a primary key column. If you try to INSERT a record with an 'id' identical with an existing one, then an error will arise: "Duplicate key...".

Now, let's say id is not a primary key value. Then, when you try to INSERT a new record with an 'id' identical with an existing one, then the new record will be inserted as duplicate.

That said, you can use UPDATE in order to update existing records. In order to not overwrite existing values, you can just omit them in UPDATE statement.

Example: table users with columns id, fname, lname:

id   fname   lname
1    John    Smith
2    Sam     Stevenson

UPDATE statement:

UPDATE users
SET fname='Helen'
WHERE id = 2;

Results:

id   fname   lname
1    John    Smith
2    Helen   Stevenson