Mysql - How do I enter NULL?

Setting NULL in graphical tools

The command-line interface is direct, but the UI of tools like MySQL Workbench or MySQL Query Browser makes it a duck soup to work with NULL values.

MySQL Workbench

To feed NULL values into fields in MySQL Workbench, right-click a cell and select "Set Field(s) to NULL". On encountering any UI glitches, know that recent updates have offered workarounds. Don't forget to execute your UPDATE to confirm changes, including NULL values.

MySQL Query Browser

In MySQL Query Browser, the NULL keyword can't be directly typed in. Instead, use SHIFT + DEL or right-click and select "Clear Field Content". To avoid any unnecessary confusions, ensure that you're not in edit mode.

Watch-outs and alternatives

NULL vs. empty strings

In your scripts, avoid mistaking NULL with an empty string or a string written as 'NULL'. They're not the same, as NULL signifies the non-existence of a value, while an empty string is a value, albeit empty.

NULL with placeholders

When using placeholders in an INSERT statement, make sure the bound value is actually NULL in your programming language so that it interprets correctly in your SQL.

Updating NULL in multiple rows

If you're UPDATING multiple rows to NULL, remember it can't be undone. So, ensure your WHERE clause conditions are precise.

/** Nullifying everyone's age after a wild new year party */
UPDATE your_table SET column_name = NULL WHERE some_condition = true;


Living with NULL: Visualisation

Think of inserting a ‘NULL’ as leaving a placeholder in an apartment.

Apartment: [🛋️, 🚪, 🛏️, *__NULL__*, 🍳]

A NULL could signify an empty room awaiting its belongings.

Furnished (VALUE): [🛋️, 🚪, 🛏️, 🖼️, 🍳]
Unfurnished (NULL): [🛋️, 🚪, 🛏️, 🚫, 🍳]

SQL helps you leave that room empty explicitly.

/** We're telling them to leave the study empty, for knowledge spares nobody */
INSERT INTO Apartments (LivingRoom, Entrance, Bedroom, Study, Kitchen)
VALUES ('Sofa', 'Door', 'Bed', NULL, 'Stove');


Handling NULL in SQL operations

NULL Checks

Use the IS NULL and IS NOT NULL operations when needing to check for NULL values as traditional comparison operations do not play well with NULL.

Aggregate Functions

NULL behaves differently with aggregate functions like SUM(), AVG(), and COUNT(). For instance, COUNT(column_name) wouldn't count the rows where column_name is NULL.

Table Design

Design your tables smartly. Decide on columns that should accept NULL and ones that shouldn't. Use NOT NULL constraints to enforce rules.

Additional pointers

Bulk insertions

For bulk insertions, use NULL to skip columns that allow NULL values, assuming you've defined default values or auto-incremented them.

Exporting and importing data

When exporting data, NULL is often represented as \N or an empty field, varying by the tool. When importing, ensure this representation matches to avoid discrepancies.