MySQL判断非空和非空函数

-- 创建一个测试表
create table customers (
  id int primary key,
  name varchar(50),
  age int
);

-- 插入示例数据
insert into customers (id, name, age) values 
(1, 'john doe', null),
(2, 'jane smith', 25),
(3, null, 30);

-- 判断字段是否为空
select name from customers where age is null;
select name from customers where age is not null;

-- 测试is null
select case 
  when (age is null) then 'age is null'
  else 'age is not null'
end as result from customers;

-- 测试is not null
select case 
  when (age is not null) then 'age is not null'
  else 'age is null'
end as result from customers;

-- 使用coalesce判断字段是否为空
select name from customers where coalesce(age, '') = '';

-- 返回第一个非null参数的值
select coalesce(null, 'hello', 'world'); -- 输出hello
select coalesce(null, null, null); -- 输出null

-- 使用coalesce函数进行非空判断
select case 
  when coalesce(age, '') = '' then 'age is null'
  else 'age is not null'
end as result from customers;

-- 使用ifnull函数进行非空处理
select ifnull(name, 'n/a') from customers;

-- 使用ifnull函数进行非空处理
select ifnull(name, 'n/a') from customers;

-- 使用nullif函数进行非空判断
select nullif(age, 0) from customers;

-- 使用nullif函数进行非空判断
select case 
  when nullif(age, 0) is null then 'age is null'
  else 'age is not null'
end as result from customers;