时间戳

首页 > 解决方案 > 最大长度的sql语句

mysql - 最大长度的sql语句

问题描述

我有一个节点 js 应用程序，我通过 express 连接到 mysql 数据库。

现在我正在尝试锁定一个表，调用一个函数并解锁这些表。

当我打印字符串时一切正常，但是当我在 connection.query(sqlStatement) 中调用它时，字符串会以某种方式被切片？

这是节点js函数：

exports.participateTournament = function(req, res){
    const {pid, tid} = req.body;
    let sqlStatement = `lock Tables Tournament_Participant WRITE, Tournament_Approved READ; 
                        select participate_Tournament('${pid}', '${tid}') as 'result'; 
                        unlock tables;`;

    console.log(sqlStatement);

    connection.query(sqlStatement, function(error, rows){
        if(error){
            console.log(error.message);
            return res.status(400).send();
        } else {
            return res.status(200).send(rows[0].result);
        }
    })
};

当我打印出字符串时，这是输出

lock Tables Tournament_Participant WRITE, Tournament_Approved READ; 

select participate_Tournament('0780b926a41bd17877894771841e6179', 'a9f0e61a137d86aa9db53465e0801612') as 'result'; 

unlock tables;

但我从 mysql 收到此错误消息：

ER_PARSE_ERROR: 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 'select participate_Tournament('0780
b926a41bd17877894771841e6179', 'a9f0e61a137d8' at line 2

这是participant_Tournament 函数：我需要检查是否达到了锦标赛参与者的最大限制

create function participate_Tournament(
    pidP varchar(64),
    tidP varchar(64)
)
returns smallint
BEGIN
    declare par_amount int;
    declare max_amount int;

    select count(TID) into par_amount from Tournament_Participant where TID = tidP;
    select max_participants into max_amount from Tournament_Approved where TID = tidP;

    if(par_amount < max_amount) then
        insert into Tournament_Participant(TID,PID) values(tidP, pidP);
        return 1; #true
    end if;

    return 0;
end;
```

Thanks in Advance.

标签： mysqlnode.jsexpress

解决方案

我现在已经这样解决了，对我有用

    const {pid, tid} = req.body;
    let tmpResult;

    connection.beginTransaction(function(err){
        if(err){
            res.status(400).send();
            return;
        }
        sqlStatement = `lock Tables Tournament_Participant WRITE, Tournament_Approved READ;`;
        connection.query(sqlStatement, function(err, rows){
            if(err){
                console.log(err);
                res.status(400).send();
            }else{
                sqlStatement = `select participate_Tournament('${pid}', '${tid}') as 'result';`;

                connection.query(sqlStatement, function(err, rows){
                    if(err){
                        console.log(err);
                        res.status(400).send();
                    }else{
                        tmpResult = rows[0].result;

                        sqlStatement = `unlock tables;`;
                        connection.query(sqlStatement, function(err, rows){
                            if(err){
                                console.log(err);
                                res.status(400).send();
                            }else{
                                connection.commit(function(err){
                                    if(err){
                                        connection.rollback(function(){
                                            res.status(400).send();
                                        });
                                    }else{
                                        res.status(200).send(tmpResult.toString());
                                    }
                                })
                            }
                        })
                    }
                })
            }
        })
    })
};

推荐阅读