两段表名为参数，查询和更新的MySQL语句
获得游戏数据块
1 -- ----------------------------
2 -- Procedure structure for `GetGameBlockData`
3 -- ----------------------------
4 DROP PROCEDURE IF EXISTS `GetGameBlockData`;
5 DELIMITER ;;
6 CREATE PROCEDURE `GetGameBlockData`(IN `inTable` varchar(64), IN `inID` bigint unsigned)
7 BEGIN
8 SET @RowCount = 0;
9 SET @SQLTXT = CONCAT("SELECT COUNT(*) INTO @RowCount FROM ", inTable, " WHERE `id`=", inID);
10 PREPARE statement FROM @SQLTXT;
11 EXECUTE statement;
12
13 IF @RowCount = 0 THEN
14 SELECT CAST(0 AS UNSIGNED INTEGER), CAST(0 AS UNSIGNED INTEGER);
15 ELSE
16 SET @SQLTXT = CONCAT("SELECT CAST(0 AS UNSIGNED INTEGER), `len`, `data` FROM ", inTable, " WHERE `id`=", inID);
17 PREPARE statement FROM @SQLTXT;
18 EXECUTE statement;
19 END IF;
20 END
21 ;;
22 DELIMITER ;
23 =================================================================================================
24 call GetGameBlockData("sa_game_1", 2);
25 =================================================================================================
更新游戏数据块
1 -- ----------------------------
2 -- Procedure structure for `SetGameBlockData`
3 -- ----------------------------
4 DROP PROCEDURE IF EXISTS `SetGameBlockData`;
5 DELIMITER ;;
6 CREATE PROCEDURE `SetGameBlockData`(IN `inTable` varchar(64), IN `inID` bigint unsigned, IN inLen int unsigned, IN `inData` blob)
7 BEGIN
8 SET @RowCount = 0;
9 SET @mydata = inData;
10
11 SET @SQLTXT = CONCAT("SELECT COUNT(*) INTO @RowCount FROM ", inTable, " WHERE `id`=", inID);
12 PREPARE statement FROM @SQLTXT;
13 EXECUTE statement;
14
15 IF @RowCount = 0 THEN
16 SET @SQLTXT = CONCAT("INSERT INTO ", inTable, " SET `id`=", inID, ", `len`=", inLen, ", `data`=?");
17 PREPARE statement FROM @SQLTXT;
18 EXECUTE statement USING @mydata;
19 ELSE
20 SET @SQLTXT = CONCAT("UPDATE ", inTable, " SET `len`=", inLen, ", `data`=? WHERE `id`=", inID);
21 PREPARE statement FROM @SQLTXT;
22 EXECUTE statement USING @mydata;
23 END IF;
24
25 SELECT CAST(0 AS UNSIGNED INTEGER);
26 END
27 ;;
28 DELIMITER ;
29
30 =================================================================================================
31 call SetGameBlockData("sa_game_1", 3, 7, "1234567");
32 =================================================================================================
