mysql quick快速存储

Quick Save 

参数:in in_sTableName varchar(50),  in in_nUserId int(4), in in_block BLOB(2000)

BEGIN

    #Routine body goes here...

    #CREATE TABLE IF NOT EXISTS in_sTableName(

    #    UserId INT NOT NULL,

    #    Block BLOB,

    #    PRIMARY KEY (UserId)

    #);

    START TRANSACTION;

    SET @sCTable = CONCAT('CREATE TABLE IF NOT EXISTS ', in_sTableName,

        "(UserId INT(11) NOT NULL, Block BLOB, PRIMARY KEY(UserId))ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;");

  PREPARE stmt FROM @sCTable;

  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

    SET @sSel = CONCAT('SELECT COUNT(*) INTO @nCnt FROM ', in_sTableName, ' WHERE UserId=', in_nUserId,';');

    PREPARE stmt FROM @sSel;

  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

    IF @nCnt = 0 THEN

        SET @sInsert = CONCAT('INSERT INTO ', in_sTableName, '(UserId, Block) VALUES (', in_nUserId, ",'", in_block,"');");

        PREPARE stmt FROM @sInsert;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;

    ELSE

        SET @sUpdate = CONCAT('UPDATE ', in_sTableName, " SET Block ='", in_block, "' WHERE UserId =", in_nUserId, ';');

        PREPARE stmt FROM @sUpdate;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;

    END IF;

    COMMIT;

END

Quick_Load

参数:in in_sTableName varchar(50),  in in_nUserId int(4)

BEGIN

    #Routine body goes here...

    START TRANSACTION;

    SET @sCTable = CONCAT('CREATE TABLE IF NOT EXISTS ', in_sTableName,

        "(UserId INT(11) NOT NULL, Block BLOB, PRIMARY KEY(UserId))ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;");

  PREPARE stmt FROM @sCTable;

  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

    SET @sSel = CONCAT('SELECT BLOCK FROM ', in_sTableName, ' WHERE UserId=', in_nUserId,';');

    PREPARE stmt FROM @sSel;

  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

    COMMIT;

END