java - '1' 中的未知列 'field list'

Question

我正在尝试将数据插入我的停止表 stops 表以及正在创建的 route 表，但程序无法将数据插入停止表，我收到此错误:

Unknown column '1' in 'field list'

感谢任何帮助。

    stt.execute("CREATE TABLE IF NOT EXISTS routes"
             + "(route_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, "
             + "route INT(11) NOT NULL)" );
    stt.execute("INSERT INTO routes(route) VALUES"
            + "(1),"
            + "(9)");

    stt.execute("CREATE TABLE IF NOT EXISTS stops"
            + "(stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, "
            + " name varchar(30) NOT NULL, "
            + " lat double(10,6) NOT NULL, "
            + " longi double(10,6)NOT NULL,"
            + "route_id INT, FOREIGN KEY fk_route_id(route_id) REFERENCES routes(route_id) )" );

    stt.execute("INSERT INTO stops(route_id, name,  lat, longi) VALUES"
            + "(`1`, 'Alex allee', '83.868937', '18.665545' ),"                     
            + "(`1`,'city street', '83.840642', '18.701246' )," 
            + "(`2`,'apple street', '83.740642', '18.761246' ),"
            + "(`2`,'wall street', '83.868256', '18.669520' )");

Answer 1

对于转义字符串，您必须使用单引号。列名或表名需要反引号。对于数字，没有必要的转义字符。

因为您的 route_id 值通过反引号转义，mysql 将其解释为列名。删除它们，您的查询将起作用。

stt.execute("INSERT INTO stops(route_id, name,  lat, longi) VALUES"
            + "(1, 'Alex allee', 83.868937, 18.665545 ),"                     
            + "(1,'city street', 83.840642, 18.701246 )," 
            + "(9,'apple street', 83.740642, 18.761246 ),"
            + "(9,'wall street', 83.868256, 18.669520 )");