SQL同步数据操作

//查询表所有列
SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'lilishop_goods' AND TABLE_NAME = 'li_goods_sku';


//商品品类同步
insert into li_category(id, commission_rate, image, `level`, `name` , parent_id,sort_order) 
select category_id, commission_rate, image, `level`, category_name, pid,sort from goods_category;



//商品sku同步
INSERT INTO li_goods_sku (
id, delete_flag, big, small, brand_id, buy_count, 
category_path, comment_num, cost, goods_id, goods_name,
store_id, store_name,sn,specs,goods_type,
is_green_goods,green_score, quantity,weight, price) 
SELECT
sku_id, 0 delete_flag, sku_image,sku_image, 0 brand_id, 0 buy_count,
goods_attr_class ,0 comment_num,cost_price, goods_id, goods_name, 
site_id,site_name ,sku_no, CONCAT("{'images':","[{'url':'", sku_image ,"'}]}")  specs,goods_class,
is_green,give_green,stock,weight/1000 weight,price
from goods_sku where is_delete = 0;



//新商品sku同步
-- 无结束括号
INSERT INTO li_goods_sku (
id, delete_flag, big, small, brand_id, buy_count, 
category_path, comment_num, cost, goods_id, goods_name,
store_id, store_name,sn,specs,json_val ,  goods_type,
is_green_goods,green_score, quantity,weight, price, create_by) 
SELECT
sku_id, 0 delete_flag, sku_image,sku_image, 0 brand_id, 0 buy_count,
goods_attr_class ,0 comment_num,cost_price, goods_id, goods_name, 
site_id,site_name ,sku_no,case when JSON_VALID(sku_spec_format) then 
CONCAT(
"{'images':","[{'url':'", sku_image ,"'}]",",",
case when JSON_VALID(sku_spec_format->> '$[0].spec_name') then JSON_EXTRACT(sku_spec_format,'$[0].spec_name') else  JSON_EXTRACT(sku_spec_format,'$[0].spec_name') end, ":", 
case when JSON_VALID(sku_spec_format->> '$[0].spec_value_name') then JSON_EXTRACT(sku_spec_format,'$[0].spec_value_name') else  JSON_EXTRACT(sku_spec_format,'$[0].spec_value_name') end)
else null end
specs, sku_spec_format json_val , goods_class,
is_green,give_green,stock,weight/1000 weight,price, -1 create_by
from goods_sku where is_delete = 0;


update li_goods_sku_copy1 set specs = CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[1].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[1].spec_name') is not null, JSON_EXTRACT(json_val,'$[1].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[1].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[1].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[1].spec_value_name') , ""))


update li_goods_sku_copy1 set specs = CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[2].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[2].spec_name') is not null, JSON_EXTRACT(json_val,'$[2].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[2].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[2].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[2].spec_value_name') , ""))


update li_goods_sku_copy1 set specs = CONCAT(specs , "}")

-- 无数据，设置成默认
update li_goods_sku_copy1 set specs = '{"默认":"默认"}' where specs is null;



--  拼接
select  id,JSON_VALID(json_val) , JSON_VALUE(json_val, '$[1].spec_name'),specs, CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[1].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[1].spec_name') is not null, JSON_EXTRACT(json_val,'$[1].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[1].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[1].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[1].spec_value_name') , "")) cat from 
 li_goods_sku_copy1 limit 1520;
 


//商品导入
INSERT INTO li_goods ( 
id, delete_flag, brand_id ,buy_count ,
category_path, comment_num, cost, goods_name,intro,
auth_flag,market_enable,mobile_intro,original,price,
quantity,recommend,sales_model,self_operated,store_id,
store_name,small,template_id,thumbnail,big,
goods_type,supplier_id,is_green_goods,green_score,
goods_grab_url,goods_video,scene,extend_id
)

select 
goods_id,0 delete_flag, brand_id,0 buy_count,
1707227305820033025 category_path,0 comment_num, cost_price,goods_name,goods_content,
'PASS' auth_flag, 'DOWN' market_enable,goods_content,SUBSTRING_INDEX(goods_image,',', 1) original,price,
goods_stock,1 recommend,  'RETAIL' sales_model,is_own,  1709839834258444290 store_id,
site_name,SUBSTRING_INDEX(goods_image,',', 1) small,0 template_id,SUBSTRING_INDEX(goods_image,',', 1) thumbnail,SUBSTRING_INDEX(goods_image,',', 1) big,
if(goods_class = 1, "PHYSICAL_GOODS","VIRTUAL_GOODS") goods_type ,supplier_id,is_green,give_green,
out_url,video_url, 'STORE' scene,1709839834258444290 extend_id
from goods   where category_name like '%电器%'; 



INSERT INTO li_goods ( 
id, delete_flag, brand_id ,buy_count ,
category_path, comment_num, cost, goods_name,intro,
auth_flag,market_enable,mobile_intro,original,price,
quantity,recommend,sales_model,self_operated,store_id,
store_name,small,template_id,thumbnail,big,
goods_type,supplier_id,is_green_goods,green_score,
goods_grab_url,goods_video,scene,extend_id
)
select 
goods_id,0 delete_flag, brand_id,0 buy_count,
1707227305820033025 category_path,0 comment_num, cost_price,goods_name,goods_content,
'PASS' auth_flag, 'DOWN' market_enable,goods_content,SUBSTRING_INDEX(goods_image,',', 1) original,price,
goods_stock,1 recommend,  'RETAIL' sales_model,is_own,  1709838863570669569 store_id,
site_name,SUBSTRING_INDEX(goods_image,',', 1) small,0 template_id,SUBSTRING_INDEX(goods_image,',', 1) thumbnail,SUBSTRING_INDEX(goods_image,',', 1) big,
if(goods_class = 1, "PHYSICAL_GOODS","VIRTUAL_GOODS") goods_type ,supplier_id,is_green,give_green,
out_url,video_url, 'STORE' scene, 1709838863570669569 extend_id
from goods   where category_name not like '%电器%'; 



//图片拆分插入
insert into li_goods_gallery ( create_by, goods_id, is_default, original, small, thumbnail )
select  -1 create_by, goods_id , 0 is_default, image original, image small, image thumbnail 
from
(	SELECT 
    goods_id,SUBSTRING_INDEX(SUBSTRING_INDEX(A.COL,',',help_topic_id+1),',',-1) AS image 
FROM 
    (SELECT goods_id,goods_image COL from goods) A join 
    mysql.help_topic b WHERE 
    b.help_topic_id < LENGTH(A.COL)-LENGTH(REPLACE(A.COL,',',''))+1 
) BB



//供应商导入   思路先把，所有的goods_id 都加 10000保证id不重复。
update supply_goods set goods_id = goods_id + 10000 
update supply_goods_sku set goods_id = goods_id + 10000
update supply_goods_sku set sku_id = sku_id + 100000 


//供应商sku导入
INSERT INTO li_goods_sku (
id, delete_flag, big,small, brand_id, buy_count, 
category_path, comment_num, cost, goods_id, goods_name,
store_id, store_name,sn,specs,goods_type, quantity,weight, price) 

SELECT
sku_id, 0 delete_flag, sku_image,sku_image, 0 brand_id, 0 buy_count ,
goods_attr_class ,0 comment_num,cost_price, goods_id, goods_name, 
site_id,site_name ,sku_no,  CONCAT("{'images':","[{'url':'", sku_image ,"'}]}")  specs,goods_class,stock,weight/1000 weight,price
from
supply_goods_sku where is_delete = 0 ;



delete from li_goods_sku where buy_count = -1;
select *  from li_goods_sku where buy_count = -1;

//新供应商sku导入

INSERT INTO li_goods_sku (
id, delete_flag, big,small, brand_id, buy_count, 
category_path, comment_num, cost, goods_id, goods_name,
store_id, store_name,sn, json_val,specs,goods_type, quantity,weight, price,create_by) 

SELECT
sku_id, 0 delete_flag, sku_image,sku_image, 0 brand_id, 0 buy_count ,
goods_attr_class ,0 comment_num,cost_price, goods_id, goods_name, 
site_id,site_name ,sku_no,  sku_spec_format,
case when JSON_VALID(sku_spec_format) then 
CONCAT(
"{'images':","[{'url':'", sku_image ,"'}]",",",
case when JSON_VALID(sku_spec_format->> '$[0].spec_name') then JSON_EXTRACT(sku_spec_format,'$[0].spec_name') else  JSON_EXTRACT(sku_spec_format,'$[0].spec_name') end, ":", 
case when JSON_VALID(sku_spec_format->> '$[0].spec_value_name') then JSON_EXTRACT(sku_spec_format,'$[0].spec_value_name') else  JSON_EXTRACT(sku_spec_format,'$[0].spec_value_name') end)
else null end
specs,goods_class,stock,weight/1000 weight,price, -10 create_by
from
supply_goods_sku where is_delete = 0 ;



update li_goods_sku set specs = CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[1].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[1].spec_name') is not null, JSON_EXTRACT(json_val,'$[1].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[1].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[1].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[1].spec_value_name') , ""))
where create_by = -10


update li_goods_sku set specs = CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[2].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[2].spec_name') is not null, JSON_EXTRACT(json_val,'$[2].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[2].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[2].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[2].spec_value_name') , ""))
where create_by = -10



update li_goods_sku set specs = CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[3].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[3].spec_name') is not null, JSON_EXTRACT(json_val,'$[3].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[3].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[3].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[3].spec_value_name') , ""))
where create_by = -10



update li_goods_sku set specs = CONCAT(specs , 
IF(JSON_VALUE(json_val, '$[4].spec_name') is not null,  "," , ""),
IF(JSON_VALUE(json_val, '$[4].spec_name') is not null, JSON_EXTRACT(json_val,'$[4].spec_name') , ""),
IF(JSON_VALUE(json_val, '$[4].spec_name')	is not null, ":" , ""),
IF(JSON_VALUE(json_val, '$[4].spec_value_name') is not null, JSON_EXTRACT(json_val,'$[4].spec_value_name') , ""))
where create_by = -10


update li_goods_sku set specs = CONCAT(specs , "}") where create_by = -10

-- 无数据，设置成默认
update li_goods_sku set specs = '{"默认":"默认"}' where specs is null and  create_by = -10;



//商品导入
INSERT INTO li_goods ( 
id, delete_flag, brand_id ,buy_count ,
category_path, comment_num, cost, goods_name,intro,
auth_flag,market_enable,mobile_intro,original,price,
quantity,recommend,sales_model,self_operated,store_id,
store_name,small,template_id,thumbnail,big,
goods_type,
goods_grab_url,goods_video,scene,extend_id
)
select 
goods_id,0 delete_flag, brand_id,0 buy_count,
1707227305820033025 category_path,0 comment_num, cost_price,goods_name,goods_content,
'PASS' auth_flag, 'DOWN' market_enable,goods_content,SUBSTRING_INDEX(goods_image,',', 1) original,price,
goods_stock,1 recommend,  'RETAIL' sales_model,is_own,  1710539757727617025 store_id,
site_name,SUBSTRING_INDEX(goods_image,',', 1) small,0 template_id,SUBSTRING_INDEX(goods_image,',', 1) thumbnail,SUBSTRING_INDEX(goods_image,',', 1) big,
if(goods_class = 1, "PHYSICAL_GOODS","VIRTUAL_GOODS") goods_type ,
out_url,video_url, 'SUPPLIER' scene, 1710539757727617025 extend_id
from supply_goods   where category_name not like '%电器%' and is_delete = 0;


INSERT INTO li_goods ( 
id, delete_flag, brand_id ,buy_count ,
category_path, comment_num, cost, goods_name,intro,
auth_flag,market_enable,mobile_intro,original,price,
quantity,recommend,sales_model,self_operated,store_id,
store_name,small,template_id,thumbnail,big,
goods_type,
goods_grab_url,goods_video,scene,extend_id
)
select 
goods_id,0 delete_flag, brand_id,0 buy_count,
1707227305820033025 category_path,0 comment_num, cost_price,goods_name,goods_content,
'PASS' auth_flag, 'DOWN' market_enable,goods_content,SUBSTRING_INDEX(goods_image,',', 1) original,price,
goods_stock,1 recommend,  'RETAIL' sales_model,is_own,  1710540003824209922 store_id,
site_name,SUBSTRING_INDEX(goods_image,',', 1) small,0 template_id,SUBSTRING_INDEX(goods_image,',', 1) thumbnail,SUBSTRING_INDEX(goods_image,',', 1) big,
if(goods_class = 1, "PHYSICAL_GOODS","VIRTUAL_GOODS") goods_type ,
out_url,video_url, 'SUPPLIER' scene, 1710540003824209922 extend_id
from supply_goods   where category_name like '%电器%' and is_delete = 0;



//图片拆分导入
insert into li_goods_gallery ( create_by, goods_id, is_default, original, small, thumbnail )
select  -1 create_by, goods_id , 0 is_default, image original, image small, image thumbnail 
from
(	SELECT 
    goods_id,SUBSTRING_INDEX(SUBSTRING_INDEX(A.COL,',',help_topic_id+1),',',-1) AS image 
FROM 
    (SELECT goods_id,goods_image COL from supply_goods) A join 
    mysql.help_topic b WHERE 
    b.help_topic_id < LENGTH(A.COL)-LENGTH(REPLACE(A.COL,',',''))+1 
) BB


//sku最低价格绿色积分的绿色积分，设置成默认
UPDATE li_goods u 
SET u.default_sku_green_score = ( SELECT x.green_score FROM li_goods_sku x WHERE x.goods_id = u.id 
group by x.goods_id ),
	u.create_by = -1 
	
//将重量导入过来。
UPDATE li_goods_sku u 
SET u.weight = ( SELECT x.weight FROM goods_sku x WHERE x.sku_id = u.id 
 ),
	u.create_by = -2 


//删除 大写A-Z 加 横岗 的商品
delete from li_goods_sku where goods_name REGEXP '^[A-Z]-'
delete from li_goods where goods_name REGEXP '^[A-Z]-'


//修改上下架，采购，代发状态
select count(*) from li_goods where store_id = 1710539757727617025 and delete_flag = false;
update li_goods set support_proxy =true, support_purchase = true, market_enable = 'UPPER'  where store_id = 1710539757727617025 and delete_flag = false;