cfc/scripts/sync_kmdiary.sql

-- ============================================================
-- 搴风編鏃ヨ浜у搧鏁版嵁瀵煎叆鑴氭湰锛堜粎缁撴瀯鍙樻洿 + 鍒嗙被鍝佺墝锛?-- 鐢ㄦ硶锛歮ysql -h 192.168.16.251 -u zxyj -p zxyj < sync_kmdiary.sql
-- ============================================================

SET NAMES utf8mb4;

-- 娣诲姞瀛楁
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS price DECIMAL(10,2) DEFAULT 0 COMMENT '褰撳墠鍞环';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS original_price INT DEFAULT 0 COMMENT '鍘熶环锛堟暣鏁帮級';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS sold_num INT DEFAULT 0 COMMENT '宸插敭鏁伴噺';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS source_platform VARCHAR(50) DEFAULT '' COMMENT '鏉ユ簮骞冲彴';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS source_id VARCHAR(100) DEFAULT '' COMMENT '鏉ユ簮骞冲彴鍟嗗搧ID';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS source_url VARCHAR(500) DEFAULT '' COMMENT '鏉ユ簮骞冲彴鍟嗗搧閾炬帴';
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS product_images TEXT COMMENT '鍟嗗搧鍥剧墖URL锛圝SON鏁扮粍锛?;
ALTER TABLE nutrition_product ADD COLUMN IF NOT EXISTS spec VARCHAR(200) DEFAULT '' COMMENT '瑙勬牸';

CREATE INDEX IF NOT EXISTS idx_source ON nutrition_product(source_platform, source_id);

-- 鎻掑叆鍒嗙被
INSERT INTO nutrition_category (name, sort_order, create_time, update_time) VALUES
('鍏荤敓楗搧', 1, NOW(), NOW()),
('淇濆仴椋熷搧', 2, NOW(), NOW()),
('涓嵂鏉?, 3, NOW(), NOW()),
('鍏荤敓椋熸潗', 4, NOW(), NOW()),
('鍏荤敓闆堕', 5, NOW(), NOW()),
('缇庡涓姢', 6, NOW(), NOW()),
('鍋ュ悍鍣ㄦ', 7, NOW(), NOW()),
('鑽喅鐐栨堡', 8, NOW(), NOW()),
('澶栫敤鎶ょ悊', 9, NOW(), NOW()),
('鍏朵粬', 10, NOW(), NOW())
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

-- 鎻掑叆鍝佺墝
INSERT INTO nutrition_brand (name, create_time, update_time) VALUES
('搴风編鏃朵唬', NOW(), NOW()),
('绂忎笢娴?, NOW(), NOW()),
('璋辨礇鏃烘€?, NOW(), NOW()),
('搴风編', NOW(), NOW()),
('濞佹柉鍋?, NOW(), NOW()),
('浜戞暀鎺?, NOW(), NOW()),
('璁稿挨浣?, NOW(), NOW()),
('鑾辨€濋粠', NOW(), NOW()),
('椋熻喅鍏?, NOW(), NOW()),
('SKG', NOW(), NOW()),
('寤垮洓鍏?, NOW(), NOW()),
('涓栧噣', NOW(), NOW()),
('灞辫尪娑?, NOW(), NOW()),
('鐧戒簯灞?, NOW(), NOW()),
('绾冲瘜灞?, NOW(), NOW()),
('灏忕背鏈夊搧', NOW(), NOW()),
('濂戒綋鐭?, NOW(), NOW()),
('鍙傛辰婧愯悆', NOW(), NOW()),
('钘忓崈宀?, NOW(), NOW()),
('鍚変附鐗?, NOW(), NOW()),
('鍗冨勾鐝?, NOW(), NOW()),
('PVPV', NOW(), NOW())
ON DUPLICATE KEY UPDATE create_time = create_time;