How to Insert Null in MySQL Especially Int Datatype

How to insert NULL in mysql especially INT dataType

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();

Insert NULL value into INT column

If the column has the NOT NULL constraint then it won't be possible; but otherwise this is fine:

INSERT INTO MyTable(MyIntColumn) VALUES(NULL);

How to insert NULL value from nodeJS/javascript to MySQL using parameterized inputs?

Using 'mariadb/callback' as the connector to the database:

const db = require('mariadb/callback');

mariaDB is one option for a MySQL database.

Let's use the following as our example variables:

var customName = '    ';
var someAddress = 'this is an address';
var mainID = 'some unique ID';
var secondID = 'maybe not a unique ID';

Queries are parameterized as such:

var sql = `UPDATE myTable SET myName=?, myAddress=? WHERE _id=? AND otherID=?;`;

By using the '?' character instead of the variable name you not only simplify the SQL statement, this also, and more importantly, prevents SQL injection. Specifically, if one of the variables was storing this:

var id = '42 OR 1=1;'

The result of not using parameterized inputs in this case might cause the entire table to be updated with the same name and address. Using parameterized inputs will simply cause this operation to fail because none of the id's in the database will be '42 OR 1=1'.

Now, if customName is an empty string or whitespace, or even a 'NULL' string, you can do this:

customName = customName.trim();
if ( (customName === '') || (customName.toUpperCase() === 'NULL') ) {
  customName = null;
}

Which is the same as:

if ( (customName.trim() === '') || (customName.trim().toUpperCase() === 'NULL') ) {
  customName = null;
}

(the first version simply reassigns customName without any leading/trailing whitespace - make sure you don't need to preserve the value of the string when choosing this method)

.trim() will remove all whitespace from both ends of the string (it will not remove any whitespace between any non-whitespace characters).

.toUpperCase() will change the string to all uppercase characters, that way if the string is 'null' or 'Null' or any other variation that includes any lowercase characters it will still evaluate to 'true' when evaluating the 'NULL' equality. Note, it's important to use .trim() here as well, that way

'  nuLl  '

becomes

'NULL'

and still evaluates to 'true'.

Next, we need to create our array of values:

var values = [customName, someAddress, mainID, secondID];

Finally, the db query:

db.query(sql, values, (err, result) => {
  // callback stuff to do here
}

db refers to the connector created at the beginning of this post. I did not include the actual connection process because that is out of scope for this post. For reference, the documentation for the API is here.

AFTER the db is connected, db.query is used to execute the query. The first argument 'sql' is the parameterized SQL statement. The second argument 'values' is the array of values that will be used in the query. This array MUST be in the same order as they will be used.

As an example of what not to do:

var sql = `UPDATE myTable SET myName=${customName}, myAddress=${someAddress} WHERE _id=${mainID} AND otherID=${secondID};`;

The reason this is bad is because of SQL injection, which works like this:
You start by doing this:

var customName = 'same name';
var mainID = '" OR ""="';
var secondID = '" OR ""="';

The resulting sql code will be:

var sql = `UPDATE myTable SET myName="same name", myAddress="this is an address" WHERE _id="" OR ""="" AND otherID="" OR ""="";`;

And the result is now every single entry in the database now has the same name and same address.

This SQL injection example is modified from W3 schools.

Mysql int will not store non zero based null.

I'm on my mobile phone so I can't test this... Also this is too long to be a comment... first thing try selecting the rows in question.. Then if they arent returned try updating a select when the rows are equal to 0.

Maybe it is null but you have a setting in mysql workbench that shows a 0 instead

SELECT id, field1 
FROM table 
WHERE field1 IS NULL;

If that doesn't return the rows then do this

SELECT id, field1
FROM table
WHERE field1 =0

If that does then try an update with these exact rows

UPDATE table t,
(   SELECT id, field1
    FROM table
    WHERE field1 = 0
) temp
SET t.field1 = NULL
WHERE t.id = temp.id

Hope that is helpful

How to have a null value for MySQL in Python

You should use the None value in python to represent the NULL mysql value you are attempting to insert. Like so:

sql_data = ('R', ept_id, ept_type, ept_ch, base_date, None);

Here is a reference to another post with this problem: https://bytes.com/topic/python/answers/166025-python-mysql-insert-null

As well as this post: How can I insert NULL data into MySQL database with Python?

How to insert Null Value in database using teradatasql on a SMALLINT, Numeric overflow occurred during computation

As Fred suggested, use the function pandas.notnull to replace NaN to None

Initially when you insert a None object to pandas, it replaces it with NaN. If you want to insert into a database you need to convert those NaN to None. This is because the database will think you are inserting a string, especially on integer data type fields. Below is the code to convert NaN to any specific value. In my scenario it should be None.

cNoneDtframe = parsedDtframe.where((pandas.notnull(parsedDtframe)), None)

How to insert null value to int column in MySQL using python 2.7

try the following, replace your none with null

sql="""insert into db.table_name (int_column, column2) values (null, 'test')"""
cursor.execute(sql)

---

Related Topics