How to insert NULL in mysql especially INT dataTyp

问题:

I have 80000 recodes that are need to insert into database especially in table: temp(ts, temp) temp is INT.

The problem is almost 20000 recodes are null, so I am wondering how to insert NULL into DB when dataType is INT.

I tried this:

String val = null;

//insert(ts, val) into temp 
String sql = "INSERT INTO temp" + "(val)" + " VALUES" + "('" + val + "')";
Statement st = (Statement) conn.createStatement();
count  = st.executeUpdate(sql);

unfortunately insert is failure. Print out the exception message:

Incorrect integer value: 'null' for column 'val' at row 1"

Wish someone can help me with it. Thank you.

回答1:

You should use a PreparedStatement and use setNull(int, int):

String sql = "INSERT INTO temp(val) VALUES (?)";
PreparedStatement st = con.prepareStatement(sql);
if (/* int value is not null */) {
   st.setInt(1, value);
} else {
   set.setNull(1, Types.INTEGER);
}
count  = st.executeUpdate();

回答2:

As an alternative to Mark Rotteveel's answer, you can also use PreparedStatement.setObject(int, Object, int).

You specify the SQL Type (3rd Parameter), and if the object is null, it inserts a null automatically. It's faster and easier.

String sql = "INSERT INTO temp(val) VALUES (?)";
PreparedStatement st = con.prepareStatement(sql);
st.setObject(1, value, Types.INTEGER);
count  = st.executeUpdate();

回答3:

You should consider using prepared statements. If you do, you'll find information on how to deal with nulls here and elsewhere.

If you're 100% sure your val value is clean and won't cause SQL Injection (rare, but possible), then the "built string" approach needs to explicitly use null when defining the value:

String sql = "INSERT INTO temp (val) VALUES (";
if (val == null) {
  sql += "null";
} else {
  sql += val;
}
sql += ")";

回答4:

I have solved this problem. The codes update as following:

String sql= null;
if(val.isEmpty()){
System.out.println(val);
System.out.println("Insert ts: " + ts + " val: null");
sql= "INSERT INTO " + table + "(ts,val,pointId)" + " VALUES" + "(" + "'" + ts + "'" + ", " + "NULL" + " , " + "'" + pointId + "'" + ")";
}
else{
System.out.println("Insert ts: " + ts + " val: " + val);
sql= "INSERT INTO " + table + "(ts,val,pointId)" + " VALUES" + "(" + "'" + ts + "'" + ", " + "'" + val + "'" + ", " + "'" + pointId + "'" + ")";
}

Statement st = (Statement) conn.createStatement();  //create the instances of statement
count = st.executeUpdate(sql);

Basically, if insert null into database, just do insert into table(val) value(NULL).

回答5:

You should explicitly CAST the NULL as an INT

...VALUES(...CAST(NULL AS INT)

回答6:

It sounds like your are sending "null" in when you should be sending an int. Perhaps try something like

(val == null ? "" : val)