zencart数据迁移到magento数据库操作-sql版本

1,732 人次阅读

共计 2671 个字符，预计需要花费 7 分钟才能阅读完成。

一首先在zen-cart中建立数据库，代码如下：

droptableif exists export_magento;

createtableexport_magento (

   store               varchar(30)     notnull

  ,websites            varchar(30)     notnull

  ,attribute_set       varchar(30)     notnull

  ,type                varchar(30)     notnull

  ,sku                 varchar(50)     notnull

  ,namevarchar(512)    notnull

  ,product_name        varchar(512)    notnull

  ,market_price        decimal(8,2)    null

  ,price               decimal(8,2)    notnull

  ,special_price       decimal(8,2)    null

  ,weight              varchar(30)     notnulldefault''

  ,short_description   varchar(1000)   notnulldefault''

  ,description         text            notnull

  ,meta_title          varchar(512)    notnulldefault''

  ,meta_keyword        varchar(512)    notnulldefault''

  ,meta_description    varchar(2000)   notnulldefault''

  ,image               varchar(255)    notnulldefault''

  ,small_image         varchar(255)    notnulldefault''

  ,thumbnail           varchar(255)    notnulldefault''

  ,gallery             varchar(512)    notnulldefault''

  ,status              varchar(10)     notnulldefault'Enabled'

  ,tax_class_id        varchar(10)     notnulldefault'None'

  ,qty                 intnotnulldefault0

  ,category_ids        varchar(50)     notnulldefault''

  ,is_in_stock         tinyint         notnulldefault1

  ,visibility          varchar(30)     notnulldefault''

  ,adddate             varchar(30)     notnulldefault''

);

其次直接根据zen-cart的数据库结构生成magento可以批量导入的表

truncate table export_magento;

insert into export_magento

select 'admin' as store

      ,'base' as websites

      ,'Common' as attribute_set

      ,'simple' as type

      ,p.products_model as sku

      ,pd.products_name as name

      ,pd.products_name as product_name

      ,'' as market_price

      ,p.products_price_retail as price

      ,p.products_price          -- ifnull(s.specials_new_products_price, p.products_price_sorter) as special_price

      ,p.products_weight as weight

      ,'Brand new, never refurbished, 100% compatible' as short_description

      ,pd.products_description as description

      ,pd.products_name as meta_title

      ,'' as meta_keyword

      ,pd.products_name as meta_description

      ,substring_index(p.products_image, ',', 1) as image

      ,substring_index(p.products_image, ',', 1) as small_image

      ,substring_index(p.products_image, ',', 1) as thumbnail

      ,replace(p.products_image, ',', ';') as gallery

      ,'Enabled' as status

      ,'None' as tax_class_id

      ,p.products_quantity as qty

      ,p.master_categories_id as category_ids

      ,1 as is_in_stock

      ,'Catalog, Search' as visibility

      ,date_format(now(),'%Y/%d/%c') as adddate

  from products p

          inner join products_description pd

    on p.products_id = pd.products_id

where pd.language_id = 1

   and p.master_categories_id in ();