Java 如何在mysql中插入NULL尤其是INT数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18449708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert NULL in mysql especially INT dataType
提问by Eric
I have 80000 recodes that are need to insert into database especially in table: temp(ts, temp) temp is INT.
我有 80000 个需要插入数据库的重新编码，尤其是在表中：temp(ts, temp) temp 是 INT。
The problem is almost 20000 recodes are null, so I am wondering how to insert NULL into DB when dataType is INT.
问题是几乎 20000 次重新编码为空，所以我想知道如何在 dataType 为 INT 时将 NULL 插入到 DB 中。
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.
希望有人可以帮助我。谢谢你。
采纳答案by Mark Rotteveel
You should use a PreparedStatementand use setNull(int, int):
您应该使用 aPreparedStatement并使用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();
回答by Declan_K
You should explicitly CASTthe NULL as an INT
您应该明确CAST将 NULL 作为 INT
...VALUES(...CAST(NULL AS INT)
...VALUES(...CAST(NULL AS INT)
回答by buzzsawddog
It sounds like your are sending "null" in when you should be sending an int. Perhaps try something like
听起来您应该在发送 int 时发送“null”。也许尝试类似的东西
(val == null ? "" : val)
(val == null ? "" : val)
回答by Ed Gibbs
You should consider using prepared statements. If you do, you'll find information on how to deal with nulls hereand elsewhere.
您应该考虑使用准备好的语句。如果这样做，您将在此处和其他地方找到有关如何处理空值的信息。
If you're 100% sure your valvalue is clean and won't cause SQL Injection (rare, but possible), then the "built string" approach needs to explicitly use nullwhen defining the value:
如果您 100% 确定您的val值是干净的并且不会导致 SQL 注入（很少见，但可能），那么null在定义值时需要显式使用“内置字符串”方法：
String sql = "INSERT INTO temp (val) VALUES (";
if (val == null) {
sql += "null";
} else {
sql += val;
}
sql += ")";
回答by Eric
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).
基本上，如果将 null 插入数据库，只需插入 table(val) value(NULL)。
回答by Xithias
As an alternative to Mark Rotteveel's answer, you can also use PreparedStatement.setObject(int, Object, int).
作为 Mark Rotteveel 答案的替代方案，您还可以使用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.
您指定 SQL 类型（第三个参数），如果对象为空，它会自动插入一个空值。它更快更容易。
String sql = "INSERT INTO temp(val) VALUES (?)";
PreparedStatement st = con.prepareStatement(sql);
st.setObject(1, value, Types.INTEGER);
count = st.executeUpdate();
