| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134 |
- SET NAMES utf8mb4;
- -- =========================================================
- -- shop_items 数据种子:从《生成商品的初始化的文档提示词.md》同步
- -- =========================================================
- -- 运行前提:
- -- 1) 先执行 `项目文档/chatbot_mysql5_建表.sql`,确保表 `shop_items` 存在
- -- 2) 该脚本设计为幂等:
- -- - 已存在的 item_code:更新字段
- -- - 不存在的 item_code:插入
- -- - 本次 seed 不包含的 item_code:is_active 置为 0(下架)
- START TRANSACTION;
- DROP TEMPORARY TABLE IF EXISTS `tmp_shop_items_seed`;
- CREATE TEMPORARY TABLE `tmp_shop_items_seed` (
- `item_code` VARCHAR(64) NOT NULL COMMENT '商品编码(业务唯一)',
- `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
- `name_zh` VARCHAR(100) NOT NULL COMMENT '商品中文名称',
- `name_en` VARCHAR(100) NOT NULL COMMENT '商品英文名称',
- `biz_type` ENUM('seat_sofa','table_cabinet','window','wallpaper','prop') NOT NULL COMMENT '业务分类',
- `item_type` ENUM('furniture','clothing','backdrop') NOT NULL COMMENT '商品类型',
- `placement_type` ENUM('wall','floor','backdrop','clothing') NOT NULL COMMENT '摆放/使用类型',
- `image_name` VARCHAR(255) NOT NULL COMMENT '图片名称',
- `size_rows` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '占用行数(家具用)',
- `size_cols` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '占用列数(家具用)',
- `price_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '能量石价格(保留2位小数)',
- `price_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '水晶石价格(保留2位小数)',
- `default_z_index` INT NOT NULL DEFAULT 0 COMMENT '默认渲染层级',
- `scene_anchor_type` ENUM('none','seat','window') NOT NULL DEFAULT 'none' COMMENT '场景锚点类型',
- `has_special_scene_trigger` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否有特殊触发场景(1=有,0=无)',
- `sort_order` INT NOT NULL DEFAULT 0 COMMENT '前端展示排序',
- PRIMARY KEY (`item_code`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- INSERT INTO `tmp_shop_items_seed`
- (`item_code`,`name`,`name_zh`,`name_en`,`biz_type`,`item_type`,`placement_type`,`image_name`,`size_rows`,`size_cols`,`price_energy`,`price_crystal`,`default_z_index`,`scene_anchor_type`,`has_special_scene_trigger`,`sort_order`)
- VALUES
- ('stump_stool','小木桩','小木桩','Stump Stool','seat_sofa','furniture','floor','Stump-Stool.png',1,1,0,0.00,10,'seat',0,10),
- ('forest_sofa','绿意沙发','绿意沙发','Forest Sofa','seat_sofa','furniture','floor','Forest-Sofa.png',1,1,150,0.00,10,'seat',0,20),
- ('vintage_table','复古小桌','复古小桌','Vintage Table','table_cabinet','furniture','floor','Vintage-Table.png',1,2,50,0.00,10,'none',0,30),
- ('stump_table','木桩小桌','木桩小桌','Stump Table','table_cabinet','furniture','floor','Stump-Table.png',1,2,150,0.00,10,'none',0,40),
- ('mushroom_table','蘑菇长桌','蘑菇长桌','Mushroom Table','table_cabinet','furniture','floor','Mushroom-Table.png',1,2,150,0.00,10,'none',0,50),
- ('french_window','落地窗','落地窗','French Window','window','furniture','wall','French-Window.png',2,3,0,0.00,20,'window',0,60),
- ('garden_balcony','花园阳台','花园阳台','Garden Balcony','window','furniture','wall','Garden-Balcony.png',2,3,150,0.00,20,'window',0,70),
- ('forest_round_window','绿意圆窗','绿意圆窗','Forest Round Window','window','furniture','wall','Forest-Window.png',2,3,150,0.00,20,'window',0,80),
- ('terra_cotta_window','红泥窗','红泥窗','Terra Cotta Window','window','furniture','wall','Terra-Cotta-Window.png',2,3,50,0.00,20,'window',0,90),
- ('fresh_green_wallpaper','清新绿','清新绿','Fresh Green Wallpaper','wallpaper','backdrop','backdrop','Fresh-Green.png',1,1,0,0.00,0,'none',0,100),
- ('midnight_blue_wallpaper','深夜蓝','深夜蓝','Midnight Blue Wallpaper','wallpaper','backdrop','backdrop','Midnight-Blue.png',1,1,50,0.00,0,'none',0,110),
- ('springtime_wallpaper','春之意','春之意','Springtime Wallpaper','wallpaper','backdrop','backdrop','Springtime.png',1,1,50,0.00,0,'none',0,120),
- ('mocha_wallpaper','摩卡浓情','摩卡浓情','Mocha Wallpaper','wallpaper','backdrop','backdrop','Mocha.png',1,1,50,0.00,0,'none',0,130),
- ('coffee_mug','咖啡杯','咖啡杯','Coffee Mug','prop','furniture','floor','Coffee-Mug.png',1,1,0,0.00,10,'none',1,140),
- ('snacks','零食','零食','Snacks','prop','furniture','floor','Snacks.png',1,1,0,0.00,10,'none',1,150),
- ('books','书','书','Books','prop','furniture','floor','Books.png',1,1,0,0.00,10,'none',1,160),
- ('energy_ball','能量球','能量球','Energy Ball','prop','furniture','floor','Energy-Ball.png',1,1,50,0.00,10,'none',0,170),
- ('chinese_new_year_bloom','年宵花','年宵花','Chinese New Year Bloom','prop','furniture','floor','Chinese-New-Year-Bloom.png',2,1,50,0.00,10,'none',0,180),
- ('photo_frame','普通相框','普通相框','Photo Frame','prop','furniture','wall','Photo-Frame.png',1,1,50,0.00,10,'none',0,190),
- ('chocolate_tree','巧克力树','巧克力树','Chocolate Tree','prop','furniture','floor','Chocolate-Tree.png',1,1,50,0.00,10,'none',0,200),
- ('lilybell_lamp','铃兰花灯','铃兰花灯','Lilybell Lamp','prop','furniture','floor','Lilybell-Lamp.png',2,1,50,0.00,10,'none',0,210),
- ('nut_wind_bell','坚果风铃','坚果风铃','Nut Wind Bell','prop','furniture','wall','Nut-Wind-Bell.png',2,1,50,0.00,10,'none',0,220);
- -- 更新已存在商品
- UPDATE `shop_items` s
- JOIN `tmp_shop_items_seed` t
- ON s.`item_code` = t.`item_code`
- SET
- s.`name` = t.`name`,
- s.`name_zh` = t.`name_zh`,
- s.`name_en` = t.`name_en`,
- s.`biz_type` = t.`biz_type`,
- s.`item_type` = t.`item_type`,
- s.`placement_type` = t.`placement_type`,
- s.`image_name` = t.`image_name`,
- s.`size_rows` = t.`size_rows`,
- s.`size_cols` = t.`size_cols`,
- s.`price_energy` = t.`price_energy`,
- s.`price_crystal` = t.`price_crystal`,
- s.`default_z_index` = t.`default_z_index`,
- s.`scene_anchor_type` = t.`scene_anchor_type`,
- s.`has_special_scene_trigger` = t.`has_special_scene_trigger`,
- s.`stock_mode` = 'unlimited',
- s.`repeat_purchase_mode` = 'single',
- s.`max_per_user` = 1,
- s.`is_active` = 1,
- s.`sort_order` = t.`sort_order`;
- -- 插入缺失商品
- INSERT INTO `shop_items`
- (`item_code`,`name`,`name_zh`,`name_en`,`biz_type`,`item_type`,`placement_type`,`image_name`,`size_rows`,`size_cols`,
- `price_energy`,`price_crystal`,`default_z_index`,`scene_anchor_type`,`has_special_scene_trigger`,`stock_mode`,`repeat_purchase_mode`,`max_per_user`,`is_active`,`sort_order`)
- SELECT
- t.`item_code`,
- t.`name`,
- t.`name_zh`,
- t.`name_en`,
- t.`biz_type`,
- t.`item_type`,
- t.`placement_type`,
- t.`image_name`,
- t.`size_rows`,
- t.`size_cols`,
- t.`price_energy`,
- t.`price_crystal`,
- t.`default_z_index`,
- t.`scene_anchor_type`,
- t.`has_special_scene_trigger`,
- 'unlimited',
- 'single',
- 1,
- 1,
- t.`sort_order`
- FROM `tmp_shop_items_seed` t
- WHERE NOT EXISTS (
- SELECT 1
- FROM `shop_items` s
- WHERE s.`item_code` = t.`item_code`
- );
- -- 下架非本次 seed 的旧商品
- UPDATE `shop_items` s
- SET s.`is_active` = 0
- WHERE NOT EXISTS (
- SELECT 1
- FROM `tmp_shop_items_seed` t
- WHERE t.`item_code` = s.`item_code`
- );
- COMMIT;
|