Supabase数据查询 #

一、基础查询 #

1.1 查询所有数据 #

typescript 
// 查询所有列
const { data, error } = await supabase
  .from('users')
  .select('*')

// 查询指定列
const { data, error } = await supabase
  .from('users')
  .select('id, name, email')

// 查询并计数
const { data, error, count } = await supabase
  .from('users')
  .select('*', { count: 'exact' })

console.log(`Total users: ${count}`)

1.2 SQL查询 #

sql 
-- 查询所有
SELECT * FROM users;

-- 查询指定列
SELECT id, name, email FROM users;

-- 别名
SELECT name AS user_name, email AS user_email FROM users;

-- 去重
SELECT DISTINCT category FROM products;

-- 限制结果
SELECT * FROM users LIMIT 10;

二、条件过滤 #

2.1 等于过滤 #

typescript 
// 等于
const { data } = await supabase
  .from('users')
  .select('*')
  .eq('id', 1)

// 不等于
const { data } = await supabase
  .from('users')
  .select('*')
  .neq('status', 'deleted')

2.2 比较过滤 #

typescript 
// 大于
const { data } = await supabase
  .from('products')
  .select('*')
  .gt('price', 100)

// 大于等于
const { data } = await supabase
  .from('products')
  .select('*')
  .gte('price', 100)

// 小于
const { data } = await supabase
  .from('products')
  .select('*')
  .lt('price', 100)

// 小于等于
const { data } = await supabase
  .from('products')
  .select('*')
  .lte('price', 100)

2.3 范围过滤 #

typescript 
// 范围
const { data } = await supabase
  .from('products')
  .select('*')
  .gte('price', 50)
  .lte('price', 100)

// IN 查询
const { data } = await supabase
  .from('products')
  .select('*')
  .in('category', ['electronics', 'books'])

// NOT IN
const { data } = await supabase
  .from('products')
  .select('*')
  .not('category', 'in', '("deleted","archived")')

2.4 模糊匹配 #

typescript 
// LIKE 模糊匹配
const { data } = await supabase
  .from('users')
  .select('*')
  .like('name', '%John%')

// ILIKE 不区分大小写
const { data } = await supabase
  .from('users')
  .select('*')
  .ilike('name', '%john%')

// 前缀匹配
const { data } = await supabase
  .from('users')
  .select('*')
  .startsWith('email', 'admin')

// 后缀匹配
const { data } = await supabase
  .from('users')
  .select('*')
  .endsWith('email', '@gmail.com')

2.5 空值判断 #

typescript 
// IS NULL
const { data } = await supabase
  .from('users')
  .select('*')
  .is('deleted_at', null)

// IS NOT NULL
const { data } = await supabase
  .from('users')
  .select('*')
  .not('deleted_at', 'is', null)

2.6 数组过滤 #

typescript 
// 包含元素
const { data } = await supabase
  .from('posts')
  .select('*')
  .contains('tags', ['javascript'])

// 包含任意元素
const { data } = await supabase
  .from('posts')
  .select('*')
  .overlaps('tags', ['javascript', 'typescript'])

2.7 JSON过滤 #

typescript 
// JSON包含
const { data } = await supabase
  .from('products')
  .select('*')
  .contains('metadata', { featured: true })

// JSON路径
const { data } = await supabase
  .from('products')
  .select('*')
  .eq('metadata->>color', 'red')

三、排序 #

3.1 基础排序 #

typescript 
// 升序
const { data } = await supabase
  .from('products')
  .select('*')
  .order('price', { ascending: true })

// 降序
const { data } = await supabase
  .from('products')
  .select('*')
  .order('created_at', { ascending: false })

// 多字段排序
const { data } = await supabase
  .from('products')
  .select('*')
  .order('category', { ascending: true })
  .order('price', { ascending: false })

3.2 NULL值排序 #

typescript 
// NULL值排在最后
const { data } = await supabase
  .from('products')
  .select('*')
  .order('deleted_at', { ascending: true, nullsFirst: false })

// NULL值排在最前
const { data } = await supabase
  .from('products')
  .select('*')
  .order('deleted_at', { ascending: true, nullsFirst: true })

3.3 SQL排序 #

sql 
-- 升序
SELECT * FROM products ORDER BY price ASC;

-- 降序
SELECT * FROM products ORDER BY created_at DESC;

-- 多字段排序
SELECT * FROM products 
ORDER BY category ASC, price DESC;

-- NULL处理
SELECT * FROM products 
ORDER BY deleted_at NULLS LAST;

四、分页 #

4.1 LIMIT和OFFSET #

typescript 
// 限制结果数量
const { data } = await supabase
  .from('products')
  .select('*')
  .limit(10)

// 分页
const page = 2
const pageSize = 10
const { data } = await supabase
  .from('products')
  .select('*')
  .range((page - 1) * pageSize, page * pageSize - 1)

// 或使用limit和offset
const { data } = await supabase
  .from('products')
  .select('*')
  .limit(pageSize)
  .offset((page - 1) * pageSize)

4.2 游标分页 #

typescript 
// 基于ID的游标分页
async function getProducts(cursor?: number, limit = 10) {
  let query = supabase
    .from('products')
    .select('*')
    .order('id', { ascending: true })
    .limit(limit)

  if (cursor) {
    query = query.gt('id', cursor)
  }

  const { data, error } = await query
  
  const nextCursor = data?.length 
    ? data[data.length - 1].id 
    : null

  return { data, nextCursor }
}

4.3 分页计数 #

typescript 
// 获取总数和分页数据
const page = 1
const pageSize = 10

const { data, error, count } = await supabase
  .from('products')
  .select('*', { count: 'exact' })
  .range((page - 1) * pageSize, page * pageSize - 1)

const totalPages = Math.ceil((count || 0) / pageSize)

console.log({
  data,
  page,
  pageSize,
  total: count,
  totalPages
})

五、关联查询 #

5.1 一对一关联 #

typescript 
// 查询用户及其资料
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    email,
    profiles (
      bio,
      avatar_url
    )
  `)

// 结果
// [
//   {
//     id: 1,
//     name: 'John',
//     email: 'john@example.com',
//     profiles: {
//       bio: 'Developer',
//       avatar_url: '...'
//     }
//   }
// ]

5.2 一对多关联 #

typescript 
// 查询用户及其文章
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts (
      id,
      title,
      created_at
    )
  `)

// 限制关联数据数量
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts (count)
  `)

5.3 多层关联 #

typescript 
// 查询文章、作者和评论
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    author:users (
      id,
      name,
      email
    ),
    comments (
      id,
      content,
      created_at,
      user:users (
        id,
        name
      )
    )
  `)

5.4 关联过滤 #

typescript 
// 过滤关联数据
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts!inner (
      id,
      title
    )
  `)
  .eq('posts.published', true)

// 使用inner join
const { data } = await supabase
  .from('users')
  .select(`
    id,
    name,
    posts!inner (id, title)
  `)
  .is('posts.deleted_at', null)

5.5 SQL关联查询 #

sql 
-- INNER JOIN
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.author_id;

-- LEFT JOIN
SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.author_id;

-- 多表关联
SELECT 
    p.title,
    u.name AS author,
    c.content AS comment
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON p.id = c.post_id;

六、聚合查询 #

6.1 计数 #

typescript 
// 计数
const { count } = await supabase
  .from('users')
  .select('*', { count: 'exact', head: true })

// 分组计数
const { data } = await supabase
  .from('products')
  .select('category')

6.2 SQL聚合 #

sql 
-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 'active';

-- 分组计数
SELECT category, COUNT(*) 
FROM products 
GROUP BY category;

-- 求和
SELECT SUM(total) FROM orders;
SELECT user_id, SUM(total) 
FROM orders 
GROUP BY user_id;

-- 平均值
SELECT AVG(price) FROM products;

-- 最大最小值
SELECT MAX(price), MIN(price) FROM products;

-- HAVING过滤
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

6.3 使用RPC聚合 #

sql 
-- 创建聚合函数
CREATE OR REPLACE FUNCTION get_product_stats()
RETURNS JSON AS $$
DECLARE
    result JSON;
BEGIN
    SELECT json_build_object(
        'total', (SELECT COUNT(*) FROM products),
        'avg_price', (SELECT AVG(price) FROM products),
        'max_price', (SELECT MAX(price) FROM products),
        'min_price', (SELECT MIN(price) FROM products),
        'by_category', (
            SELECT json_agg(row_to_json(t))
            FROM (
                SELECT category, COUNT(*) as count, AVG(price) as avg_price
                FROM products
                GROUP BY category
            ) t
        )
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;
typescript 
// 调用聚合函数
const { data } = await supabase.rpc('get_product_stats')

七、复杂查询 #

7.1 子查询 #

sql 
-- 子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT author_id FROM posts);

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p 
    WHERE p.author_id = u.id
);

7.2 CTE (Common Table Expression) #

sql 
-- WITH子句
WITH active_users AS (
    SELECT * FROM users 
    WHERE last_login > NOW() - INTERVAL '30 days'
),
user_stats AS (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
)
SELECT 
    u.name,
    COALESCE(s.post_count, 0) as posts
FROM active_users u
LEFT JOIN user_stats s ON u.id = s.user_id;

7.3 窗口函数 #

sql 
-- 排名
SELECT 
    name,
    price,
    RANK() OVER (ORDER BY price DESC) as price_rank
FROM products;

-- 分区排名
SELECT 
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;

-- 移动平均
SELECT 
    created_at::DATE as date,
    amount,
    AVG(amount) OVER (
        ORDER BY created_at 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM orders;

八、全文搜索 #

8.1 基础全文搜索 #

sql 
-- 创建全文索引
CREATE INDEX idx_posts_search ON posts 
USING GIN (to_tsvector('english', title || ' ' || content));

-- 全文搜索
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content) 
    @@ to_tsquery('english', 'javascript & react');

-- 使用plainto_tsquery简化
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content)
    @@ plainto_tsquery('english', 'javascript react');

8.2 客户端全文搜索 #

typescript 
// 使用RPC进行全文搜索
const { data } = await supabase.rpc('search_posts', {
  query: 'javascript react'
})
sql 
-- 创建搜索函数
CREATE OR REPLACE FUNCTION search_posts(search_query TEXT)
RETURNS SETOF posts AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM posts
    WHERE to_tsvector('english', title || ' ' || content)
        @@ plainto_tsquery('english', search_query)
    ORDER BY ts_rank(
        to_tsvector('english', title || ' ' || content),
        plainto_tsquery('english', search_query)
    ) DESC;
END;
$$ LANGUAGE plpgsql;

九、查询优化 #

9.1 使用索引 #

sql 
-- 查看查询计划
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'electronics';

-- 创建索引
CREATE INDEX idx_products_category ON products(category);

-- 复合索引
CREATE INDEX idx_products_category_price ON products(category, price DESC);

9.2 优化建议 #

text 
查询优化建议
├── 只查询需要的列
├── 使用索引
├── 避免SELECT *
├── 合理使用LIMIT
├── 使用EXPLAIN分析
├── 避免N+1查询
└── 使用连接池

十、实战示例 #

10.1 产品列表查询 #

typescript 
interface ProductFilters {
  category?: string
  minPrice?: number
  maxPrice?: number
  search?: string
  sortBy?: 'price' | 'created_at' | 'name'
  sortOrder?: 'asc' | 'desc'
  page?: number
  pageSize?: number
}

async function getProducts(filters: ProductFilters) {
  const {
    category,
    minPrice,
    maxPrice,
    search,
    sortBy = 'created_at',
    sortOrder = 'desc',
    page = 1,
    pageSize = 20
  } = filters

  let query = supabase
    .from('products')
    .select('*', { count: 'exact' })

  // 应用过滤
  if (category) {
    query = query.eq('category', category)
  }
  if (minPrice !== undefined) {
    query = query.gte('price', minPrice)
  }
  if (maxPrice !== undefined) {
    query = query.lte('price', maxPrice)
  }
  if (search) {
    query = query.ilike('name', `%${search}%`)
  }

  // 排序
  query = query.order(sortBy, { 
    ascending: sortOrder === 'asc' 
  })

  // 分页
  const offset = (page - 1) * pageSize
  query = query.range(offset, offset + pageSize - 1)

  const { data, error, count } = await query

  return {
    data,
    error,
    pagination: {
      page,
      pageSize,
      total: count,
      totalPages: Math.ceil((count || 0) / pageSize)
    }
  }
}

10.2 用户活动统计 #

typescript 
async function getUserStats(userId: string) {
  const { data, error } = await supabase.rpc('get_user_stats', {
    user_id: userId
  })

  return data
}
sql 
CREATE OR REPLACE FUNCTION get_user_stats(user_id UUID)
RETURNS JSON AS $$
DECLARE
    result JSON;
BEGIN
    SELECT json_build_object(
        'posts_count', (SELECT COUNT(*) FROM posts WHERE author_id = user_id),
        'comments_count', (SELECT COUNT(*) FROM comments WHERE user_id = user_id),
        'total_views', (SELECT COALESCE(SUM(view_count), 0) FROM posts WHERE author_id = user_id),
        'recent_posts', (
            SELECT json_agg(row_to_json(t))
            FROM (
                SELECT id, title, created_at
                FROM posts
                WHERE author_id = user_id
                ORDER BY created_at DESC
                LIMIT 5
            ) t
        )
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

十一、总结 #

查询操作要点：

操作 方法
选择列 select(‘col1, col2’)
过滤 eq, neq, gt, gte, lt, lte, in, like
排序 order(‘column’)
分页 range(), limit(), offset()
关联 select(‘table1, table2(cols)’)
计数 select(‘*’, { count: ‘exact’ })

下一步，让我们学习数据更新！

最后更新：2026-03-28