时间戳

首页 > 解决方案 > MySQL 连接抛出“给 mysql_stmt_precheck 的未知准备语句处理程序 (0)”错误

mysql - MySQL 连接抛出“给 mysql_stmt_precheck 的未知准备语句处理程序 (0)”错误

问题描述

我创建了一个 macOS 应用程序来学习和测试本地 MySQL 连接。

我通过 Homebrew 安装了 MySQL。

我可以从我的应用程序连接到本地主机。

我尝试使用不存在的数据库名称连接服务器，MySQL 抛出Unknown database 'test'

当我使用现有数据库名称时，我收到此错误：

Unknown prepared statement handler (0) given to mysql_stmt_precheck

我的连接代码：

import PerfectHTTP
import PerfectHTTPServer
import PerfectMySQL
import Foundation

public class DB {

    let host = "127.0.0.1"
    let user = "root"
    let password = "12345678"
    let database = "pets"
   
    func databaseConnect(host: String, user: String, password: String, db: String) -> MySQL {
        
        let mysql = MySQL() // Create an instance of MySQL to work with
        
        let connected = mysql.connect(host: host, user: user, password: password, db: db)
        
        guard connected else {
            // verify that we have connected successfully
            print(mysql.errorMessage())
            return mysql
        }
        
        return mysql
    }
    
    public func insertGame(title: String, description: String, createdDate: String){
    
        // Connect to our database
        var db = databaseConnect(host: host, user: user, password: password, db: database)
        
        defer {
            db.close() //This defer block makes sure we terminate the connection once finished, regardless of the result
        }
        
        // Create the statement we wish to execute
        let statement = MySQLStmt(db)
        let insert = "INSERT INTO game(id, title, description, release_date) VALUES (\(statement.insertId()), '\(title)', '\(description)', '\(createdDate)');"
        _ = statement.prepare(statement: insert)
        
        // Run the query
        let querySuccess = statement.execute()
        
        // Check that our query was successfuly, otherwise return out
        guard querySuccess else {
            print(db.errorMessage())
            return
        }
        
        print("Insert successful!");
    }
    
}

标签： mysqlswift

解决方案

您不应该在语句本身中使用insertId（这只是调用mysql_insert_id） 。INSERT该函数获取先前执行的INSERT语句的 id，并且由于没有先前的语句，它将失败。

您应该简单地从语句中省略AUTO_INCREMENT列INSERT，让 MySQL 自己设置自动递增值。这显然假设您将其定义为自动递增列，例如

在此处输入图像描述

现在，如果在 之后，INSERT如果您需要自动递增键使用的值，那么您可以调用insertId. 例如，

func databaseConnect(host: String, user: String, password: String, db: String) -> MySQL? {
    let mysql = MySQL() // Create an instance of MySQL to work with

    let connected = mysql.connect(host: host, user: user, password: password, db: db)

    // verify that we have connected successfully
    guard connected else {
        print(mysql.errorCode(), mysql.errorMessage())
        return nil
    }

    return mysql
}

@discardableResult
public func insertGame(title: String, description: String, createdDate: String) -> UInt? {
    // Connect to our database
    guard let db = databaseConnect(host: host, user: user, password: password, db: database) else {
        return nil
    }

    // Create the statement we wish to execute
    let statement = MySQLStmt(db)
    let insert = "INSERT INTO game (title, description, release_date) VALUES (?, ?, ?);"
    guard statement.prepare(statement: insert) else {
        print(db.errorMessage())
        return nil
    }

    // bind values to the ? in the prepared SQL
    statement.bindParam(title)
    statement.bindParam(description)
    statement.bindParam(createdDate)

    // Check that our query was successfuly, otherwise return out
    guard statement.execute() else {
        print(db.errorMessage())
        return nil
    }

    // if you really need the ID, this is where you'd call `insertId`, perhaps returning it in case the caller would need it
    let id = statement.insertId()
    return id
}

上述代码片段中包含的其他一些不相关的问题：

  • 我建议不要使用字符串插值来构建 SQL。如果标题是“Finnegan's Wake”，名称中的撇号会弄乱 SQL，过早终止该字符串值。理想情况下，您会将值绑定到上面的占位符，或者至少使用一些转义逻辑。您问题中的 SQL 将容易受到 SQL 注入攻击和/或需要转义的字符串的语法错误。

  • 我不会建议这种_ = statement.prepare(...)模式。返回值的全部意义在于告诉你它是否成功。如果prepare失败，您将在此处检查错误消息并退出该功能。

  • 我建议仅在与服务器连接成功的情况下继续。在您之前的问题中，您报告了第二条“MySQL 服务器已消失”消息。这仅仅是因为您在连接失败时尝试与 MySQL 进行交互，并且无论如何您都在继续。

推荐阅读