Mysql 存储过程 处理批量插入具有一定特点的数据
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql 存储过程 处理批量插入具有一定特点的数据，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含37647字，纯文字阅读大概需要54分钟。
内容图文
本博文属于原创，转载请注明出处~！
首先，根据业务需求，需要往现有的所有第二级别的知识点中，添加数据
所有第二级别的数据如下：
上图中，cateCode就是代表级别代表，parentID实现链表树状级别
所有的第三第四级别的数据都是一样，其中sort是和当前id一致的，而parentID需要根据逻辑公式求出，下边是第三第四级别的插入sql数据
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况','4065','产地概况',NULL,NULL,'4008',NULL,'中药材组','A.1.1'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'市场需求情况','4066','市场需求情况',NULL,NULL,'4008',NULL,'中药材组','A.1.2'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'价格成本分析','4067','价格成本分析',NULL,NULL,'4008',NULL,'中药材组','A.1.3'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'政策法规','4068','政策法规',NULL,NULL,'4008',NULL,'中药材组','A.1.4'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'来源及基本形态','4069','来源及基本形态',NULL,NULL,'4065',NULL,'中药材组','A.1.1.1'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地分布','4070','产地分布',NULL,NULL,'4065',NULL,'中药材组','A.1.1.2'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地产量分布','4071','产地产量分布',NULL,NULL,'4065',NULL,'中药材组','A.1.1.3'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'生产地的政策','4072','生产地的政策',NULL,NULL,'4065',NULL,'中药材组','A.1.1.4'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'市场主流规格及我司需求的规格等级要求','4073','市场主流规格及我司需求的规格等级要求',NULL,NULL,'4065',NULL,'中药材组','A.1.1.5'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产量与需求量关系','4074','产量与需求量关系',NULL,NULL,'4066',NULL,'中药材组','A.1.2.1'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'主要需求厂家或主要需求品类的情况','4075','主要需求厂家或主要需求品类的情况',NULL,NULL,'4066',NULL,'中药材组','A.1.2.2'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'我司目前主要供应商','4076','我司目前主要供应商',NULL,NULL,'4066',NULL,'中药材组','A.1.2.3'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'历史价格分析及未来一年的价格预测','4077','历史价格分析及未来一年的价格预测',NULL,NULL,'4067',NULL,'中药材组','A.1.3.1'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'种植流程分析','4078','种植流程分析',NULL,NULL,'4067',NULL,'中药材组','A.1.3.2'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'加工环节分析','4079','加工环节分析',NULL,NULL,'4067',NULL,'中药材组','A.1.3.3'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'可能对价格产生影响的主要因素','4080','可能对价格产生影响的主要因素',NULL,NULL,'4067',NULL,'中药材组','A.1.3.4'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'总体成本分析','4081','总体成本分析',NULL,NULL,'4067',NULL,'中药材组','A.1.3.5'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'供应风险','4082','供应风险',NULL,NULL,'4068',NULL,'中药材组','A.1.4.1'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'质量风险','4083','质量风险',NULL,NULL,'4068',NULL,'中药材组','A.1.4.2'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'法规风险','4084','法规风险',NULL,NULL,'4068',NULL,'中药材组','A.1.4.3');
为了摸索存储化设置，我进行了一些辅助查询sql语句的摸索，觉得很有必要，记录如下
ALTER TABLE lc_knowledge_type AUTO_INCREMENT=4169
SELECT * FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND id > 3999
#20条
#最大记录为 4068
DELETE FROM lc_knowledge_type WHERE id >0
UPDATE lc_knowledge_type SET parentid = NULL
#LIKE 单个匹配字母
SELECT @cateNameDual:=cateCode FROM lc_knowledge_type WHERE cateCode LIKE '_'
#查找父类
SELECT @cateNameDual:=id FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND cateCode='A.1' AND id>3999;
#-自连接
SELECT @cateNameDual:=id FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND cateCode='A.1' AND id>3999;
#-查找当前 知识分类的 父类信息
SELECT fa.* FROM lc_knowledge_type AS child,lc_knowledge_type AS fa WHERE child.id = fa.id AND child.id = 4168
#截取固定长度的子串
SELECT CHAR_LENGTH(cateCode) AS len ,LEFT(cateCode,len) FROM lc_knowledge_type WHERE id = 123
#总共有多少个需要插入的目录 34
SELECT * FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND id>3999;
SELECT COUNT(*) FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND id>3999;
#存储化测试插入 例子
SELECT @cateNameDual:=cateCode,@cateNameDual2:=topParentName FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND catecode LIKE 'A.2'AND id > 3999;
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况','4065','产地概况',NULL,NULL,NULL,NULL,'中药材组',CONCAT(CONCAT('A.2',@cateNameDual),@cateNameDual2));
#删除数据 还原回原始状态
UPDATE lc_knowledge_type SET parentId=NULL WHERE id >4168;
DELETE FROM lc_knowledge_type WHERE id >4168;
ALTER TABLE lc_knowledge_type AUTO_INCREMENT=4169;
现在是主菜了：
存储过程化处理，根据数据进行必要的处理如下
#批量存储化处理 #cur_index 暂时用不上 稍后用 update lc_knowledge_type set sort=id where id > 3999 # A2-A9增加三四级目录 DROP PROCEDURE IF EXISTS perA; DELIMITER // CREATE PROCEDURE perA() BEGIN #循环的变量,代表第二级id，如A.1 DECLARE num INT; #当前插入值的id索引 DECLARE cur_index INT; #当前插入值的记录的parentId开始索引,代表第三级别，如A.1.1,总共有20条记录，每次循环完，得增量20迭代 DECLARE index_start INT; #当前插入值的cateCode前缀 DECLARE cateCodeHead VARCHAR(30); #当前插入值的topParentName DECLARE topParName VARCHAR(30); #当前插入值的cateCode前缀的变量升序索引 DECLARE index_asc INT; SET cur_index=4169,num=4009,cateCodeHead='A.',index_start=4169,topParName='中药材组',index_asc=2; WHILE num < 4017 DO SET cateCodeHead='A.', cateCodeHead=CONCAT(CONCAT(cateCodeHead,index_asc),'.'); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况',cur_index,'产地概况',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'1')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'市场需求情况',cur_index,'市场需求情况',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'2')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'价格成本分析',cur_index,'价格成本分析',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'3')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'政策法规',cur_index,'政策法规',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'4')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'来源及基本形态',cur_index,'来源及基本形态',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.1')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地分布',cur_index,'产地分布',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.2')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地产量分布',cur_index,'产地产量分布',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.3')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'生产地的政策',cur_index,'生产地的政策',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.4')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'市场主流规格及我司需求的规格等级要求',cur_index,'市场主流规格及我司需求的规格等级要求',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.5')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产量与需求量关系',cur_index,'产量与需求量关系',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'2.1')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'主要需求厂家或主要需求品类的情况',cur_index,'主要需求厂家或主要需求品类的情况',NULL,NULL,index_start+1,NULL,topParName,CONCAT(cateCodeHead,'2.2')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'我司目前主要供应商',cur_index,'我司目前主要供应商',NULL,NULL,index_start+1,NULL,topParName,CONCAT(cateCodeHead,'2.3')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'历史价格分析及未来一年的价格预测',cur_index,'历史价格分析及未来一年的价格预测',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.1')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'种植流程分析',cur_index,'种植流程分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.2')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'加工环节分析',cur_index,'加工环节分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.3')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'可能对价格产生影响的主要因素',cur_index,'可能对价格产生影响的主要因素',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.4')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'总体成本分析',cur_index,'总体成本分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.5')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'供应风险',cur_index,'供应风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.1')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'质量风险',cur_index,'质量风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.2')); INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'法规风险',cur_index,'法规风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.3')); SET num=num+1,cur_index=cur_index+1,index_asc=index_asc+1,index_start=index_start+20; END WHILE; END;
都是很基础的逻辑，一看就能懂，其中语句块中有一句这么样的东西，不理解的同学，可以百度看看，主要是起到，取消‘；’分号停顿的作用
DELIMITER //
然后进行调用存储过程
#调用存储过程 CALL perA
全部语句贴示如下：仅供参考学习，因为也是学习摸索，所以，如有厉害的大牛看到这样的代码，莫笑
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况','4065','产地概况',NULL,NULL,'4008',NULL,'中药材组','A.1.1');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'市场需求情况','4066','市场需求情况',NULL,NULL,'4008',NULL,'中药材组','A.1.2');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'价格成本分析','4067','价格成本分析',NULL,NULL,'4008',NULL,'中药材组','A.1.3');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'政策法规','4068','政策法规',NULL,NULL,'4008',NULL,'中药材组','A.1.4');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'来源及基本形态','4069','来源及基本形态',NULL,NULL,'4065',NULL,'中药材组','A.1.1.1');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地分布','4070','产地分布',NULL,NULL,'4065',NULL,'中药材组','A.1.1.2');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地产量分布','4071','产地产量分布',NULL,NULL,'4065',NULL,'中药材组','A.1.1.3');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'生产地的政策','4072','生产地的政策',NULL,NULL,'4065',NULL,'中药材组','A.1.1.4');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'市场主流规格及我司需求的规格等级要求','4073','市场主流规格及我司需求的规格等级要求',NULL,NULL,'4065',NULL,'中药材组','A.1.1.5');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产量与需求量关系','4074','产量与需求量关系',NULL,NULL,'4066',NULL,'中药材组','A.1.2.1');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'主要需求厂家或主要需求品类的情况','4075','主要需求厂家或主要需求品类的情况',NULL,NULL,'4066',NULL,'中药材组','A.1.2.2');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'我司目前主要供应商','4076','我司目前主要供应商',NULL,NULL,'4066',NULL,'中药材组','A.1.2.3');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'历史价格分析及未来一年的价格预测','4077','历史价格分析及未来一年的价格预测',NULL,NULL,'4067',NULL,'中药材组','A.1.3.1');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'种植流程分析','4078','种植流程分析',NULL,NULL,'4067',NULL,'中药材组','A.1.3.2');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'加工环节分析','4079','加工环节分析',NULL,NULL,'4067',NULL,'中药材组','A.1.3.3');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'可能对价格产生影响的主要因素','4080','可能对价格产生影响的主要因素',NULL,NULL,'4067',NULL,'中药材组','A.1.3.4');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'总体成本分析','4081','总体成本分析',NULL,NULL,'4067',NULL,'中药材组','A.1.3.5');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'供应风险','4082','供应风险',NULL,NULL,'4068',NULL,'中药材组','A.1.4.1');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'质量风险','4083','质量风险',NULL,NULL,'4068',NULL,'中药材组','A.1.4.2');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'法规风险','4084','法规风险',NULL,NULL,'4068',NULL,'中药材组','A.1.4.3');
ALTER TABLE lc_knowledge_type AUTO_INCREMENT=4169
SELECT * FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND id > 3999
#20条
#最大记录为 4068
DELETE FROM lc_knowledge_type WHERE id >0
UPDATE lc_knowledge_type SET parentid = NULL
#LIKE 单个匹配字母
SELECT @cateNameDual:=cateCode FROM lc_knowledge_type WHERE cateCode LIKE '_'
#查找父类
SELECT @cateNameDual:=id FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND cateCode='A.1' AND id>3999;
#-自连接
SELECT @cateNameDual:=id FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND cateCode='A.1' AND id>3999;
#-查找当前 知识分类的 父类信息
SELECT fa.* FROM lc_knowledge_type AS child,lc_knowledge_type AS fa WHERE child.id = fa.id AND child.id = 4168
#截取固定长度的子串
SELECT CHAR_LENGTH(cateCode) AS len ,LEFT(cateCode,len) FROM lc_knowledge_type WHERE id = 123
#总共有多少个需要插入的目录 34
SELECT * FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND id>3999;
SELECT COUNT(*) FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND id>3999;
#存储化测试插入 例子
SELECT @cateNameDual:=cateCode,@cateNameDual2:=topParentName FROM lc_knowledge_type WHERE CHAR_LENGTH(cateCode) = 3 AND topParentName="中药材组" AND catecode LIKE 'A.2'AND id > 3999;
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况','4065','产地概况',NULL,NULL,NULL,NULL,'中药材组',CONCAT(CONCAT('A.2',@cateNameDual),@cateNameDual2));
#删除数据 还原回原始状态
UPDATE lc_knowledge_type SET parentId=NULL WHERE id >4168;
DELETE FROM lc_knowledge_type WHERE id >4168;
ALTER TABLE lc_knowledge_type AUTO_INCREMENT=4169;
#批量存储化处理
#cur_index 暂时用不上 稍后用 update lc_knowledge_type set sort=id where id > 3999
# A2-A9增加三四级目录
DROP PROCEDURE IF EXISTS perA;
DELIMITER //
CREATE PROCEDURE perA()
BEGIN
#循环的变量,代表第二级id，如A.1
DECLARE num INT;
#当前插入值的id索引
DECLARE cur_index INT;
#当前插入值的记录的parentId开始索引,代表第三级别，如A.1.1,总共有20条记录，每次循环完，得增量20迭代
DECLARE index_start INT;
#当前插入值的cateCode前缀
DECLARE cateCodeHead VARCHAR(30);
#当前插入值的topParentName
DECLARE topParName VARCHAR(30);
#当前插入值的cateCode前缀的变量升序索引
DECLARE index_asc INT;
SET cur_index=4169,num=4009,cateCodeHead='A.',index_start=4169,topParName='中药材组',index_asc=2;
WHILE num < 4017 DO
SET cateCodeHead='A.', cateCodeHead=CONCAT(CONCAT(cateCodeHead,index_asc),'.');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况',cur_index,'产地概况',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'市场需求情况',cur_index,'市场需求情况',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'价格成本分析',cur_index,'价格成本分析',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'政策法规',cur_index,'政策法规',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'4'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'来源及基本形态',cur_index,'来源及基本形态',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地分布',cur_index,'产地分布',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地产量分布',cur_index,'产地产量分布',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'生产地的政策',cur_index,'生产地的政策',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.4'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'市场主流规格及我司需求的规格等级要求',cur_index,'市场主流规格及我司需求的规格等级要求',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.5'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产量与需求量关系',cur_index,'产量与需求量关系',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'2.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'主要需求厂家或主要需求品类的情况',cur_index,'主要需求厂家或主要需求品类的情况',NULL,NULL,index_start+1,NULL,topParName,CONCAT(cateCodeHead,'2.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'我司目前主要供应商',cur_index,'我司目前主要供应商',NULL,NULL,index_start+1,NULL,topParName,CONCAT(cateCodeHead,'2.3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'历史价格分析及未来一年的价格预测',cur_index,'历史价格分析及未来一年的价格预测',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'种植流程分析',cur_index,'种植流程分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'加工环节分析',cur_index,'加工环节分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'可能对价格产生影响的主要因素',cur_index,'可能对价格产生影响的主要因素',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.4'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'总体成本分析',cur_index,'总体成本分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.5'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'供应风险',cur_index,'供应风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'质量风险',cur_index,'质量风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'法规风险',cur_index,'法规风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.3'));
SET num=num+1,cur_index=cur_index+1,index_asc=index_asc+1,index_start=index_start+20;
END WHILE;
END;
#调用存储过程
CALL perA
#######################################################################
# B1-B9增加三四级目录
DROP PROCEDURE IF EXISTS perB;
DELIMITER //
CREATE PROCEDURE perB()
BEGIN
#循环的变量,代表第二级id，如A.1
DECLARE num INT;
#当前插入值的id索引
DECLARE cur_index INT;
#当前插入值的记录的parentId开始索引,代表第三级别，如A.1.1,总共有20条记录，每次循环完，得增量20迭代
DECLARE index_start INT;
#当前插入值的cateCode前缀
DECLARE cateCodeHead VARCHAR(30);
#当前插入值的topParentName
DECLARE topParName VARCHAR(30);
#当前插入值的cateCode前缀的变量升序索引
DECLARE index_asc INT;
SET cur_index=4329,num=4029,cateCodeHead='B.',index_start=4329,topParName='中药材组',index_asc=1;
WHILE num < 4038 DO
SET cateCodeHead='B.', cateCodeHead=CONCAT(CONCAT(cateCodeHead,index_asc),'.');
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'产地概况',cur_index,'产地概况',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'市场需求情况',cur_index,'市场需求情况',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'价格成本分析',cur_index,'价格成本分析',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'1',NULL,'政策法规',cur_index,'政策法规',NULL,NULL,num,NULL,topParName,CONCAT(cateCodeHead,'4'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'来源及基本形态',cur_index,'来源及基本形态',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地分布',cur_index,'产地分布',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产地产量分布',cur_index,'产地产量分布',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'生产地的政策',cur_index,'生产地的政策',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.4'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'市场主流规格及我司需求的规格等级要求',cur_index,'市场主流规格及我司需求的规格等级要求',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'1.5'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'产量与需求量关系',cur_index,'产量与需求量关系',NULL,NULL,index_start,NULL,topParName,CONCAT(cateCodeHead,'2.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'主要需求厂家或主要需求品类的情况',cur_index,'主要需求厂家或主要需求品类的情况',NULL,NULL,index_start+1,NULL,topParName,CONCAT(cateCodeHead,'2.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'我司目前主要供应商',cur_index,'我司目前主要供应商',NULL,NULL,index_start+1,NULL,topParName,CONCAT(cateCodeHead,'2.3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'历史价格分析及未来一年的价格预测',cur_index,'历史价格分析及未来一年的价格预测',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'种植流程分析',cur_index,'种植流程分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'加工环节分析',cur_index,'加工环节分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.3'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'可能对价格产生影响的主要因素',cur_index,'可能对价格产生影响的主要因素',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.4'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'总体成本分析',cur_index,'总体成本分析',NULL,NULL,index_start+2,NULL,topParName,CONCAT(cateCodeHead,'3.5'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'供应风险',cur_index,'供应风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.1'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'质量风险',cur_index,'质量风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.2'));
INSERT INTO `lc_knowledge_type` ( `createTime`, `modifyTime`, `child`, `image`, `remark`, `sort`, `typeName`, `modifyId`, `ownerId`, `parentId`, `photoId`, `topParentName`, `cateCode`) VALUES(NULL,NULL,'0',NULL,'法规风险',cur_index,'法规风险',NULL,NULL,index_start+3,NULL,topParName,CONCAT(cateCodeHead,'4.3'));
SET num=num+1,cur_index=cur_index+1,index_asc=index_asc+1,index_start=index_start+20;
END WHILE;
END;
#调用存储过程
CALL perB
本例完~~~~~~~~~~！！！！
Mysql 存储过程 处理批量插入具有一定特点的数据
标签：
本文系统来源：http://blog.csdn.net/wuqilianga/article/details/51720866
内容总结
以上是互联网集市为您收集整理的Mysql 存储过程 处理批量插入具有一定特点的数据全部内容，希望文章能够帮你解决Mysql 存储过程 处理批量插入具有一定特点的数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】