我们知道，不同类别的商品属性是不同的。

譬如图书和男装

图书：作者、出版社、出版时间、ISBN、开本

男装：工艺（纯色或图案)、风格、适用对象、材质等

首先建一个商品分类属性表 prod_class_attr ，并插入一些测试数据：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `prod_class_attr`
-- ----------------------------
DROP TABLE IF EXISTS `prod_class_attr`;
CREATE TABLE `prod_class_attr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attr_name` varchar(100) NOT NULL,
  `attr_note` varchar(100) DEFAULT NULL COMMENT '属性备注',
  `attr_pid` int(11) NOT NULL DEFAULT '0',
  `prod_classid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of prod_class_attr
-- ----------------------------
INSERT INTO `prod_class_attr` VALUES ('1', 'press', '出版社', '0', '1');
INSERT INTO `prod_class_attr` VALUES ('2', 'author', '作者', '0', '1');
INSERT INTO `prod_class_attr` VALUES ('3', 'format', '开本', '0', '1');
INSERT INTO `prod_class_attr` VALUES ('4', 'pressdate', '出版时间', '0', '1');
INSERT INTO `prod_class_attr` VALUES ('5', 'technics', '工艺', '0', '2');
INSERT INTO `prod_class_attr` VALUES ('6', 'style', '风格', '0', '2');
INSERT INTO `prod_class_attr` VALUES ('7', 'target', '适用对象', '0', '2');
INSERT INTO `prod_class_attr` VALUES ('8', ' material', '材质', '0', '2');

再创建一张表prod_attr

用于记录商家自定义配置的个性化商品属性：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `prod_attr`
-- ----------------------------
DROP TABLE IF EXISTS `prod_attr`;
CREATE TABLE `prod_attr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` int(11) NOT NULL,
  `attr_id` int(11) NOT NULL,
  `attr_value` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

动态执行sql语句

什么叫"动态执行sql语句"？举个例子，我们先来执行一条正常的sql语句：

SELECT 1 AS attr_id, '中国出版社' AS attr_value UNION SELECT 2 AS attr_id, '刘勇' AS attr_value;

查询结果如图所示：

那好，接下来我希望通过传入“字符串sql语句”，然后让Mysql帮我执行，需要怎么做呢？分为以下四步：

# 定义sql语句，通常是程序或者参数传递进来的。
SET @ss = 'SELECT 1 AS attr_id, \'中国出版社\' AS attr_value UNION SELECT 2 AS attr_id, \'刘勇\' AS attr_value;';
# 预定义
PREPARE pname from @ss;
# 执行sql语句
EXECUTE pname;
# 释放变量
DEALLOCATE PREPARE pname;

执行结果如图所示：

进阶：动态执行Sql并传入变量

# 定义变量
SET @pid = 5;
SET @pid_2 = 8;
# 定义字符串sql。请注意其中的两个“？”，以及字符串需要转移“\”的问题
SET @ss = 'SELECT ? AS prod_id, 1 AS attr_id, \'中国出版社\' AS attr_value 
           UNION 
           SELECT ? AS prod_id, 2 AS attr_id, \'刘勇\' AS attr_value';
# 预定义
PREPARE pname from @ss;
# 执行sql语句，并且传入变量，对号入座的替换“？”
EXECUTE pname using @pid, @pid_2;
# 释放变量
DEALLOCATE PREPARE pname;

只需要在字符串sql中插入占位符 "?" 即可。一一对应着using后面的变量。

先理解这两段sql

SELECT 5 AS prod_id, a.* from (
    SELECT 1 AS attr_id, '中国出版社' AS attr_value 
    UNION 
    SELECT 2 AS attr_id, '刘勇' AS attr_value
) AS a;

将上面查询出来的数据，插入到 prod_attr 表中：

INSERT INTO prod_attr 
SELECT 5 AS prod_id, a.* from (
    SELECT 1 AS attr_id, '中国出版社' AS attr_value 
    UNION
    SELECT 2 AS attr_id, '刘勇' AS attr_value
) AS a;

所有知识点结合起来，做一个存储过程sp_new_prod：

DROP PROCEDURE IF EXISTS `sp_new_prod`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `sp_new_prod`(IN _prod_name VARCHAR(50), IN _class_id INT)
BEGIN
    SET @pid = 0;
    INSERT INTO prod_main(prod_name, prod_classid) VALUES (_prod_name, _class_id);
    SET @pid = LAST_INSERT_ID();
        SET @ss = 'INSERT INTO prod_attr SELECT ? AS prod_id, a.* from (SELECT 1 AS attr_id, \'中国出版社\' AS attr_value UNION SELECT 2 AS attr_id, \'刘勇\' AS attr_value ) AS a';
        PREPARE pname FROM @ss;
    EXECUTE pname USING @pid;
    DEALLOCATE PREPARE pname;
END;

执行示例：

call sp_new_prod('Java从入门到精通', 1)

最终代码：

DROP PROCEDURE IF EXISTS `sp_new_prod`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `sp_new_prod`(IN _prod_name VARCHAR(50), IN _class_id INT, IN _attr_sql VARCHAR(2000))
BEGIN 
    SET @pid = 0;
    INSERT INTO prod_main(prod_name, prod_classid) VALUES (_prod_name, _class_id);
    SET @pid = LAST_INSERT_ID();
    # SET @ss = 'INSERT INTO prod_attr SELECT ? AS prod_id, a.* from (SELECT 1 AS attr_id, \'中国出版社\' AS attr_value UNION SELECT 2 AS attr_id, \'刘勇\' AS attr_value) AS a';
    SET @ss = concat('INSERT INTO prod_attr SELECT ? AS prod_id, a.* FROM (', _attr_sql ,') AS a');
    PREPARE pname FROM @ss;
    EXECUTE pname USING @pid;
    DEALLOCATE PREPARE pname; 
END;

调用示例：

call sp_new_prod('python', 1, 'SELECT 1 AS attr_id, \'中国出版社\' AS attr_value UNION SELECT 2 AS attr_id, \'刘勇\' AS attr_value')