gpt4 book ai didi

mysql - mysql存储过程中的预处理语句

转载 作者:行者123 更新时间:2023-11-29 00:27:26 27 4
gpt4 key购买 nike

我曾尝试构建一个 mysql 存储过程,但似乎有不同的错误,我只是试图从存储过程运行准备好的语句,以实现在表中动态插入一些数据。

任何人都可以检查并告诉我我的错误在哪里吗?

谢谢

    DELIMITER $$
CREATE PROCEDURE insert_tracking(IN deviceid VARCHAR(15),IN timing timestamp,IN valid tinyint(1),IN latitude double,IN longitude double,IN speed double,IN course double,IN power double,IN comando varchar(45))
BEGIN
DECLARE query VARCHAR(150);
SET query = "INSERT INTO tracking_? (device_id,time,valid,latitude,longitude,speed,course,power,command) VALUES (?,?,?,?,?,?,?,?,?)";
PREPARE stmt FROM query;
EXECUTE stmt USING deviceid,deviceid,timing,valid,latitude,longitude,speed,course,power,comando;
END $$

我也尝试过这种串联,但它不起作用。

    DELIMITER $$
CREATE PROCEDURE insert_tracking(IN deviceid VARCHAR(15),IN timing timestamp,IN valid tinyint(1),IN latitude double,IN longitude double,IN speed double,IN course double,IN power double,IN comando varchar(45))
BEGIN
DECLARE query VARCHAR(200);
SET query = CONCAT('INSERT INTO tracking_',deviceid,' (device_id,time,valid,latitude,longitude,speed,course,power,command) VALUES (?,?,?,?,?,?,?,?,?)');
PREPARE stmt FROM query;
EXECUTE stmt USING deviceid,timing,valid,latitude,longitude,speed,course,power,comando;
DEALLOCATE PREPARE stmt;
END $$

编辑后我有了这个,我测试了 concat 语句,它们分开工作,但是准备好的语句一直有问题,它不起作用。

    DELIMITER $$
CREATE PROCEDURE insert_tracking(IN deviceid VARCHAR(15),IN timing timestamp,IN valid tinyint(1),IN latitude double,IN longitude double,IN speed double,IN course double,IN power double,IN comando varchar(45))
BEGIN
SET @deviceid = deviceid;
SET @nsert = CONCAT('INSERT INTO tracking_',@deviceid);
SET @query = CONCAT(@nsert,' (device_id,time,valid,latitude,longitude,speed,course,power,command) VALUES ("?","?","?","?","?","?","?","?","?")');
SET @fquery = @query;
PREPARE stmt FROM @fquery;
EXECUTE stmt USING deviceid,timing,valid,latitude,longitude,speed,course,power,comando;
END $$

最佳答案

我相信这是因为表名和列名不能被准备好的语句中的参数替换。尝试获取完整的表名并测试是否要验证。

代码取自 ( http://dev.af83.com/2007/05/30/use-parameters-for-field-and-table-names-in-a-mysql-prepare-statement-to-create-truly-dynamic-sql.html) 归功于作者

这个函数可以允许动态表名和列名

DELIMITER //
DROP FUNCTION IF EXISTS substrCount//
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);//

DROP FUNCTION IF EXISTS charsplit//
CREATE FUNCTION charsplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');//

DROP FUNCTION IF EXISTS replacefirst//
CREATE FUNCTION replacefirst(haystack varchar(255), needle varchar(255),replacestr varchar(255)) returns varchar(255)
BEGIN
IF LOCATE(needle,haystack)>0 THEN
SET @replaced=concat(left(haystack,LOCATE(needle,haystack)-LENGTH(needle)),replacestr,right(haystack,LENGTH(haystack)-LOCATE(needle,haystack)));
ELSE
SET @replaced=haystack;
END IF;
RETURN @replaced;
END;//

DROP FUNCTION IF EXISTS properprepare//
CREATE FUNCTION properprepare(template varchar(255), args varchar(255)) returns varchar(255)
BEGIN
SET @i=0;
SET @numargs = substrCount(args,',');
WHILE @i <= @numargs DO
SET @i= @i+ 1;
SET template=replacefirst(template,'?',charsplit(args,',',@i));
END WHILE;
RETURN template;
END;//

DELIMITER ;

关于mysql - mysql存储过程中的预处理语句,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18326055/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com