mysql5 存储过程运行期间是否会根据实际情况部分重编译

Submitted by caramon on 周二, 2007/11/13 - 11:15

不好意思，注册了段时间，一直在向大家学习，前几天遇到个小问题，一直无法解决，想到了这里，还希望可以得到帮助
经常在网上看到一些关于MYSQL5 存储过程内将表名做为参数传递进存储过程以提高存储过程灵活度的帖子，当然，其中也提到了，如果使用EXEC这类方法的时候很有可能会因为MYSQL每次调用存储过程都会将之重新编译而导致提高了灵活度而降低了运行效率的。
但是每篇文章都没有提及到底是这个存储过程是整体被重新编译，还是说只是存储过程中的例如 EXEC “SELECT ***"中双引号部分被重新编译。如果有知道的能否指点下，麻烦大家了

CREATE DEFINER=`root`@`localhost` PROCEDURE `mytest`(IN tablename TEXT,out p_sErrorMsg char(50))
BEGIN

DECLARE m_sTempStr char(200) default '';
DECLARE m_sTempId char(30) default '';
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ROLLBACK; SET p_sErrorMsg = 'NOT FOUND'; END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLEXCEPTION'; END;
DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLWARNING'; END;
START TRANSACTION;
SET @m_sTempStr = CONCAT("insert into ",tablename," values(1,1)");
PREPARE STMT FROM @m_sTempStr;
EXECUTE STMT;
COMMIT;
END

Taxonomy upgrade extras:

yejr

周三, 2007/11/14 - 16:17

应该是全部重新编译的,否则不会有存储过程不支持query cache的说法.不过这个问题,在5.1好像不复存在了?

MySQL方案、培训、支持
MySQL 用户组

caramon

周三, 2007/11/14 - 16:42

老兄你所说的5.1不存在这个问题是指有的存储过程不支持QUERY CACHE？还是指的重新编译呀？另外还发现个小问题，不知道该如何解决，呵呵，也想顺便麻烦下，我在一个存储过程中，进行了一些UPDATE ，INSERT操作后，去调用了另外一个存储过程，无论被调用的存储过程是否成功，前面所进行的UPDATE，INSERT操作都会被自动隐性的COMMIT，这种情况是否是因为呼叫第二个存储过程的语句本身造成的呀

yejr

周三, 2007/11/14 - 20:52

1. 指支持query cache
2. 贴代码先。

MySQL方案、培训、支持
MySQL 用户组

caramon

周三, 2007/11/14 - 23:38

-- MySQL dump 10.10
--
-- Host: localhost Database: test1
-- ------------------------------------------------------
-- Server version 5.0.27-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `test1`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test1`;

--
-- Table structure for table `ad_community`
--

DROP TABLE IF EXISTS `access_list`;
CREATE TABLE `access_list` (
`user_id` int(10) unsigned NOT NULL,
`access_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
`access_sum` bigint(20) unsigned NOT NULL,
`access_type` int(10) unsigned NOT NULL,
`access_product` int(10) unsigned NOT NULL,
`access_id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`access_id`),
KEY `Index_2` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=GBK;

--
-- Dumping data for table `access_list`
--

LOCK TABLES `access_list` WRITE;
/*!40000 ALTER TABLE `access_list` DISABLE KEYS */;
/*!40000 ALTER TABLE `access_list` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`user_id` int(10) unsigned NOT NULL,
`sum_gold` bigint(20) unsigned NOT NULL default '0',
`sum_silver` int(10) unsigned NOT NULL default '0',
`credit` int(10) unsigned NOT NULL,
`onlock` int(10) unsigned NOT NULL,
`loan_sum` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=GBK;

DROP TABLE IF EXISTS `ad_community`;
CREATE TABLE `ad_community` (
`community_id` int(10) unsigned NOT NULL,
`ad_column` int(10) unsigned NOT NULL,
`ad_block` int(10) unsigned NOT NULL,
`ad_type` int(10) unsigned NOT NULL,
`ad_info` mediumtext NOT NULL,
`ad_url` varchar(200) NOT NULL,
`ad_tip` varchar(100) NOT NULL,
`ad_id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY USING BTREE (`ad_id`),
KEY `Index_2` (`community_id`,`ad_column`,`ad_block`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `ad_community`
--

LOCK TABLES `ad_community` WRITE;
/*!40000 ALTER TABLE `ad_community` DISABLE KEYS */;
INSERT INTO `ad_community` VALUES (1,1,1,1,'newinfo18','newurl18','newtip',1),

(1,1,2,1,'http://www.xxx.net','http://www.xxx.net','tip',2),(1,1,3,1,'http://www.xxx.net','http://www.xxx.net','tip',3),

(1,1,4,1,'http://www.xxx.net','http://www.xxx.net','tip',4),(1,1,5,1,'http://www.xxx.net','http://www.xxx.net','tip',5),

(1,1,6,1,'http://www.xxx.net','http://www.xxx.net','tip',6),(1,1,7,1,'http://www.xxx.net','http://www.xxx.net','tip',7);
/*!40000 ALTER TABLE `ad_community` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `ad_community_blotters`
--

DROP TABLE IF EXISTS `ad_community_blotters`;
CREATE TABLE `ad_community_blotters` (
`community_id` int(10) unsigned NOT NULL,
`ad_column` int(10) unsigned NOT NULL,
`ad_block` int(10) unsigned NOT NULL,
`ad_type` int(10) unsigned NOT NULL,
`ad_info` mediumtext NOT NULL,
`ad_url` varchar(200) NOT NULL,
`ad_tip` varchar(100) NOT NULL,
`ad_publishtime` bigint(20) NOT NULL,
`ad_id` int(10) unsigned NOT NULL,
`blotters_id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY USING BTREE (`blotters_id`),
KEY `Index_2` (`community_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `ad_community_blotters`
--

LOCK TABLES `ad_community_blotters` WRITE;
/*!40000 ALTER TABLE `ad_community_blotters` DISABLE KEYS */;
INSERT INTO `ad_community_blotters` VALUES (1,1,1,1,'newinfo3','newurl3','newtip',1195028480,1,3),

(1,1,1,1,'newinfo4','newurl4','newtip',1195028603,1,4),(1,1,1,1,'newinfo5','newurl5','newtip',1195028695,1,5),

(1,1,1,1,'newinfo11','newurl11','newtip',1195031145,1,11),(1,1,1,1,'newinfo12','newurl12','newtip',1195031215,1,12),

(1,1,1,1,'newinfo18','newurl18','newtip',1195031456,1,13);
/*!40000 ALTER TABLE `ad_community_blotters` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'test1'
--
DELIMITER ;;
/*!50003 DROP PROCEDURE IF EXISTS `ad_init` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `ad_init`(IN p_iCommunitId INT,out p_sErrorMsg

char(50))
BEGIN

DECLARE m_iCount INT DEFAULT 0;

DECLARE m_iCredit INT DEFAULT 0;

DECLARE m_iLoansum INT DEFAULT 0;

DECLARE m_sTempStr char(30) default '';

DECLARE m_sTempId char(30) default '';

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ROLLBACK; SET p_sErrorMsg = 'NOT FOUND'; END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLEXCEPTION'; END;

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLWARNING'; END;

START TRANSACTION;

INSERT INTO

ad_community (community_id,ad_column,ad_block,ad_type,ad_info,ad_url,ad_tip)

VALUES

(p_iCommunitId,1,1,1,"http://www.xxx.net","http://www.xxx.net","tip"),

(p_iCommunitId,1,2,1,"http://www.xxx.net","http://www.xxx.net","tip"),

(p_iCommunitId,1,3,1,"http://www.xxx.net","http://www.xxx.net","tip"),

(p_iCommunitId,1,4,1,"http://www.xxx.net","http://www.xxx.net","tip"),

(p_iCommunitId,1,5,1,"http://www.xxx.net","http://www.xxx.net","tip"),

(p_iCommunitId,1,6,1,"http://www.xxx.net","http://www.xxx.net","tip"),

(p_iCommunitId,1,7,1,"http://www.xxx.net","http://www.xxx.net","tip");

SET m_iCount = ROW_COUNT();

IF m_iCount = 7 THEN

SET p_sErrorMsg = "ok";

ELSE

SET p_sErrorMsg = m_iCount;

END IF;

END */;;

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `spanmoney` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `spanmoney`(IN p_iUserid INT,IN p_iProductid INT

,IN p_iMoney INT,out p_sErrorMsg char(50))
BEGIN

DECLARE m_iCount INT DEFAULT 0;

DECLARE m_iCredit INT DEFAULT 0;

DECLARE m_sTempStr char(30) default '';

DECLARE m_sTempId char(30) default '';

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ROLLBACK; SET p_sErrorMsg = 'NOT FOUND'; END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLEXCEPTION'; END;

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLWARNING'; END;

START TRANSACTION;

SELECT user_id FROM account WHERE sum_gold >= p_iMoney and user_id = p_iUserid;

SET m_iCount = FOUND_ROWS();

IF m_iCount = 1 THEN

update account set sum_gold = sum_gold - p_iMoney WHERE sum_gold >= p_iMoney and user_id = p_iUserid;

SET m_iCount = ROW_COUNT();

IF m_iCount = 1 THEN

INSERT INTO access_list

(user_id,access_time,access_sum,access_type,access_product)

VALUES

(p_iUserid,UNIX_timestamp(now()),p_iMoney,1,p_iProductid);

SET m_iCount = ROW_COUNT();

IF m_iCount = 1 THEN

COMMIT;

SET p_sErrorMsg = "ok";

ELSE

ROLLBACK;

SET p_sErrorMsg = "-3";

END IF;

ELSE

SET p_sErrorMsg = "-2";

ROLLBACK;

END IF;

ELSE

SET p_sErrorMsg = "-1";

END IF;

END */;;

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `ad_publish` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `ad_publish`(IN p_iAdId INT,IN p_iCommunitId

INT,IN p_iAdColumn INT,IN p_iAdBlock INT,IN p_iAdType INT,IN P_sAdInfo char(200),IN p_sAdUrl char(200),IN p_sAdTip char

(100),out p_sErrorMsg char(50))
BEGIN

DECLARE m_iCount INT DEFAULT 0;

DECLARE m_iCredit INT DEFAULT 0;

DECLARE m_iLoansum INT DEFAULT 0;

DECLARE m_sTempStr char(30) default '';

DECLARE m_sTempId char(30) default '';

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ROLLBACK; SET p_sErrorMsg = 'NOT FOUND'; END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLEXCEPTION'; END;

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLWARNING'; END;

START TRANSACTION;

CALL spanmoney(1,6,5,@sErrorMsg);

select @sErrorMsg INTO m_sTempStr;

IF m_sTempStr = "ok" THEN

UPDATE ad_community

SET ad_column = p_iAdColumn,ad_block = p_iAdBlock,

ad_type=p_iAdType,ad_info = P_sAdInfo,ad_url = p_sAdUrl,

ad_tip = p_sAdTip

WHERE

ad_id = p_iAdId AND community_id = p_iCommunitId;

SET m_iCount = ROW_COUNT();

IF m_iCount = 1 THEN

INSERT INTO ad_community_blotters

(community_id,ad_column,ad_block,ad_type,ad_info,ad_url,ad_tip,ad_publishtime,ad_id)

VALUES

(p_iCommunitId,p_iAdColumn,p_iAdBlock,p_iAdType,P_sAdInfo,p_sAdUrl,p_sAdTip,unix_timestamp(now

()),p_iAdId);

SET m_iCount = ROW_COUNT();

IF m_iCount = 1 THEN

COMMIT;

SET p_sErrorMsg = "ok";

ELSE

SET p_sErrorMsg = "insert error";

END IF;

ELSE

SET p_sErrorMsg = "update:not found";

ROLLBACK;

END IF;

ELSE

ROLLBACK;

SET p_sErrorMsg = concat("span error:",m_sTempStr);

END IF;

END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `ad_publish_old` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `ad_publish_old`(IN p_iAdId INT,IN p_iCommunitId

INT,IN p_iAdColumn INT,IN p_iAdBlock INT,IN p_iAdType INT,IN P_sAdInfo char(200),IN p_sAdUrl char(200),IN p_sAdTip char

(100),out p_sErrorMsg char(50))
BEGIN

DECLARE m_iCount INT DEFAULT 0;

DECLARE m_iCredit INT DEFAULT 0;

DECLARE m_iLoansum INT DEFAULT 0;

DECLARE m_sTempStr char(30) default '';

DECLARE m_sTempId char(30) default '';

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN ROLLBACK; SET p_sErrorMsg = 'NOT FOUND'; END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLEXCEPTION'; END;

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SET p_sErrorMsg = 'SQLWARNING'; END;

START TRANSACTION;

set AUTOCOMMIT =0;

set @autocommit = 0;

UPDATE ad_community

SET ad_column = p_iAdColumn,ad_block = p_iAdBlock,

ad_type=p_iAdType,ad_info = P_sAdInfo,ad_url = p_sAdUrl,

ad_tip = p_sAdTip

WHERE

ad_id = p_iAdId AND community_id = p_iCommunitId;

SET m_iCount = ROW_COUNT();

IF m_iCount = 1 THEN

INSERT INTO ad_community_blotters

(community_id,ad_column,ad_block,ad_type,ad_info,ad_url,ad_tip,ad_publishtime,ad_id)

VALUES

(p_iCommunitId,p_iAdColumn,p_iAdBlock,p_iAdType,P_sAdInfo,p_sAdUrl,p_sAdTip,unix_timestamp(now

()),p_iAdId);

SET m_iCount = ROW_COUNT();

IF m_iCount = 1 THEN

CALL spanmoney(1,1,5,@sErrorMsg);

select @sErrorMsg INTO m_sTempStr;

IF m_sTempStr = "ok" THEN

SET p_sErrorMsg = "ok";

ELSE

ROLLBACK;

SET p_sErrorMsg = concat("span error:",m_sTempStr);

END IF;

ELSE

SET p_sErrorMsg = "insert error";

END IF;

ELSE

SET p_sErrorMsg = "update:not found";

ROLLBACK;

END IF;

END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `test` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `test`(out testmsg TEXT)
BEGIN

set testmsg="1";

END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-11-14 10:11:49

yejr

周日, 2007/11/18 - 20:56

你肯定没仔细看手册，看 "12.4.3. Statements That Cause an Implicit Commit" 这节。

MySQL方案、培训、支持
MySQL 用户组