问题:已知username表,得到userhobby表
username表:(用户ID,用户名,用户所有爱好) userhobby表:(用户id,用户名,用户单个爱好)
解决思路:实现创建空的single_hobby表,用于存储所有的单一爱好,eg:string ;逐行读取username表,并处理UserHobby字段:按照','分割字符串,将提取出的单一爱好,eg:string ,判断此单一爱好是否已存在single_hobby表中,若是不存在,则将此单一爱好插入到single_hobby表中,并将username表所有的含有此爱好的用户添加到userhobby表中。
single_hobby表:(单一的爱好)
1.mysql中空格问题:字段值若是空''或一个空格' '或一个以上空格' ' mysql用于=号的时候不区分它们,同等对待 。而用于like的时候不一致,因为'%%'表示:所有的字符串;若%之间是1个空格表示:至少有1个空格的字符串;若%之间是2个空格表示:至少有2个空格的字符串;以此类推。
2.mysql中字符大小写问题:mysql对大小写不敏感,从=或like运算符可以看出一致看待。
3.mysql中游标问题:语句需要放在存储过程中。
语法参考:http://www.jbxue.com/db/13451.html
实现:
DROP PROCEDURE IF EXISTS PK;
CREATE PROCEDURE PK()
BEGIN
DECLARE DONE INT DEFAULT 0; /*用于游标循环终止的变量*/
DECLARE n INT DEFAULT 0; /*用于在single_hobby表中统计单一爱好行数的变量*/
DECLARE semi_hobby VARCHAR(60) DEFAULT ''; /*用于存取待处理的爱好字符串*/
DECLARE h VARCHAR(60) DEFAULT ''; /*用于从semi_hobby中提取出的首个单一爱好*/
DECLARE mycursor CURSOR FOR select UserHobby from username; /*游标定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1; /*游标循环终止条件设置*/
open mycursor; /*打开游标*/
REPEAT
FETCH mycursor INTO semi_hobby; /*游标取出待处理的字段*/
set semi_hobby = REPLACE(semi_hobby,' ',''); /*为了用于有空格但意义相同的单一爱好处理*/
IF NOT DONE THEN /*判断是否终止游标循环*/
WHILE(LOCATE(',',semi_hobby)<> 0) DO /*用于分割字符串的循环*/
SET h = SUBSTRING(semi_hobby,1,LOCATE(',',semi_hobby)-1); /*取出首个单一爱好字符串*/
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby = h; /*获取single_hobby表中已有的此单一爱好个数*/
IF NOT n THEN /*n为0即尚不存在*/
INSERT INTO single_hobby(Hobby) VALUES(h); /*插入到single_hobby表*/
/*向userhobby表中插入username表中含有此爱好的所有用户*/
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,h from username WHERE UserHobby LIKE CONCAT('%',h,'%');
END IF;
/*删除已处理的部分,将剩余的赋值给semi_hobby*/
SET semi_hobby = SUBSTR(semi_hobby,LOCATE(',',semi_hobby)+1,LENGTH(semi_hobby)-LOCATE(',',semi_hobby));
END WHILE;
/*对最后一个单一的爱好处理,类似以上h的操作,不同的是对于空或空格的处理*/
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby =semi_hobby;
IF NOT n THEN
INSERT INTO single_hobby(Hobby) VALUES(semi_hobby);
IF semi_hobby = '' THEN /*若字段的值原来为空或是若干个空格,都将插入userhobby表中*/
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby = '';
ELSE
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby LIKE CONCAT('%',semi_hobby,'%');
END IF;
END IF;
END IF;
UNTIL DONE END REPEAT; /*结束游标循环*/
close mycursor; /*关闭游标*/
END;
CALL PK(); /*执行存储过程*/
注意:以上代码其实结果有问题。
若是username表中Userhobby中有单个爱好为java和javascript的,但添加含有java爱好的用户到userhobby表时,会把含有javascript的也插入进去,而等到添加含有javascript爱好的用户时,用添加了一次,所以like语句中必须用分隔符来作为边界。
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby = h /*这样假如一个前后没有空格的字符串h,但表中已有一个前面或是后面有空格的字段,但其实是没有前后没有空格的字符串h的,但是n的值不是0,这是应为=运算符忽略字符串前后的空格,所以改为like运算符比较好*/
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby like h
修改后的代码参考如下:
DROP PROCEDURE IF EXISTS PK;
CREATE PROCEDURE PK()
BEGIN
DECLARE DONE INT DEFAULT 0; /*用于游标循环终止的变量*/
DECLARE n INT DEFAULT 0; /*用于在single_hobby表中统计单一爱好行数的变量*/
DECLARE semi_hobby VARCHAR(60) DEFAULT ''; /*用于存取待处理的爱好字符串*/
DECLARE s_hobby VARCHAR(60) DEFAULT '';
DECLARE h VARCHAR(60) DEFAULT ''; /*用于从semi_hobby中提取出的首个单一爱好*/
DECLARE mycursor CURSOR FOR select UserHobby from username; /*游标定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1; /*游标循环终止条件设置*/
open mycursor; /*打开游标*/
REPEAT
FETCH mycursor INTO semi_hobby; /*游标取出待处理的字段*/
IF NOT DONE THEN /*判断是否终止游标循环*/
set s_hobby = REPLACE(semi_hobby,' ',''); /*只在为空或是若干空格时使用,若在其他情况下,很难取到,如:%sql%,或包括含有sql-server单个爱好,而不是纯粹的sql*/
IF s_hobby = '' THEN /*单独处理空或是若干个空格*/
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby =s_hobby;
IF NOT n THEN
INSERT INTO single_hobby(Hobby) VALUES(s_hobby);
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby = '';
END IF;
ELSE
WHILE(LOCATE(',',semi_hobby)<> 0) DO /*用于分割字符串的循环*/
SET h = SUBSTRING(semi_hobby,1,LOCATE(',',semi_hobby)-1); /*取出首个单一爱好字符串*/
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby like h; /*获取single_hobby表中已有的此单一爱好个数*/
IF NOT n THEN /*n为0即尚不存在*/
INSERT INTO single_hobby(Hobby) VALUES(h); /*插入到single_hobby表*/
/*向userhobby表中插入username表中含有此爱好的所有用户,注意分四种情况:在开头后面有新的,在开头后面无新的,不在开头后面有新的,不在开头后面无新的*/
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,h from username WHERE UserHobby LIKE CONCAT('%,',h,',%');
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,h from username WHERE UserHobby LIKE CONCAT('%,',h);
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,h from username WHERE UserHobby LIKE CONCAT(h,',%');
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,h from username WHERE UserHobby LIKE h;
END IF;
/*删除已处理的部分,将剩余的赋值给semi_hobby*/
SET semi_hobby = SUBSTR(semi_hobby,LOCATE(',',semi_hobby)+1,LENGTH(semi_hobby)-LOCATE(',',semi_hobby));
END WHILE;
/*对最后一个单一的爱好处理,类似以上h的操作*/
SELECT COUNT(*) INTO n from single_hobby WHERE Hobby like semi_hobby;
IF NOT n THEN /*若是select 语句中semi_hobby改为replace(semi_hobby,' ',''),则userhobby表中semi_hobby字段会不含有空格如:' c'和'c'而一致*/
INSERT INTO single_hobby(Hobby) VALUES(semi_hobby);
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby LIKE CONCAT('%,',semi_hobby,',%');
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby LIKE CONCAT(semi_hobby,',%');
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby LIKE CONCAT('%,',semi_hobby);
INSERT INTO userhobby (id,name,semi_hobby) SELECT UserID,UserName,semi_hobby from username WHERE UserHobby LIKE semi_hobby;
END IF;
END IF;
END IF;
UNTIL DONE END REPEAT; /*结束游标循环*/
close mycursor; /*关闭游标*/
END;
CALL PK(); /*执行存储过程*/