使用update语句时出现java MySQL错误：“您的SQL语法有错误…”

Question

我从MYSQL服务器收到此错误：

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 ''0' = 0, '1' = 0, '2' = 0, '3' = 0, '4' = 0, '5' = 0, '6' = 0, '7' = 0, '8' = 0,' at line 1

以下是有关update语句的代码：

PreparedStatement getLocations = con.prepareStatement(sql);

ResultSet adjacentSpots = getLocations.executeQuery();

if (adjacentSpots.next()){ 
    int freqAti;
    int total_freq = adjacentSpots.getInt("total_freq");
    newfreqs[0] += total_freq;
    for (int i = 0; i < freqs.length-1; i++) {
        freqAti = adjacentSpots.getInt(""+i);
        newfreqs[i+1] += freqAti;
    }

    sql = "update _frequent_routes set total_freq = " + newfreqs[0];

    for (int i = 1; i < freqs.length; i++) {
        int iAdjusted = i-1; 
        sql += ", '" + iAdjusted + "' = " + newfreqs[i];
    }

    sql += " where device_id= '" + deviceID + "' and intersection1= '" + 
    intersectionName1 + "' and intersection2='" + intersectionName2 + "'"; 

    PreparedStatement updateSpots = con.prepareStatement(sql);

    updateSpots.executeUpdate();
}

例如：

update _frequent_routes set total_freq = 2, 0 = 0, 1 = 0, 2 = 0, 3 = 0, 4 = 0, 5 = 0, 6 = 0, 7 = 0, 8 = 0, 9 = 0, 10 = 0, 11 = 0, 12 = 0, 13 = 0, 14 = 0, 15 = 2, 16 = 0, 17 = 0, 18 = 0, 19 = 0, 20 = 0, 21 = 0, 22 = 0, 23 = 0 where device_id= 'some string' and intersection1= 'some string' and intersection2='some string'

我相信在匹配数据表中列的名称和类型时不会出错。有人能在我的update语句中发现语法错误吗

Answer 1

如果您的列名确实是示例中显示的数字，那么这可能就是问题的根源。Mysql列名不能完全由数字组成，除非使用倒勾（`）来引用它们

来自the docs：（也请参见here。）

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

所以如果你在这一行把单引号换成反勾号sql += ", '" + iAdjusted + "' = " + newfreqs[i];，你应该很乐意去

Answer 2

之前的答案是：

From the docs: (See [here][1] as well.)

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

事实上，MYSQL似乎根本不允许在查询中使用纯数字标识符。只要我在查询中指定这些标识符，就会出现语法错误，不管它是否被引用。但是，如果我没有指定列名，比如在INSERT语句中，我可以说，“INSERT into _FRENCENT_routes values（一些值，一些值，'一些字符串'…）”它将写入数据库而不会导致任何错误

因此，一个简单的解决方案是将这些仅数字字段的名称更改为仅非数字字段。在这种情况下，我可以：

update _frequent_routes set total_freq = 94, slot0 = 4, slot1 = 2, slot2 = 6, slot3 = 2, slot4 = 6, slot5 = 4, slot6 = 6, slot7 = 6, slot8 = 8, slot9 = 2, slot10 = 0, slot11 = 2, slot12 = 0, slot13 = 8, slot14 = 0, slot15 = 4, slot16 = 4, slot17 = 4, slot18 = 2, slot19 = 0, slot20 = 4, slot21 = 4, slot22 = 6, slot23 = 10 where device_id= 'some string' and intersection1= 'some string' and intersection2='some string'

并相应地更改字段的名称