MySQL: how to use COALESCE

问题:

Say I have the following table:

TABLE: product
===============================================================================
| product_id | language_id | name           | description                     |
===============================================================================
| 1          | 1           | Widget 1       | Really nice widget. Buy it now! |
-------------------------------------------------------------------------------
| 1          | 2           | Lorem  1       |                                 |
-------------------------------------------------------------------------------

How do I query this such that it tries to give me the name and description where language_id = 2, but fall back to language_id = 1 if the column contains a NULL?

In the above example, I should get Lorem 1 for name and Really nice widget. Buy it now! for description.

回答1:

How about this?

SET @pid := 1, @lid := 2;
SELECT 
    COALESCE(name,(
        SELECT name
        FROM product
        WHERE product_id = @pid AND description IS NOT NULL
        LIMIT 1
    )) name, 
    COALESCE(description,(
        SELECT description
        FROM product
        WHERE product_id = @pid AND description IS NOT NULL
        LIMIT 1
    )) description
FROM product
WHERE product_id = @pid 
    AND (language_id = @lid 
    OR language_id = 1)
ORDER BY language_id DESC
LIMIT 1;

where:

• @pid: current product id
• @lid: current language id
• Values for name and/or description could be null
• language_id = 2 item could not exist

回答2:

select name, description from product
where product_id = @pid
  and name is not null
  and description is not null
  and (language_id = @lang or language_id = 1)
order by language_id desc

where @pid is the current product id and @lang is the current language id.

The first row returned will contain the current name and description.

This assumes that the row language_id = 1 will NOT contain NULL in name or description.

回答3:

select p2.product_id
      ,coalesce(p2.name, p1.name, 'No name') as name
      ,coalesce(p2.description, p1.description, 'No description') as description
  from product p2
  left join product p1 on(
       p1.product_id = p2.product_id
   and p1.language_id = 1
  )
 where p2.product_id  = 1
   and p2.language_id = 2;

Edit1:
The above query assumes that the language=2 row exist but that the name/descr may be null.

Edit 2.
I just remembered someone asked a similar question recently. And then I discovered it was you. You need to separate the products from the translations. That is what is making this query hard to write. Thomas answer makes it easy to do what you want.

回答4:

Assumption: There is an entry for each product with language = 1 The code below is just simple sql to retrieve what you want.

Another topic is if you want the behaviour you requested.. because you can have mixed languages between 'name' and 'description'. I would design it differently, if one of the two fields is empty i would default back to the main language (1).

select p.product_id
      ,coalesce(pl.name, p.name, 'No name') as name
      ,coalesce(pl.description, p.description, 'No description') as description
  from product p
  left join product pl on ( pl.product_id = p.product_id and pl.language_id = :language_id)
  )
 where p.product_id  = :product_id and p.language_id = 1

回答5:

Here is an example of using with SQL Update:

Its like equivalent to Oracle's NVL. You can use it like below in a prepared statement using parameters

UPDATE
    tbl_cccustomerinfo
SET
    customerAddress = COALESCE(?,customerAddress),
    customerName =  COALESCE(?,customerName),
    description =  COALESCE(?,description)
WHERE
    contactNumber=?