13 KiB
13 KiB
商城系统数据库增量升级指南
本目录包含多个数据库升级脚本，适用于不同的部署场景。请根据您的实际情况选择合适的脚本执行。
🔧 最新修复
PL/pgSQL 变量冲突修复
2024年最新修复：mock_data_insert.sql 中的变量命名冲突和空值问题
在 mock_data_insert.sql 中修复了三个重要问题：
- 变量命名冲突：将订单生成部分的变量
merchant_id重命名为selected_merchant_id - 订单商品价格空值：使用 COALESCE 函数处理SKU价格为空的情况，确保价格字段不为空
- 配送任务重复：使用 DISTINCT ON 和 NOT EXISTS 确保每个订单只创建一个配送任务
修复的错误类型：
ERROR: 42702: column reference "merchant_id" is ambiguousERROR: 23502: null value in column "price" violates not-null constraintERROR: 23505: duplicate key value violates unique constraint "ml_delivery_tasks_order_id_key"
详细信息请查看 VARIABLE_CONFLICT_FIX_REPORT.md
验证脚本
运行 verify_mock_data_fix.sql 可以验证修复效果和数据完整性
⚠️ 重要：角色字段统一升级
版本更新：用户角色字段已从 user_type (INTEGER) 统一为 role (TEXT)
为提高代码可读性和语义清晰度，我们将所有用户角色相关字段统一为 role 字段：
ak_users.role- TEXT 类型，值：'admin', 'merchant', 'customer', 'delivery', 'service'ml_user_profiles.role- TEXT 类型，值：'admin', 'merchant', 'customer', 'delivery', 'service'
角色字段快速迁移
如果您的数据库中仍有 user_type 字段，请运行以下脚本进行迁移：
psql -f quick_role_migration.sql
该脚本会：
- 安全地添加
role字段 - 将现有
user_type数据迁移到role字段 - 更新相关约束、索引、函数和视图
- 同步
ak_users和ml_user_profiles的角色字段
🔐 重要：Supabase Auth 用户创建
在执行任何数据库升级之前，必须先创建 Supabase Auth 用户！
第一步：创建 Supabase Auth 用户
方法一：自动化脚本（推荐）
# 1. 安装依赖
npm install @supabase/supabase-js
# 2. 设置环境变量
export SUPABASE_URL="https://your-project.supabase.co"
export SUPABASE_SERVICE_ROLE_KEY="your-service-role-key"
# 3. 运行创建脚本
node create_supabase_auth_users.js
方法二：Supabase Dashboard 手动创建
在 Dashboard → Authentication → Users 中创建以下测试用户：
- admin@mall.com (密码: Test123456!)
- merchant1@mall.com (密码: Test123456!)
- merchant2@mall.com (密码: Test123456!)
- customer1@mall.com (密码: Test123456!)
- customer2@mall.com (密码: Test123456!)
- customer3@mall.com (密码: Test123456!)
- driver1@mall.com (密码: Test123456!)
- driver2@mall.com (密码: Test123456!)
验证用户创建
\i create_supabase_auth_users.sql
<EFBFBD>📋 脚本清单
🔍 检查脚本
mall_database_check.sql- 数据库状态检查脚本- 分析现有数据库结构
- 检查缺失的表、字段、索引
- 生成个性化升级建议
🚀 升级脚本
-
mall_alter_upgrade.sql- 完整增量升级脚本- 创建商城核心表（如果不存在）
- 为 ak_users 表添加商城字段
- 创建索引、触发器、函数
- 插入基础配置数据
-
mall_fields_only_upgrade.sql- 仅字段升级脚本- 专门为已有表添加缺失字段
- 添加CID自增字段（SEO优化）
- 创建相应索引和约束
- 最小化修改，适用于生产环境
🔄 迁移脚本
quick_role_migration.sql- 角色字段快速迁移脚本- 将
user_type字段安全迁移为role字段 - 更新相关约束、索引、函数和视图
- 包含完整的回滚方案
- 将
role_field_unification.sql- 角色字段统一升级脚本（完整版）- 全面的角色字段统一方案
- 创建角色管理相关的辅助函数
- 数据一致性检查和修复
👥 用户和数据脚本
-
create_supabase_auth_users.sql- Supabase Auth 用户检查脚本- 检测Supabase环境
- 提供用户创建指导
- 验证Auth用户状态
-
create_supabase_auth_users.js- Node.js 用户批量创建脚本- 使用 Admin API 自动创建测试用户
- 自动处理已存在用户
- 详细日志输出
-
create_supabase_auth_users.js- Node.js 用户创建脚本- 使用Admin API批量创建测试用户
- 自动处理重复用户
- 详细的执行日志
-
mock_data_insert.sql- 模拟数据插入脚本
🎯 使用场景选择
场景一：Supabase 环境全新部署
# Supabase 环境完整部署流程
1. create_supabase_auth_users.js # (推荐) 使用Admin API创建Auth用户
# 或 create_supabase_auth_users.sql # 检查并指导创建Auth用户
2. mall_migration.sql # 创建所有表和结构
3. mall_seo_security.sql # SEO优化和安全策略
4. mock_data_insert.sql # (可选) 插入测试数据
场景二：现有数据库 + 缺少商城表
# 如果已有 ak_users 但缺少商城表
1. create_supabase_auth_users.js # (Supabase环境) 创建Auth用户
2. mall_database_check.sql # 检查数据库状态
3. mall_alter_upgrade.sql # 增量升级（推荐）
4. mall_seo_security.sql # SEO优化和安全策略
场景三：已有商城表 + 缺少字段/CID
# 如果已有商城表但缺少某些字段或CID
1. create_supabase_auth_users.js # (Supabase环境) 确保Auth用户存在
2. mall_database_check.sql # 检查数据库状态
3. mall_fields_only_upgrade.sql # 仅添加字段和CID（推荐）
场景四：非Supabase环境
# 如果使用标准PostgreSQL
1. mall_database_check.sql # 检查数据库状态
2. mall_alter_upgrade.sql # 或 mall_fields_only_upgrade.sql
3. mall_seo_security.sql # SEO优化和安全策略
4. mock_data_insert.sql # 模拟数据（会创建虚拟auth_id）
📖 详细使用步骤
🔐 第零步：创建Supabase Auth用户（Supabase环境必需）
如果您使用Supabase，必须先创建Auth用户，否则业务数据无法正确关联。
方法一：使用Node.js脚本（推荐）
# 1. 安装依赖
npm install @supabase/supabase-js
# 2. 设置环境变量
export SUPABASE_URL=https://your-project.supabase.co
export SUPABASE_SERVICE_ROLE_KEY=your-service-role-key
# 3. 运行脚本
node create_supabase_auth_users.js
方法二：使用Supabase Dashboard
# 1. 登录 https://supabase.com/dashboard
# 2. 进入您的项目 -> Authentication -> Users
# 3. 点击 "Add user" 创建以下测试用户：
测试用户列表（密码统一：Test123456!）：
📧 admin@mall.com (角色: 管理员)
📧 merchant1@mall.com (角色: 商家)
📧 merchant2@mall.com (角色: 商家)
📧 customer1@mall.com (角色: 消费者)
📧 customer2@mall.com (角色: 消费者)
📧 customer3@mall.com (角色: 消费者)
📧 driver1@mall.com (角色: 配送员)
📧 driver2@mall.com (角色: 配送员)
方法三：SQL检查脚本
-- 检查环境并获得创建指导
\i create_supabase_auth_users.sql
第一步：检查数据库状态
-- 在数据库中执行检查脚本
\i mall_database_check.sql
第二步：根据检查结果选择脚本
检查脚本会输出类似以下建议：
根据您的数据库状态分析：
• ak_users 表缺失字段数: 3
• 缺失商城核心表数: 5
推荐执行方案: 建议使用 mall_alter_upgrade.sql（完整升级脚本）
第三步：执行升级脚本
-- 根据建议执行相应脚本
\i mall_alter_upgrade.sql
-- 或
\i mall_fields_only_upgrade.sql
第四步：执行SEO优化（可选）
\i mall_seo_security.sql
🔧 脚本特性
安全特性
- ✅ 使用
IF NOT EXISTS检查，避免重复创建 - ✅ 使用
DO $$ ... END $$块进行条件检查 - ✅ 详细的日志输出，便于跟踪执行过程
- ✅ 事务安全，出错时自动回滚
兼容性
- ✅ PostgreSQL 12+
- ✅ Supabase 完全兼容
- ✅ 保持与现有数据的兼容性
- ✅ 复用 ak_users 表，新表使用 ml_ 前缀
📝 字段说明
ak_users 表新增字段
| 字段名 | 类型 | 默认值 | 说明 |
|---|---|---|---|
mall_status |
INTEGER | 1 | 商城状态 (1:正常 2:禁用) |
mall_type |
INTEGER | 1 | 用户类型 (1:消费者 2:商家 3:其他) |
total_orders |
INTEGER | 0 | 总订单数 |
total_spent |
DECIMAL | 0.00 | 总消费金额 |
user_level |
INTEGER | 1 | 用户等级 (1-10) |
points |
INTEGER | 0 | 用户积分 |
verified_status |
INTEGER | 0 | 认证状态 (0:未认证 1:已认证 2:失败) |
商城核心表
| 表名 | 说明 | CID字段 |
|---|---|---|
ml_user_profiles |
用户扩展信息 | ❌ |
ml_categories |
商品分类 | ✅ |
ml_brands |
品牌 | ✅ |
ml_products |
商品 | ✅ |
ml_shops |
店铺 | ✅ |
ml_orders |
订单 | ✅ |
⚠️ 注意事项
执行前准备
- 备份数据库 - 在生产环境执行前务必备份
- 测试环境验证 - 先在测试环境执行和验证
- 检查权限 - 确保有足够的数据库权限
- 停止应用 - 执行期间建议停止相关应用
生产环境建议
- 分步执行 - 可以分多次执行，每次执行一个脚本
- 监控日志 - 注意观察执行过程中的日志输出
- 验证结果 - 执行后检查表结构和数据完整性
- 回滚准备 - 准备回滚方案以防出现问题
🔄 回滚方案
如果需要回滚，可以执行以下操作：
-- 删除新增字段（谨慎操作）
ALTER TABLE public.ak_users DROP COLUMN IF EXISTS mall_status;
ALTER TABLE public.ak_users DROP COLUMN IF EXISTS mall_type;
-- ... 其他字段
-- 删除新建表（谨慎操作）
DROP TABLE IF EXISTS public.ml_shopping_cart CASCADE;
DROP TABLE IF EXISTS public.ml_orders CASCADE;
-- ... 其他表（注意依赖关系）
<EFBFBD> Supabase Auth 用户创建详细说明
为什么需要先创建 Auth 用户？
在 Supabase 环境中，ak_users.auth_id 字段需要关联真实的 auth.users.id。如果 Auth 用户不存在，模拟数据脚本会创建虚拟 UUID，导致用户无法正常登录。
创建方式对比
| 方式 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| Node.js 脚本 | 自动化，批量处理，错误处理完善 | 需要配置环境变量 | 开发环境，批量创建 |
| Dashboard 手动 | 直观，不需要代码 | 手动操作，容易出错 | 少量用户，生产环境 |
| Admin API | 灵活，可集成到应用 | 需要编程实现 | 自定义集成 |
环境变量配置
创建 .env 文件或设置系统环境变量：
# Supabase 项目 URL
SUPABASE_URL=https://your-project-id.supabase.co
# Service Role Key (在 Dashboard > Settings > API 中找到)
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
验证 Auth 用户创建成功
-- 查看所有测试用户
SELECT
id,
email,
email_confirmed_at IS NOT NULL as confirmed,
created_at,
user_metadata
FROM auth.users
WHERE email LIKE '%@mall.com'
ORDER BY email;
-- 检查 ak_users 关联状态
SELECT
u.email,
u.nickname,
u.user_type,
CASE
WHEN au.id IS NOT NULL THEN '✓ 已关联'
ELSE '✗ 未关联'
END as auth_status
FROM ak_users u
LEFT JOIN auth.users au ON u.auth_id = au.id
WHERE u.email LIKE '%@mall.com'
ORDER BY u.email;
常见问题解决
1. Service Role Key 权限不足
确保使用的是 Service Role Key，不是 anon key。
2. 用户已存在错误
脚本会自动处理已存在的用户，不会重复创建。
3. 邮箱验证问题
脚本设置 email_confirm: true，自动验证邮箱。
4. 密码策略不符合要求
默认密码 Test123456! 符合大多数密码策略，如需修改请在脚本中调整。
🔧 故障排除
Auth 用户创建失败
# 检查网络连接
curl -I https://your-project.supabase.co
# 验证 API Key
curl -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
https://your-project.supabase.co/auth/v1/admin/users
# 重新运行创建脚本
node create_supabase_auth_users.js
<EFBFBD>📞 技术支持
如遇问题，请：
- 检查数据库日志
- 确认PostgreSQL版本兼容性
- 验证执行权限
- 查看详细错误信息
- 确保 Supabase Auth 用户已正确创建
最后更新： 2024年12月 版本： v1.1 兼容性： PostgreSQL 12+, Supabase 新增： Supabase Auth 用户创建流程