哪位高手看一下下面这个函数是否会搞垮mysql数据库？-- ============================================================================
--
-- Public: Assigning a first free otrino to the new user
--
-- Return: json
--
-- {'err_code':'0','w':'x1','a':'x2','r':'x3','ringNum':'x4','cm':'x5',
-- 'cf':'x6','mapId':'x7','bldId':'x8','otrId':'x9,
-- 'mapFile':'x10','mapVer':'x11','ix':'x12','h':'x13','w':'x14',
-- 'stile_points':'x15','points':'x16'
--
-- x1 -
-- x2 -
-- x3 -
--
-- {'err_code':'y'} - y - error code from db_error_messages
--SELECT 'Creating init_new_user function..' AS Action;
DROP FUNCTION IF EXISTS init_new_user;
DELIMITER //
CREATE FUNCTION init_new_user (w INT,a INT,userId INT,rewardDay INT,
rewardType ENUM('k','c','t','i','l',''),rewardValue1 TEXT,
rewardValue2 TEXT,dbg BOOLEAN) RETURNS TEXT
BEGIN
-- Constants
DECLARE myname VARCHAR(64) DEFAULT 'init_new_user';
-- DECLARE php_cmd VARCHAR(255) DEFAULT '/usr/bin/php -f /opt/SFS_PRO_1.6.6/Server/mysql_udf/lib_mysqludf_php/sp_init_new_user.php';
DECLARE php_cmd VARCHAR(255) DEFAULT 'java -cp /opt/SFS_PRO_1.6.6/Server/mysql_udf/lib_mysqludf_java/lib_mysqludf_java.jar InitNewUser'; DECLARE otrino_object_type INT DEFAULT 1; -- from object_types
DECLARE clothes_obj_type INT DEFAULT 16; DECLARE regular_map_group_id INT DEFAULT 3; -- from map_tpl_groups
DECLARE reg INT DEFAULT 3; -- looking for new otrino in reg_id=3
DECLARE salt VARCHAR(32) DEFAULT 'icecream_mooncake'; DECLARE Err_php INT DEFAULT 14;
DECLARE Err_already_has_otrino INT DEFAULT 16;
DECLARE Err_cant_expand_region INT DEFAULT 34; DECLARE defaultStoreId INT DEFAULT 1;-- Variables
DECLARE php_arg_1,php_arg_2,php_ret,userFolder,ret,x,iClname,iName,
ringNum,mapRange,freeItems,c TEXT DEFAULT '';
DECLARE smap,emap,sz,plot_ix,res,mapVer,floorId,fmapVer,freeOtrinos,
free_otrino_id,free_otrObj_id,free_bld_id,mapId,iId,storeId,itemId,icatId,
reqKarmaLevel,newObjId,closetId,_swag INT DEFAULT 0;
DECLARE mapFile,res2,stile,stile_points,file_n TEXT;
DECLARE errCode TEXT DEFAULT '0';
DECLARE Dir ENUM('NW','N','NE','E','SE','S','SW','W');
DECLARE cfJsonName VARCHAR(16); DECLARE done,complexColor BOOL DEFAULT FALSE; DECLARE cur CURSOR FOR
SELECT DISTINCT cf_json_name FROM mv_cf_preview; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 1,myname,
CONCAT('-- BEGIN w=',w,',a=',a,',userId=',userId);
END IF;-- Make checks
SELECT uobj.object_id INTO free_otrObj_id
FROM user_objects uobj
JOIN otrinos otr
ON uobj.object_id=otr.object_id
WHERE uobj.user_id=userId; IF free_otrObj_id<>0 THEN
RETURN CONCAT('{\"err_code\":\"',Err_already_has_otrino,'\"}');
END IF;-- Find map with a free otrino
SELECT m.map_id,m.free_otrinos,
CONCAT('\"w\":\"',m.world_id,'\",\"a\":\"',m.area_id,'\",\"r\":\"',m.reg_id,
'\",\"ringNum\":\"',m.ringNum,'\",')
INTO mapId,freeOtrinos,ret
FROM maps m
JOIN map_tpls mt
ON m.map_tpl_id=mt.map_tpl_id
WHERE m.world_id=w AND m.area_id=a AND m.reg_id=reg AND m.is_enabled AND
mt.map_tpl_group_id=regular_map_group_id AND
m.free_otrinos>0
ORDER BY RAND()
LIMIT 1; IF freeOtrinos<1 THEN
IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 5,myname,CONCAT('No free otrinos, expanding');
END IF;-- SELECT ExpandRegion(w,a,reg,dbg) INTO res2;
-- SET ret = _make_SFS_room_json_str(w,a,reg,res2,dbg); SET res2 = ExpandRegion2(w,a,reg,dbg);
IF res2<>'' THEN
SET ringNum = CAST(SUBSTRING_INDEX(res2,',',1) AS UNSIGNED);
SET mapRange = SUBSTRING_INDEX(res2,',',-1);
SET mapId = SUBSTRING_INDEX(SUBSTRING_INDEX(res2,',',-1),':',1); SET ret = CONCAT('\"w\":\"',w,'\",\"a\":\"',a,
'\",\"r\":\"',reg,'\",\"ringNum\":\"',ringNum,'\",\"cm\":\"',
mapRange,'\"'); IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 10,myname,CONCAT('made expandReg, ret=',ret);
END IF;
ELSE
RETURN CONCAT('{\"err_code\":\"',Err_cant_expand_region,'\"}');
END IF; CALL correct_plot_prices(w,a,reg,dbg); ELSE
SET ret = CONCAT(ret,'\"cm\":\"\"'); -- dont need to enable map ring
IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 5,myname,
CONCAT('Found map with free otrino, map_id=',
mapId);
END IF;
END IF;-- Get first free otrino object id SELECT o.id,p.map_id,p.plot_ix,p.object_dir,
CASE o.level_id -- otrino size for each level
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
WHEN 5 THEN 25
WHEN 6 THEN 30
END
INTO free_otrObj_id,mapId,plot_ix,Dir,sz
FROM objects o
JOIN plots p
ON o.id=p.object_id
JOIN maps m
USING(map_id,area_id,world_id)
WHERE p.world_id=w AND p.area_id=a AND p.reg_id=reg AND m.map_id=mapId AND
o.type_id=otrino_object_type AND p.parent_id IS NULL AND
p.bound_with IS NULL AND
o.id NOT IN (SELECT object_id FROM user_objects)
LIMIT 1; IF free_otrObj_id<>0 THEN SELECT otrino_id,building_id
INTO free_otrino_id,free_bld_id
FROM otrinos
WHERE object_id=free_otrObj_id;-- Get 1st floor of the free otrino
SELECT (SELECT CONCAT(php_arg_1,'\"floor\":{\"f\":\"',
filename,'\",\"t\":\"',f.filename,
'\",\"fmapVer\":\"',f.file_ver,'\"},')
FROM otrino_floor_tpls
WHERE f_tpl_id=(SELECT f_tpl_id
FROM otrino_floors
WHERE floor_id=f.floor_id AND floorNum=1)), CONCAT(ret,',\"cf\":{\"fid\":\"',floor_id,
'\",\"fn\":\"', floorNum,'\",\"fname\":\"',
floorname,'\",\"ffile\":\"',
filename,'\"},'),
floor_id
INTO php_arg_1,ret,floorId
FROM floors f
WHERE building_id=free_bld_id AND floorNum=1; SELECT filename,file_ver+1
INTO mapFile,mapVer
FROM maps
WHERE world_id=w AND area_id=a AND reg_id=reg AND
map_id=mapId; IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 5,myname,
CONCAT('Found free otrino: free_otrino_id=',
free_otrino_id,',free_otrObj_id=',
free_otrObj_id,',free_bld_id=',free_bld_id,
',plot_ix=',plot_ix,',sz=',sz,',mapFile=',mapFile,
',mapVer=',mapVer,',php_arg_1=',php_arg_1,',ret=',ret
);
END IF;
--
-- Public: Assigning a first free otrino to the new user
--
-- Return: json
--
-- {'err_code':'0','w':'x1','a':'x2','r':'x3','ringNum':'x4','cm':'x5',
-- 'cf':'x6','mapId':'x7','bldId':'x8','otrId':'x9,
-- 'mapFile':'x10','mapVer':'x11','ix':'x12','h':'x13','w':'x14',
-- 'stile_points':'x15','points':'x16'
--
-- x1 -
-- x2 -
-- x3 -
--
-- {'err_code':'y'} - y - error code from db_error_messages
--SELECT 'Creating init_new_user function..' AS Action;
DROP FUNCTION IF EXISTS init_new_user;
DELIMITER //
CREATE FUNCTION init_new_user (w INT,a INT,userId INT,rewardDay INT,
rewardType ENUM('k','c','t','i','l',''),rewardValue1 TEXT,
rewardValue2 TEXT,dbg BOOLEAN) RETURNS TEXT
BEGIN
-- Constants
DECLARE myname VARCHAR(64) DEFAULT 'init_new_user';
-- DECLARE php_cmd VARCHAR(255) DEFAULT '/usr/bin/php -f /opt/SFS_PRO_1.6.6/Server/mysql_udf/lib_mysqludf_php/sp_init_new_user.php';
DECLARE php_cmd VARCHAR(255) DEFAULT 'java -cp /opt/SFS_PRO_1.6.6/Server/mysql_udf/lib_mysqludf_java/lib_mysqludf_java.jar InitNewUser'; DECLARE otrino_object_type INT DEFAULT 1; -- from object_types
DECLARE clothes_obj_type INT DEFAULT 16; DECLARE regular_map_group_id INT DEFAULT 3; -- from map_tpl_groups
DECLARE reg INT DEFAULT 3; -- looking for new otrino in reg_id=3
DECLARE salt VARCHAR(32) DEFAULT 'icecream_mooncake'; DECLARE Err_php INT DEFAULT 14;
DECLARE Err_already_has_otrino INT DEFAULT 16;
DECLARE Err_cant_expand_region INT DEFAULT 34; DECLARE defaultStoreId INT DEFAULT 1;-- Variables
DECLARE php_arg_1,php_arg_2,php_ret,userFolder,ret,x,iClname,iName,
ringNum,mapRange,freeItems,c TEXT DEFAULT '';
DECLARE smap,emap,sz,plot_ix,res,mapVer,floorId,fmapVer,freeOtrinos,
free_otrino_id,free_otrObj_id,free_bld_id,mapId,iId,storeId,itemId,icatId,
reqKarmaLevel,newObjId,closetId,_swag INT DEFAULT 0;
DECLARE mapFile,res2,stile,stile_points,file_n TEXT;
DECLARE errCode TEXT DEFAULT '0';
DECLARE Dir ENUM('NW','N','NE','E','SE','S','SW','W');
DECLARE cfJsonName VARCHAR(16); DECLARE done,complexColor BOOL DEFAULT FALSE; DECLARE cur CURSOR FOR
SELECT DISTINCT cf_json_name FROM mv_cf_preview; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 1,myname,
CONCAT('-- BEGIN w=',w,',a=',a,',userId=',userId);
END IF;-- Make checks
SELECT uobj.object_id INTO free_otrObj_id
FROM user_objects uobj
JOIN otrinos otr
ON uobj.object_id=otr.object_id
WHERE uobj.user_id=userId; IF free_otrObj_id<>0 THEN
RETURN CONCAT('{\"err_code\":\"',Err_already_has_otrino,'\"}');
END IF;-- Find map with a free otrino
SELECT m.map_id,m.free_otrinos,
CONCAT('\"w\":\"',m.world_id,'\",\"a\":\"',m.area_id,'\",\"r\":\"',m.reg_id,
'\",\"ringNum\":\"',m.ringNum,'\",')
INTO mapId,freeOtrinos,ret
FROM maps m
JOIN map_tpls mt
ON m.map_tpl_id=mt.map_tpl_id
WHERE m.world_id=w AND m.area_id=a AND m.reg_id=reg AND m.is_enabled AND
mt.map_tpl_group_id=regular_map_group_id AND
m.free_otrinos>0
ORDER BY RAND()
LIMIT 1; IF freeOtrinos<1 THEN
IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 5,myname,CONCAT('No free otrinos, expanding');
END IF;-- SELECT ExpandRegion(w,a,reg,dbg) INTO res2;
-- SET ret = _make_SFS_room_json_str(w,a,reg,res2,dbg); SET res2 = ExpandRegion2(w,a,reg,dbg);
IF res2<>'' THEN
SET ringNum = CAST(SUBSTRING_INDEX(res2,',',1) AS UNSIGNED);
SET mapRange = SUBSTRING_INDEX(res2,',',-1);
SET mapId = SUBSTRING_INDEX(SUBSTRING_INDEX(res2,',',-1),':',1); SET ret = CONCAT('\"w\":\"',w,'\",\"a\":\"',a,
'\",\"r\":\"',reg,'\",\"ringNum\":\"',ringNum,'\",\"cm\":\"',
mapRange,'\"'); IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 10,myname,CONCAT('made expandReg, ret=',ret);
END IF;
ELSE
RETURN CONCAT('{\"err_code\":\"',Err_cant_expand_region,'\"}');
END IF; CALL correct_plot_prices(w,a,reg,dbg); ELSE
SET ret = CONCAT(ret,'\"cm\":\"\"'); -- dont need to enable map ring
IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 5,myname,
CONCAT('Found map with free otrino, map_id=',
mapId);
END IF;
END IF;-- Get first free otrino object id SELECT o.id,p.map_id,p.plot_ix,p.object_dir,
CASE o.level_id -- otrino size for each level
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
WHEN 5 THEN 25
WHEN 6 THEN 30
END
INTO free_otrObj_id,mapId,plot_ix,Dir,sz
FROM objects o
JOIN plots p
ON o.id=p.object_id
JOIN maps m
USING(map_id,area_id,world_id)
WHERE p.world_id=w AND p.area_id=a AND p.reg_id=reg AND m.map_id=mapId AND
o.type_id=otrino_object_type AND p.parent_id IS NULL AND
p.bound_with IS NULL AND
o.id NOT IN (SELECT object_id FROM user_objects)
LIMIT 1; IF free_otrObj_id<>0 THEN SELECT otrino_id,building_id
INTO free_otrino_id,free_bld_id
FROM otrinos
WHERE object_id=free_otrObj_id;-- Get 1st floor of the free otrino
SELECT (SELECT CONCAT(php_arg_1,'\"floor\":{\"f\":\"',
filename,'\",\"t\":\"',f.filename,
'\",\"fmapVer\":\"',f.file_ver,'\"},')
FROM otrino_floor_tpls
WHERE f_tpl_id=(SELECT f_tpl_id
FROM otrino_floors
WHERE floor_id=f.floor_id AND floorNum=1)), CONCAT(ret,',\"cf\":{\"fid\":\"',floor_id,
'\",\"fn\":\"', floorNum,'\",\"fname\":\"',
floorname,'\",\"ffile\":\"',
filename,'\"},'),
floor_id
INTO php_arg_1,ret,floorId
FROM floors f
WHERE building_id=free_bld_id AND floorNum=1; SELECT filename,file_ver+1
INTO mapFile,mapVer
FROM maps
WHERE world_id=w AND area_id=a AND reg_id=reg AND
map_id=mapId; IF dbg THEN
INSERT INTO debug (severity,method,m)
SELECT 5,myname,
CONCAT('Found free otrino: free_otrino_id=',
free_otrino_id,',free_otrObj_id=',
free_otrObj_id,',free_bld_id=',free_bld_id,
',plot_ix=',plot_ix,',sz=',sz,',mapFile=',mapFile,
',mapVer=',mapVer,',php_arg_1=',php_arg_1,',ret=',ret
);
END IF;
INSERT INTO user_objects (user_id,object_id,status_id,start_time,creator)
SELECT userId,id,status_id,CURRENT_TIMESTAMP,myname
FROM objects
WHERE id=free_otrObj_id;-- Change map file-- Get otrino supertile and coords strings
SET stile = _get_otrino_outside_supertile(free_otrino_id,'json',dbg);
SET stile_points = _get_outrino_outside_points(free_otrino_id,'relative',dbg); SET userFolder=MD5(CONCAT(userId,salt)); SET freeItems=add_default_free_items(userId,floorId,Dir,dbg); SET php_arg_1 = CONCAT('\'{',php_arg_1,
'\"user_folder\":\"',
userFolder,'\",\"free_items\":\"',freeItems,'\",\"mapFile\":\"',
mapFile,'\",\"mapVer\":\"',mapVer,'\",\"ix\":',plot_ix,
',\"sz\":\"',sz,'\",\"stile\":',stile,',\"stile_points\":\"',
stile_points,'\"}\''); IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 20,myname,
CONCAT('-- php_cmd=',php_cmd
);
INSERT INTO debug (severity,method,m) SELECT 21,myname,
CONCAT('-- php_arg_1=',php_arg_1
);
END IF; SET php_ret = sys_eval(CONCAT(php_cmd,' ',php_arg_1));
-- SET php_ret = udf_init_new_user(php_arg_1); IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 24,myname,
CONCAT('-- php_ret=',php_ret
);
END IF; IF SUBSTR(php_ret,1,1)<>'#' THEN
CALL log_failure_msg(
CONCAT(myname,': ','UDF returns error '
));
SET errCode = Err_php;
ELSE
-- Update free otrinos counter
UPDATE maps SET
free_otrinos = free_otrinos - 1,
file_ver = mapVer,
modified=CURRENT_TIMESTAMP
WHERE world_id=w AND area_id=a AND reg_id=reg AND map_id=mapId;
-- Save user files folder name
UPDATE users SET
user_folder = userFolder,
lastLogin_vw=CURRENT_TIMESTAMP,
totalLogins_vw = totalLogins_vw+1,
modified=CURRENT_TIMESTAMP
WHERE user_id=userId;
INSERT INTO stat_logins (session_id,user_id,username,login,
is_vw_login,creator)
SELECT MD5(CURRENT_TIMESTAMP),userId,username,CURRENT_TIMESTAMP,
TRUE,myname
FROM users
WHERE user_id=userId; IF rewardType<>'' THEN
CALL _rewardUser(userId,rewardDay,rewardType,rewardValue1,
rewardValue2,dbg);
END IF; INSERT INTO debug (severity,method,m) SELECT 5,myname,
CONCAT('\"mapId\":\"',mapId,'\",\"bldId\":\"',free_bld_id,
'\",\"otrId\":\"',free_otrino_id,'\",\"stile_points\":\"',
stile_points,'\",\"points\":',SUBSTR(php_ret,2));
SET ret = CONCAT(ret,'\"mapId\":\"',mapId,'\",\"bldId\":\"',free_bld_id,'\",\"otrId\":\"',free_otrino_id,'\",\"mapFile\":\"',
mapFile,'\",\"mapVer\":\"',mapVer,'\",\"ix\":',plot_ix,',\"h\":\"',sz,'\",\"w\":\"',sz,'\",\"stile_points\":\"',
stile_points,'\",\"points\":',SUBSTR(php_ret,2));
--
-- Save first free clothes SET done=FALSE; OPEN cur;
Loop1: LOOP
FETCH cur INTO cfJsonName;
IF done THEN
CLOSE cur;
LEAVE Loop1;
END IF;
IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 10,myname,
CONCAT('cfJsonName=',cfJsonName);
END IF; SELECT si.id,si.store_id,si.item_id,si.icat_id,i.classname,
i.item_name,i.color,i.complex_color,i.k_level,si.swag
INTO iId,storeId,itemId,icatId,iClname,iName,c,
complexColor,reqKarmaLevel,_swag
FROM items i
JOIN store_items si USING(item_id,icat_id)
WHERE classname IN (SELECT cf_clname FROM mv_cf_preview
WHERE cftt_id=1 AND cf_json_name=cfJsonName AND
gender=(SELECT gender FROM users JOIN profiles
USING (profile_id) WHERE user_id=userId)) AND
store_id=defaultStoreId AND si.swag=0
ORDER BY RAND()
LIMIT 1;
IF FOUND_ROWS()=1 THEN
-- 1. Objects
INSERT INTO objects (level_id,classname,name,type_id,rgb,
bcPrice,btPrice,scPrice,stPrice,creator)
SELECT 1,iClname,iName,clothes_obj_type,
CASE WHEN complexColor THEN '' ELSE c END,
0,0,0,0,myname;
SET newObjId = LAST_INSERT_ID();
-- Saving complex colors
IF complexColor THEN
CALL save_uobj_attrs (newObjId,userId,icatId,
SUBSTRING_INDEX(SUBSTRING_INDEX(iClname,'_',4),'_',-1),
c,FALSE,dbg); -- manytime_use=FALSE
END IF;
IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 5,myname,
CONCAT('Created new clothes iClname=',iClname,
',object_id=',newObjId);
END IF;
-- 3. User_objects
INSERT INTO user_objects (user_id,object_id,start_time,
creator)
VALUES (userId,newObjId,CURRENT_TIMESTAMP,myname);
SELECT closet_id
INTO closetId
FROM user_closets
WHERE user_id =userId;
INSERT INTO user_closet_items (closet_id,user_id,object_id,
iid,store_id,icat_id,item_id,swag,color_str,on_me,
is_haircut,creator)
SELECT closetId,userId,newObjId,iId,storeId,icatId,itemId,
_swag,c,TRUE,(cfJsonName='hp'),myname; INSERT INTO user_features(user_id,object_id,cfcn_id,color,
creator)
SELECT userId,newObjId,cfcn_id,c,myname
FROM cfeature_clnames WHERE cf_clname=iClname; END IF;
END LOOP;
END IF;
END IF;
IF dbg THEN
INSERT INTO debug (severity,method,m) SELECT 1,myname,
CONCAT('-- END err_code=',errCode,',ret=',ret);
END IF;
RETURN CONCAT('{\"err_code\":\"',errCode,'\",',ret,'}');
END;
//
DELIMITER ;
，用WINRAR压缩
然后，给出验证的简单步骤，这样大家就省心了。比如执行什么的参数时，可能会导致你认为的把mysql搞垮。