Summary of related knowledge points of null (IFNULL,COALESCE and NULLIF) in mysql 04/05 Update SLTechnology News&Howtos

Summary of related knowledge points of null (IFNULL,COALESCE and NULLIF) in mysql

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

This paper gives an example of the knowledge points related to null (IFNULL,COALESCE and NULLIF) in mysql. Share with you for your reference, the details are as follows:

In MySQL, the null value represents an unknown value, which is different from the 0 or empty string''and is not equal to itself.

If we compare a null value with another null value or any other value, the result is NULL, because a value that doesn't know what it is (NULL value) is compared with another value that doesn't know what it is (NULL value), and its value is of course a value that doesn't know what it is (NULL value).

However, we usually use null values to represent situations where data is missing, unknown or inapplicable. For example, the lead's phone number might be NULL and can be added later. So when we create a table, we can specify whether the column accepts null values by using the NOT NULL constraint. Next, let's create a leads table and take a detailed look at it:

CREATE TABLE leads (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, source VARCHAR (255) NOT NULL, email VARCHAR (100), phone VARCHAR (25))

We can see that id is the primary key column, it does not accept any null values, and then first_name,last_name and source columns use NOT NULL constraints, so no null values can be inserted in these columns, while email and phone columns can accept null values.

Therefore, we can use null values in the insert statement to specify data loss. For example, the following statement inserts a row into a line table. Because the phone number is missing, the null value is used:

INSERT INTO leads (first_name,last_name,source,email,phone) VALUE ('John','Doe','Web Search','john.doe@yiibai.com',NULL)

Because the default value for the email column is NULL, you can omit email in the INSERT statement as follows:

INSERT INTO leads (first_name,last_name,source,phone) VALUES ('Lily','Bush','Cold Calling',' 408-555-1234'), ('David','William','Web Search',' (408)-888-6789')

When done, if we want to set the value of the column to NULL, we can use the assignment operator (=). For example, to update David William's phone to NULL, use the following UPDATE statement:

UPDATE leads SET phone = NULLWHERE id = 3

However, if you use the order by clause to sort the result set in ascending order, MySQL thinks that the null value is lower than the other values, so it displays the null value first. The following query statements are sorted in ascending order of telephone number (phone):

SELECT * FROM leadsORDER BY phone

Execute the above query statement, and the result is as follows:

If you use ORDER BY DESC, the null value is displayed at the end of the result set:

SELECT * FROM leadsORDER BY phone DESC

Execute the above query statement, and the result is as follows:

If we want to test NULL in a query, we can use the IS NULL or IS NOT NULL operator in the where clause. For example, to obtain a lead who has not yet provided a phone number, use the IS NULL operator, as follows:

SELECT * FROM leadsWHERE phone IS NULL

Execute the above query statement, and the result is as follows:

We can also use the IS NOT operator to get all prospects who provide e-mail addresses:

SELECT * FROM leadsWHERE email IS NOT NULL

Execute the above query statement, and the result is as follows:

However, even if NULL is not equal to the two null values in the NULL,GROUP BY clause, look at the sql instance:

SELECT email, count (*) FROM leadsGROUP BY email

The query returns only two rows because the rows of its mailbox (email) column NULL are grouped into one row, and the result is as follows:

+-+-+ | email | count (*) | +-+-+ | NULL | 2 | | john.doe@yiibai.com | 1 | +-+-+ 2 rows in set

We need to know that when you use a unique constraint or UNIQUE index on a column, you can insert multiple null values in that column, in which case MySQL believes that null values are different. Next, we verify this by creating a UNIQUE index on the phone column:

CREATE UNIQUE INDEX idx_phone ON leads (phone)

Note here that if you use the BDB storage engine, mysql will assume that null values are equal, so we cannot insert multiple null values into columns with unique constraints.

Now that we know the pros and cons of null, let's take a look at what to do with it in mysql. Mysql provides a total of three functions, IFNULL,COALESCE and NULLIF.

Let's look at it separately. First, the IFNULL function accepts two parameters. If the IFNULL function is not NULL, the first argument is returned, otherwise the second argument is returned. For example, if it is not NULL, the following statement returns the phone number (phone), otherwise it returns Nmax An instead of NULL. Let's look at an example:

SELECT id, first_name, last_name, IFNULL (phone, 'Nmax A') phoneFROM leads

Execute the above query statement and get the following results:

+-+ | id | first_name | last_name | phone | +-+ | 1 | John | Doe | NambiA | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | NambiA | +-+ 3 rows in set |

When you're done, the COALESCE function takes a list of arguments and returns the first non-NULL parameter. For example, you can use the COALESCE function to display the contact information of a clue in the following order according to the priority of the information: phone, email, and NCMA. The following is an example:

SELECT id, first_name, last_name, COALESCE (phone, email,'N 'A') contactFROM leads

Execute the above query statement to get the following code:

+-+ | id | first_name | last_name | contact | +-+ | | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush | (408)-555-1234 | | 3 | David | William | NambiA | +-+ 3 rows in set

Finally, there is the NULLIF function, which takes two parameters. The NULLIF function returns NULL if the two arguments are equal. Otherwise, it returns the first parameter. The NULLIF function is useful when you have both NULL and empty string values in a column. For example, we mistakenly inserted the following line into the leads table:

INSERT INTO leads (first_name,last_name,source,email,phone) VALUE ('Thierry','Henry','Web Search','thierry.henry@yiibai.com','')

Because phone is an empty string:'', not NULL. So, if we want to get the potential customer's contact information, we end up with an empty phone instead of an email, as follows:

SELECT id, first_name, last_name, COALESCE (phone, email,'N 'A') contactFROM leads

Execute the above query statement to get the following code:

+-+ | id | first_name | last_name | contact | +-+ | 1 | John | Doe | john.doe@yiibai.com | | 2 | Lily | Bush |-555-1234 | 3 | David | William | Numbago | | 4 | Thierry | Henry | | +-+

If we want to solve this problem, we need to use the NULLIF function to compare the phone with the empty string (''). If it is equal, we return NULL, otherwise the phone number is returned:

SELECT id, first_name, last_name, COALESCE (NULLIF (phone,'), email, 'Nmax A') contactFROM leads

Execute the above query statement to get the following code:

All right, that's all for this record.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.