4 Ways to Replace NULL with a Different Value in MySQL
MAY 31, 2018 / IAN
In MySQL, sometimes you don’t want NULL values to be returned as NULL. Sometimes you want NULL values to be returned with a different value, such as “N/A”, “Not Applicable”, “None”, or even the empty string “”.
Fortunately there are several ways to do this in MySQL.
Here are four:
The IFNULL() function
The COALESCE() function
The IF() function combined with the IS NULL (or IS NOT NULL) operator
The CASE expression combined with the IS NULL (or IS NOT NULL) operator
Examples of these options are below.
Sample Data
First, let’s grab some sample data:
USE Solutions;
SELECT TaskCode
From Tasks;
Result:
+----------+
| TaskCode |
+----------+
| gar123 |
| NULL |
| NULL |
| dog456 |
| NULL |
| cat789 |
+----------+
So we have three NULL values and three non-NULL values.
The IFNULL() Function
Given its name, this is probably the most obvious option for replacing NULL values in MySQL. This function is basically the equivalent of ISNULL() in SQL Server.
The IFNULL() function allows you to provide two arguments. The first argument is returned only if it is not NULL. If it is NULL, then the second argument is returned instead.
Here’s an example of using IFNULL() against our sample data set:
SELECT IFNULL(TaskCode, 'N/A') AS Result
FROM Tasks;
Result:
+--------+
| Result |
+--------+
| gar123 |
| N/A |
| N/A |
| dog456 |
| N/A |
| cat789 |
+--------+
Here, we simply replaced NULL values with N/A.
The COALESCE() Function
This function is similar to the IFNULL() function, but slightly different. This function adheres to the ANSI SQL standard, and it is widely deployed across various RDBMSs .
The way it works is, you provide as many arguments as you need. COALESCE() will then return the first non-NULL value in the list, or NULL if there are no non-NULL values.
Like this:
SELECT COALESCE(TaskCode, 'N/A') AS Result
FROM Tasks;
Result:
+--------+
| Result |
+--------+
| gar123 |
| N/A |
| N/A |
| dog456 |
| N/A |
| cat789 |
+--------+
So we get exactly the same result as previously.
However, the difference with this function is that, as mentioned, you can provide a list of arguments. The COALESCE() function will take whichever is the first non-NULL value.
So for example, we could add NULL as the first argument and place None before N/A and look what happens:
SELECT COALESCE(NULL, TaskCode, 'None', 'N/A') AS Result
FROM Tasks;
Result:
+--------+
| Result |
+--------+
| gar123 |
| None |
| None |
| dog456 |
| None |
| cat789 |
+--------+
It skipped the first NULL as expected, then it skipped any NULL values in the TaskCode column, before settling on None. The N/A value didn’t get used in this case because None came first and it’s a non-NULL value.
The IF() Function Combined with IS NULL/IS NOT NULL
The IS NULL and IS NOT NULL operators allow you to test for NULL values, and present a different value depending on the outcome.
We can use these operators inside the IF() function, so that non-NULL values are returned, and NULL values are replaced with a value of our choosing.
Example:
SELECT IF(TaskCode IS NOT NULL, TaskCode, 'N/A') AS Result
FROM Tasks;
Result:
+--------+
| Result |
+--------+
| gar123 |
| N/A |
| N/A |
| dog456 |
| N/A |
| cat789 |
+--------+
So the same result as with the IFNULL() and COALESCE() functions.
And of course, we could swap IS NOT NULL with IS NULL. If we do that, we would need to swap the subsequent arguments too:
SELECT IF(TaskCode IS NULL, 'N/A', TaskCode) AS Result
FROM Tasks;
The CASE Expression Combined with IS NULL/IS NOT NULL
Another way to do it is to use the CASE expression:
SELECT
CASE
WHEN TaskCode IS NOT NULL THEN TaskCode
ELSE 'N/A'
END AS Result
FROM Tasks;
Result:
+--------+
| Result |
+--------+
| gar123 |
| N/A |
| N/A |
| dog456 |
| N/A |
| cat789 |
+--------+
As with the previous example, this could be rewritten to use IS NULL instead of IS NOT NULL:
SELECT
CASE
WHEN TaskCode IS NULL THEN 'N/A'
ELSE TaskCode
END AS Result
FROM Tasks;
mysql
https://database.guide/4-ways-to-replace-null-with-a-different-value-in-mysql/
<iframe width="100%" height="3080" src="http://snipet.teknotit.com/index.php?embed=5c0dc2337ec23" type="text/html"></iframe>
Texte seul - Permalink - Snippet public posté le 10/12/2018
backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
ou
bunzip2 < db_filename.sql.bz2 | mysql -u root -p target_db_name
Different mysqldump group options:
–skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locksptions –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks
avec gzip
backup : mysqldump -u -p --quick [database_name] | gzip > database_name.gz
mysql
http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
<iframe width="100%" height="416" src="http://snipet.teknotit.com/index.php?embed=544abbfa04803" type="text/html"></iframe>
Texte seul - Permalink - Snippet public posté le 15/02/2020