需求

不同的商品属性规格具有不同的价格，譬如《Java从入门到跳楼》的开本属性。

分为 “16开” 和 “32开” （既书本大小不一样）。价格就不同了。分为￥30 和 ￥35

临时表： TEMPORARY TABLE

DROP TEMPORARY TABLE IF EXISTS temp_a;
create TEMPORARY TABLE temp_a AS SELECT 3 AS id;
SELECT * FROM temp_a;

添加属性价格表prod_price

-- ----------------------------
-- Table structure for `prod_price`
-- ----------------------------
DROP TABLE IF EXISTS `prod_price`;
CREATE TABLE `prod_price` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` int(11) NOT NULL,
  `prod_attr_id` int(11) NOT NULL DEFAULT '0',
  `prod_price` decimal(5,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

了解这段sql的作用

select 1 as attr_id, '中国出版社' as attr_value, 0 as prod_price union
select 3 as attr_id, '16开' as attr_value, 30 as prod_price union
select 3 as attr_id, '32开' as attr_value, 35 as prod_price

添加存储过程

BEGIN
  # 该定义必须放在顶部
  DECLARE _isend INT DEFAULT 0;
  DECLARE _prod_id INT;  
  DECLARE _attr_id INT;
  DECLARE _attr_value VARCHAR(20);
  DECLARE _prod_price DECIMAL(5, 2);
  DECLARE _cur CURSOR FOR SELECT prod_id, attr_id, attr_value, prod_price FROM temp_a;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _isend = 1;   

  # 插入主表，获取商品id
  INSERT INTO prod_main(prod_name, prod_classid) VALUES (_prod_name, _prod_classid);
  SET @pid = LAST_INSERT_ID();

  # 创建临时表 + 动态执行sql
  DROP TEMPORARY TABLE IF EXISTS temp_a;
  SET @ss = CONCAT('CREATE TEMPORARY TABLE temp_a AS SELECT ? AS prod_id, a.* FROM (', _attr_sql ,' ) AS a');
  PREPARE pname FROM @ss;
  EXECUTE pname USING @pid;
  DEALLOCATE PREPARE pname;

  # 游标
  OPEN _cur;
      FETCH _cur INTO _prod_id, _attr_id, _attr_value, _prod_price; 
      WHILE _isend != 1 DO
          INSERT INTO prod_attr(prod_id, attr_id, attr_value) VALUES (_prod_id, _attr_id, _attr_value);
          SET @prod_attr_id = LAST_INSERT_ID(); 
          INSERT INTO prod_price(prod_id, prod_attr_id, prod_price) VALUES (_prod_id, @prod_attr_id, _prod_price);
          FETCH _cur INTO _prod_id, _attr_id, _attr_value, _prod_price; 
      END WHILE;
  CLOSE _cur;

  # 删除临时表
  DROP TEMPORARY TABLE temp_a; 
END

调用示例：

call sp_shop_fuck('abc书', 1, '
    select 1 as attr_id, \'中国出版社\' as attr_value, 0 as prod_price union
    select 3 as attr_id, \'16开\' as attr_value, 30 as prod_price union
    select 3 as attr_id, \'32开\' as attr_value, 35 as prod_price
')