如何从命令行调用mysql存储过程,使用参数? [英] How to call a mysql stored procedure, with arguments, from command line?
问题描述
如何从命令行调用存储过程?
我有一个过程:
CREATE DEFINER =`root` @`localhost` PROCEDURE`insertEvent`(IN`dateTimeIN`DATETIME)
无SQL
BEGIN
SET @eventIDOut = NULL;
如果EXISTS(SELECT * FROM`events` WHERE`eventDate` = dateTimeIN)THEN
SELECT`eID` INTO @eventIDOut FROM`events` WHERE`eventDate` = dateTimeIN LIMIT 1;
ELSE
INSERT INTO`events`(`eventDate`)VALUES(dateTimeIN);
SET @eventIDOut = last_insert_id();
END IF;
SELECT CONCAT(@eventIDOut);
END
:
mysql> CALL insertEvent(2012.01.01 12:12:12);
结果:
错误1064(42000):您的SQL语法有错误;请检查对应于您的MySQL服务器版本的
手册,以获得正确的
语法,在第1行附近使用'.01 12:12:12)'
而且:
mysql> CALL insertEvent
- > 2012.01.01 12:12:12;
结果:
错误1064(42000):您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册,以获取在第2行2012.01.01 12:12:12附近使用的正确语法
解决方案使用引号括起日期:
mysql> CALL insertEvent('2012.01.01 12:12:12');
How can I call a stored procedure from command line?
I have a procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertEvent`(IN `dateTimeIN` DATETIME) NO SQL BEGIN SET @eventIDOut = NULL; IF EXISTS(SELECT * FROM `events` WHERE `eventDate` = dateTimeIN) THEN SELECT `eID` INTO @eventIDOut FROM `events` WHERE `eventDate` = dateTimeIN LIMIT 1; ELSE INSERT INTO `events` (`eventDate`) VALUES(dateTimeIN); SET @eventIDOut = last_insert_id(); END IF; SELECT CONCAT(@eventIDOut); END
I tried this:
mysql> CALL insertEvent(2012.01.01 12:12:12);
Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.01 12:12:12)' at line 1
And this:
mysql> CALL insertEvent
-> 2012.01.01 12:12:12;
Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2012.01.01 12:12:12' at line 2
解决方案With quotes around the date:
mysql> CALL insertEvent('2012.01.01 12:12:12');
这篇关于如何从命令行调用mysql存储过程,使用参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!