1

I am writing a script to insert values from a file into a mysql database. The script reads lines from the file and constructs insert queries from them. Sometimes particular columns are empty, so an insert query might look like this...

insert into table (col1, col2, col3, col4) values('abc',100,,'def')

where col3 allows nulls, and also has NULL as its default value.

This insert query throws an error, while if I ran it in MS SQL it would work, and insert null where the insert query has nothing between the commas.

Is it possible to acheive this behaviour in mysql?

I tried doing a replace of ,, with ,NULL, but this fails when there are two empty values in a row - ,,, (I end up with ,NULL,,) and also adds extra processing (and therefore extra time taken) to the code. If there is a way to allow insert queries like insert into table (col,col,col) values(val,,val) I could avoid having to replace nothing values with 'NULL' when constructing the insert query string.


Edit:

For convenience I include below the code I have written to work around this problem. My code is in a windows script file (vbscript) (not tagged due to it not being directly relevant to the question being asked, but I'll tag it if it should be)

So here is code to replace the 'nothings' with NULL...

row = split(arfile.readline,"|")

for i = 0 to ubound(row)

    row(i) = rtrim(row(i))
    if row(i) = "" then row(i) = "NULL"

next

Then further down I have a line that combines them all into an insert query (actually it's more complex, it creates 100 insert rows and then executes the command and then creates another 100)

As part of the construction of the query, some values get single quotes added, because they are string or other types that need it. For that reason, wherever there has been a NULL replacement it becomes 'NULL' so I need to also deal with this...

conn.execute( insertquerystart & replace(insertquery,"'NULL'","NULL") & ";")

closed as off-topic by ypercubeᵀᴹ, dezso, mustaccio, RolandoMySQLDBA, Erik Mar 20 '16 at 11:56

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, dezso, mustaccio, RolandoMySQLDBA, Erik
If this question can be reworded to fit the rules in the help center, please edit the question.

2

No, it's not possible.

It would be interesting to see how your script looks like. Maybe you should consider writing the default value ('null') when writing the script? Or do you receive it as it is?

  • Thanks. I was afraid of that. I have written code the iterates through the array (that's produced by splitting the pipe delimited line) and replaces "" with "NULL". And also some code that replaces "'NULL'" with "NULL" to deal with string types. Later I'll add the code to my question. – MrVimes Mar 19 '16 at 19:44
1

Another alternative is to not include the column that would be NULL in the list of columns. E.g.

Insert Into T1(C1, C2, C3) Values('A', 'B', 'C')

And if C2 is NULL:

Insert Into T1(C1, C3) Values('D', 'E')

This doesn't fix your performance problems, but when it comes to that... how many rows are you inserting?

1

You can use the default keyword instead of null:

create table foo (
  id int not null AUTO_INCREMENT,
  name varchar(255),

  primary key (id)
);

insert into foo values (default, 'john doe');

select * from foo; -- result 1, 'john doe'

If that's not the problem, you need a real CSV program to help you import data into your db.

  • Thanks but that leaves me with the same problem - having to insert the word 'default' wherever my source file's value is nothing (a blank value) – MrVimes Mar 19 '16 at 19:42
  • @MrVimes what format is this file in? CSV? – Neil McGuigan Mar 19 '16 at 19:50
  • Pipe delimited. | – MrVimes Mar 19 '16 at 19:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.