SQL判断数据存不存在的正确做法(99%的人还在写错!)

select count(*) from users where email = 'test@example.com';

select count(*) from users where email = 'zhang@example.com';

-- ✅ 推荐写法
select exists (
    select 1 from users where email = 'test@example.com'
) as user_exists;

select exists (select 1 from users where email = 'test@example.com');
select exists (select * from users where email = 'test@example.com');
select exists (select email from users where email = 'test@example.com');

-- 检查邮箱是否已被注册
select exists (
    select 1 from users 
    where email = 'newuser@example.com'
) as email_taken;
-- 返回 1 表示已被占用，0 表示可以使用

-- 找出所有有过订单的用户
select u.id, u.name, u.email
from users u
where exists (
    select 1 from orders o 
    where o.user_id = u.id
);

-- 找出从来没下过单的用户
select u.id, u.name, u.email
from users u
where not exists (
    select 1 from orders o 
    where o.user_id = u.id
);

-- 假设用户表中有 50 万条记录
-- 使用 count(*) 的方式
select count(*) from users where city = '上海';
-- 执行时间：150ms（需要统计所有上海的用户）
-- 使用 exists 方式  
select exists (
    select 1 from users where city = '上海'
) as has_sh_users;
-- 执行时间：3ms（找到第一个就直接停止了）

package main
import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)
// 检查邮箱是否已存在
func checkemailexists(db *sql.db, email string) (bool, error) {
    var exists bool
    query := `
        select exists (
            select 1 from users 
            where email = ?
        )`
    err := db.queryrow(query, email).scan(&exists)
    if err != nil {
        return false, err
    }
    return exists, nil
}
func main() {
    // 连接数据库
    db, err := sql.open("mysql", "user:password@tcp(localhost:3306)/mydb")
    if err != nil {
        log.fatal(err)
    }
    defer db.close()
    // 检查邮箱是否存在
    email := "test@example.com"
    exists, err := checkemailexists(db, email)
    if err != nil {
        log.fatal(err)
    }
    if exists {
        fmt.printf("邮箱 %s 已被注册\n", email)
    } else {
        fmt.printf("邮箱 %s 可以使用\n", email)
    }
}

// 用户注册逻辑
func registeruser(db *sql.db, email, password string) error {
    // 1. 先检查邮箱是否已存在
    exists, err := checkemailexists(db, email)
    if err != nil {
        return fmt.errorf("检查邮箱失败: %v", err)
    }
    if exists {
        return fmt.errorf("邮箱 %s 已被注册", email)
    }
    // 2. 邮箱可用，执行注册逻辑
    _, err = db.exec(`
        insert into users (email, password, created_at) 
        values (?, ?, now())
    `, email, password)
    if err != nil {
        return fmt.errorf("注册失败: %v", err)
    }
    fmt.printf("用户 %s 注册成功！\n", email)
    return nil
}
// 检查用户是否有订单
func userhasorders(db *sql.db, userid int) (bool, error) {
    var hasorders bool
    query := `
        select exists (
            select 1 from orders 
            where user_id = ? and status != 'cancelled'
        )`
    err := db.queryrow(query, userid).scan(&hasorders)
    return hasorders, err
}
// 获取用户信息，同时检查是否为 vip
func getuserwithvipstatus(db *sql.db, userid int) error {
    type userinfo struct {
        id     int    `json:"id"`
        name   string `json:"name"`
        email  string `json:"email"`
        isvip  bool   `json:"is_vip"`
    }
    var user userinfo
    query := `
        select 
            u.id,
            u.name,
            u.email,
            exists (
                select 1 from memberships m
                where m.user_id = u.id 
                  and m.status = 'active' 
                  and m.expired_at > now()
            ) as is_vip
        from users u
        where u.id = ?`
    err := db.queryrow(query, userid).scan(
        &user.id, &user.name, &user.email, &user.isvip,
    )
    if err != nil {
        return err
    }
    fmt.printf("用户信息: %+v\n", user)
    return nil
}

-- 为了让 exists 查询更快，记得在经常查询的字段上建索引
create index idx_users_email on users(email);
create index idx_orders_user_id on orders(user_id);

-- 如果字段可能为 null，记得特殊处理
select exists (
    select 1 from users 
    where phone is not null 
      and phone = '13800138000'
) as phone_exists;

-- 没必要排序
select exists (
    select 1 from users 
    where city = '上海'
    order by created_at  -- 这个排序完全没啥卵用
);
-- 直接开撸
select exists (
    select 1 from users 
    where city = '上海'
);