MySQL5.7存储过程错误

1sbrub3j 于 2021-06-21 发布在 Mysql

关注(0)|答案(4)|浏览(377)

我试图用以下格式创建存储过程：分隔符$$

CREATE PROCEDURE `CreateInsertLocation` (tableName VARCHAR(255),ts BIGINT(20),systs INT(20),lat FLOAT,lon FLOAT)
BEGIN
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = 'DB'
    AND table_name = tableName;

    IF FoundCount = 1 THEN SET @sql = CONCAT('INSERT INTO ',tableName,'
        (timestamp, lattitude, longitude,systime) 
    VALUES
        (','ts','lat','lon','systs')');
PREPARE stmt FROM @sql; 
    EXECUTE stmt;

ELSE 
    SET @sql = CONCAT('CREATE TABLE `',tableName,'`(
        `id` bigint(20) NOT NULL DEFAULT '0', 
        `timestamp` bigint(20) NOT NULL DEFAULT '0',  
        `lattitude` float NOT NULL DEFAULT '0', 
        `longitude` float NOT NULL DEFAULT '0',
        `systime` bigint(20) DEFAULT NULL, 
        KEY `LocIdx` (`vtuId`,`timestamp`),
        KEY `SysIdx` (`vtuId`,`systime`), 
        PRIMARY KEY (id)'); 
    PREPARE stmt FROM @sql; 
    EXECUTE stmt;

    SET @sql = CONCAT('INSERT INTO ',tableName,'
            (timestamp, lattitude, longitude,systime) 
        VALUES
            (','ts','lat','lon','systs')');
    PREPARE stmt FROM @sql2; 
        EXECUTE stmt;

END 
$$

DELIMITER ;

--当我试图在mysql 5.7中执行这个查询时，我得到了以下错误

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 '');
  PREPARE stmt FROM @sql; 
     EXECUTE stmt;

   ELSE SET @sql = CONCAT' at line 10

有人能帮忙改进这个程序吗？

mysql stored-procedures stored-functions java-stored-procedures

来源：https://stackoverflow.com/questions/49981501/mysql-5-7-stored-procedure-error

4条答案

按热度按时间

bvhaajcl1#

在标记的注解中，此代码缺少要完成的引号 concat 正确地

$$
CREATE PROCEDURE `CreateInsertLocation` (tableName VARCHAR(255),ts BIGINT(20),systs INT(20),lat FLOAT,lon FLOAT)
BEGIN
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = 'DB'
    AND table_name = tableName;

    IF FoundCount = 1 THEN SET @sql = CONCAT('INSERT INTO ',tableName,'
        (timestamp, lattitude, longitude,systime) 
    VALUES
        (','ts','lat','lon','systs',')'); /* Error in this line */
PREPARE stmt FROM @sql; 
    EXECUTE stmt;

ELSE 
    SET @sql = CONCAT('CREATE TABLE `',tableName,'`(
        `id` bigint(20) NOT NULL DEFAULT '0', 
        `timestamp` bigint(20) NOT NULL DEFAULT '0',  
        `lattitude` float NOT NULL DEFAULT '0', 
        `longitude` float NOT NULL DEFAULT '0',
        `systime` bigint(20) DEFAULT NULL, 
        KEY `LocIdx` (`vtuId`,`timestamp`),
        KEY `SysIdx` (`vtuId`,`systime`), 
        PRIMARY KEY (id)'); 
    PREPARE stmt FROM @sql; 
    EXECUTE stmt;

    SET @sql = CONCAT('INSERT INTO ',tableName,'
            (timestamp, lattitude, longitude,systime) 
        VALUES
            (','ts','lat','lon','systs')');
    PREPARE stmt FROM @sql2; 
        EXECUTE stmt;

END 
$$

DELIMITER ;

赞(0）回复(0）举报 2021-06-21

w46czmvw2#

这应该管用。注意单引号的顺序，因为它可能会变得棘手。

DELIMITER $$
CREATE PROCEDURE `CreateInsertLocation` (tableName VARCHAR(255),ts BIGINT(20),systs INT(20),lat FLOAT,lon FLOAT)
    BEGIN
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = 'DB'
    AND table_name = tableName;

    IF FoundCount = 1 THEN 
        SET @sql = CONCAT('INSERT INTO ',tableName,' (timestamp, lattitude, longitude,systime) VALUES (',ts, ',', lat, ',', lon, ',', systs, ')' );

        PREPARE stmt FROM @sql; 
        EXECUTE stmt;

    ELSE 
        SET @sql = CONCAT('CREATE TABLE `',tableName,'` (
        `id` bigint(20) NOT NULL DEFAULT 0, 
        `timestamp` bigint(20) NOT NULL DEFAULT 0,  
        `lattitude` float NOT NULL DEFAULT 0, 
        `longitude` float NOT NULL DEFAULT 0,
        `systime` bigint(20) DEFAULT NULL, 
        KEY `LocIdx` (`vtuId`,`timestamp`),
        KEY `SysIdx` (`vtuId`,`systime`), 
        PRIMARY KEY (id) )' ); 

        PREPARE stmt FROM @sql; 
        EXECUTE stmt;
    END IF;

    SET @sql = CONCAT('INSERT INTO ',tableName,' (timestamp, lattitude, longitude,systime) VALUES (',ts, ',', lat , ',', lon, ',', systs, ')');

    PREPARE stmt FROM @sql; 
        EXECUTE stmt;

END 
$$

DELIMITER ;

赞(0）回复(0）举报 2021-06-21

wfveoks03#

create schema mine;
use mine;
delimiter $$
CREATE PROCEDURE `CreateInsertLocation` (tableName VARCHAR(255),ts BIGINT(20),systs INT(20),lat FLOAT,lon FLOAT)
BEGIN
    DECLARE FoundCount INT;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.tables
    WHERE table_schema = 'DB'
    AND table_name = tableName;

    IF FoundCount = 1 THEN SET @sql = CONCAT('INSERT INTO ',tableName,'
        (timestamp, lattitude, longitude,systime) 
    VALUES
        (','ts','lat','lon','systs\')');
PREPARE stmt FROM @sql; 
    EXECUTE stmt;

ELSE 
    SET @sql = CONCAT('CREATE TABLE ',tableName,'(
        `id` bigint(20) NOT NULL DEFAULT \'0\', 
        `timestamp` bigint(20) NOT NULL DEFAULT \'0\',  
        `lattitude` float NOT NULL DEFAULT \'0\', 
        `longitude` float NOT NULL DEFAULT \'0\',
        `systime` bigint(20) DEFAULT NULL, 
        KEY `LocIdx` (`vtuId`,`timestamp`),
        KEY `SysIdx` (`vtuId`,`systime`), 
        PRIMARY KEY (id)'); 
    PREPARE stmt FROM @sql; 
    EXECUTE stmt;

    SET @sql = CONCAT('INSERT INTO ',tableName,'
            (timestamp, lattitude, longitude,systime) 
        VALUES
            (','ts','lat','lon','systs\')');
    PREPARE stmt FROM @sql2; 
        EXECUTE stmt;

END if;
end
$$
DELIMITER ;

你可以试试上面的代码；
首先，你需要避开所有的单引号 ' 你忘了关闭endif。

赞(0）回复(0）举报 2021-06-21

w6mmgewl4#

对于create table语句的concat字符串和其中的默认值，使用单引号（'）。
将默认值放在双引号（“）中，对其进行转义（'），或者删除它们，因为它们都是数字类型。
edit：insert语句的参数也被放在引号中，看起来也不对。

赞(0）回复(0）举报 2021-06-21