magento 采集数据处理sql 初稿

DROP TABLE IF EXISTS export_magento;

CREATE TABLE export_magento(
  store               VARCHAR(30)     NOT NULL
  ,websites            VARCHAR(30)     NOT NULL
  ,attribute_set       VARCHAR(30)     NOT NULL
  ,type              VARCHAR(30)     NOT NULL
  ,sku                 VARCHAR(50)     NOT NULL
  ,name              VARCHAR(512)    NOT NULL
  ,product_name        VARCHAR(512)    NOT NULL
  ,price               VARCHAR(50)    NOT NULL
  ,special_price       VARCHAR(50)   NULL
  ,weight              VARCHAR(30)     NOT NULL DEFAULT ''
  ,short_description   VARCHAR(1000)   NOT NULL DEFAULT ''
  ,description         TEXT            NOT NULL
  ,meta_title          VARCHAR(512)    NOT NULL DEFAULT ''
  ,meta_keyword        VARCHAR(512)    NOT NULL DEFAULT ''
  ,meta_description    VARCHAR(2000)   NOT NULL DEFAULT ''
  ,image               VARCHAR(255)    NOT NULL DEFAULT ''
  ,small_image         VARCHAR(255)    NOT NULL DEFAULT ''
  ,thumbnail           VARCHAR(255)    NOT NULL DEFAULT ''
  ,gallery             VARCHAR(512)    NOT NULL DEFAULT ''
  ,status             VARCHAR(10)     NOT NULL DEFAULT 'Enabled'
  ,tax_class_id        VARCHAR(10)     NOT NULL DEFAULT 'None'
  ,qty                 INT             NOT NULL DEFAULT 0
  ,category_ids        VARCHAR(50)     NOT NULL DEFAULT ''
  ,is_in_stock         TINYINT         NOT NULL DEFAULT 1
  ,visibility          VARCHAR(30)     NOT NULL DEFAULT ''
);

delete from export_magento where store='store';
delete from export_magento where image='';
update export_magento set short_description=replace(short_description,'?',' ');
update export_magento set meta_description=replace(meta_description,'?',' ');
update export_magento set price=replace(price,'$','');
update export_magento set special_price=replace(special_price,'$','');
update export_magento set visibility='Catalog, Search';
update export_magento set attribute_set='Electronics';
update export_magento set image=replace(image,'/','/E/psp2000/');
update export_magento set small_image=replace(small_image,'/','/E/psp2000/');
update export_magento set thumbnail=replace(thumbnail,'/','/E/psp2000/');
update export_magento set gallery=replace(gallery,'/','/E/psp2000/');
update export_magento set category_ids='2,3';

select LOCATE('src="',image,6) from export_magento;
select LOCATE('">',image) from export_magento;
select SUBSTRING(image,LOCATE('src="',image)+5,LOCATE('">',image)-LOCATE('src="',image)-5) from export_magento;

                  
/*INSERT INTO export_magento_temp
SELECT p.entity_id AS entity_id
      ,'admin' AS store
      ,'base' AS websites
      ,'Default' AS attribute_set
      ,'simple' AS TYPE
      ,p.sku AS sku
      ,p.name AS NAME
      ,p.name AS product_name
      ,p.price AS price
      ,p.special_price  AS special_price
      ,p.weight  AS weight
      ,p.short_description AS short_description
      ,'' AS description
      ,p.name AS meta_title
      ,'' AS meta_keyword
      ,'' AS meta_description
      ,'' AS image
      ,p.small_image AS small_image
      ,p.thumbnail AS thumbnail
      ,'' AS gallery
      ,'Enabled' AS STATUS
      ,'None' AS tax_class_id
      ,1000 AS qty
      ,'2' AS category_ids
      ,1 AS is_in_stock
      ,'Catalog, Search' AS visibility
  FROM catalog_product_flat_1 p;*/