java - om.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : You have an error in your SQL syntax;

Question

我正在尝试创建 2 个表，但我不完全明白哪里出了问题。这显然是一个语法错误，而且我对 SQL 不太熟悉，有人可以向我解释一下我在这里做错了什么吗？

 String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

        String query2 = "CREATE TABLE IF NOT EXISTS " + secondTableName + " (" +
                "team_name VARCHAR(255) NOT NULL," +
                " team_password VARCHAR(255) NOT NULL" +
                " BOOLEAN friendly_fire NOT NULL," +
                " VARCHAR(255) hq," +
                " VARCHAR(255) rally," +
                " PRIMARY KEY (team_name))";

这是我的堆栈跟踪:

WARN com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'manager NOT NULL, BOOLEAN team_chat NOT NULL, PRIMARY KEY (uuid))' at line 1

用于执行的代码:

    private void createTables(String tableName, String secondTableName) throws SQLException {
    try {
        checkConnection();
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {

        String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

        String query2 = "CREATE TABLE IF NOT EXISTS " + secondTableName + " (" +
                "team_name VARCHAR(255) NOT NULL," +
                " team_password VARCHAR(255) NOT NULL" +
                " BOOLEAN friendly_fire NOT NULL," +
                " VARCHAR(255) hq," +
                " VARCHAR(255) rally," +
                " PRIMARY KEY (team_name))";

        PreparedStatement preparedStatement = connection.prepareStatement(query);
        PreparedStatement preparedStatement2 = connection.prepareStatement(query2);

        preparedStatement.execute();
        preparedStatement2.execute();
        main.getLogger().log(Level.INFO, "Tables " + tableName + " and " + secondTableName + " were successfully created.");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        close();
    }

}

Answer 1

问题是表定义中存在 MAX。 VARCHAR 需要一个数字作为参数，因此您需要将 MAX 定义为变量并传递它(从 Java)，或者只需将 MAX 替换为数字，例如:

String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR( + " + MAX + ") NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

或

String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

如果您不知道最大长度，则可以使用LONGTEXT 作为类型。

此外，我们需要修复type和列名称的顺序，它需要是manager BOOLEAN而不是BOOLEAN manager，例如:

    String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " manager BOOLEAN NOT NULL," +
                " team_chat BOOLEAN NOT NULL," +
                " PRIMARY KEY (uuid))";

    String query2 = "CREATE TABLE IF NOT EXISTS " + secondTableName + " (" +
            "team_name VARCHAR(255) NOT NULL," +
            " team_password VARCHAR(255) NOT NULL" +
            " friendly_fire BOOLEAN  NOT NULL," +
            " hq VARCHAR(255)," +
            " rally VARCHAR(255)," +
            " PRIMARY KEY (team_name))";