多个插入不适用于 SQLite 3 [英] Multiple inserts not working with SQLite 3
问题描述
在 PHP 中使用带有 PDO 的 SQLite DB 的具体错误:
The specific error, using a SQLite DB with PDO in PHP:
致命错误:未捕获的异常 'PDOException' 带有消息 'SQLSTATE[HY000]: General error: 1 near ",": syntax error' in D:\Projects\2013\Stat collection plugin\stats\htdocs\index.php:8 Stack trace: #0 D:\Projects\2013\Stat collection plugin\stats\htdocs\index.php(8): PDO->exec('CREATE TABLE IF...') #1 {main} 抛出到 D:\Projects\2013\Stat collection plugin\stats\htdocs\index.php 第 8 行
代码:
$dbSchema = file_get_contents('../schema.sql');
$PDO = new PDO('sqlite:../stats.db');
$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$PDO->exec($dbSchema);
这是我的架构.当我只有第一个 CREATE TABLE 语句时，错误似乎就在那里，但如果我在以下语句中犯了同样的错误，如果你告诉我，我将不胜感激.提前致谢！
Here's my schema. The error seems to be there when I just have the first CREATE TABLE statement, but if I've made the same error in the following statements I'd appreciate it if you told me. Thanks in advance!
CREATE TABLE IF NOT EXISTS game (
id INTEGER PRIMARY KEY AUTOINCREMENT,
serverName STRING NOT NULL,
map STRING NOT NULL,
winner INTEGER NOT NULL,
gameMode STRING NOT NULL,
controlPoints INTEGER,
setupGate BOOLEAN,
capsRed INTEGER,
capsBlue INTEGER,
winsRed INTEGER,
winsBlue INTEGER,
CONSTRAINT game_winner_teamTypes_id FOREIGN KEY (winner) REFERENCES teamTypes(id)
);
CREATE TABLE IF NOT EXISTS player (
id INTEGER PRIMARY KEY AUTOINCREMENT,
gameId INTEGER NOT NULL,
name STRING NOT NULL,
team INTEGER NOT NULL,
class INTEGER NOT NULL,
queueJump BOOLEAN NOT NULL,
CONSTRAINT player_gameId_game_id FOREIGN KEY (gameId) REFERENCES game(id),
CONSTRAINT player_team_teamTypes_id FOREIGN KEY (team) REFERENCES teamTypes(id),
CONSTRAINT player_class_classTypes_id FOREIGN KEY (class) REFERENCES classTypes(id)
);
CREATE TABLE IF NOT EXISTS stat (
playerId INTEGER NOT NULL,
type INTEGER NOT NULL,
value INTEGER NOT NULL,
CONSTRAINT stat_playerId_player_id FOREIGN KEY (playerId) REFERENCES player(id),
CONSTRAINT stat_type_statTypes_id FOREIGN KEY (type) REFERENCES statTypes(id)
);
CREATE TABLE IF NOT EXISTS teamTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO teamTypes(id, name) VALUES (0, 'Red'), (1, 'Blue'), (2, 'Spectator');
CREATE TABLE IF NOT EXISTS classTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO classTypes(id, name) VALUES (0, 'Runner'), (1, 'Rocketman'), (2, 'Rifleman'), (3, 'Detonator'), (4, 'Healer'), (5, 'Constructor'), (6, 'Overweight'), (7, 'Infiltrator'), (8, 'Firebug'), (9, 'Querly');
CREATE TABLE IF NOT EXISTS statTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO statTypes(id, name) VALUES (0, 'Kills'), (1, 'Deaths'), (2, 'Caps'), (3, 'Assists'), (4, 'Destruction'), (5, 'Stabs'), (6, 'Healing'), (7, 'Defenses'), (8, 'Invulns'), (9, 'Bonus'), (10, 'Dominations'), (11, 'Revenge'), (12, 'Points');
推荐答案
原来问题是我有多个 VALUES 组 (VALUES (1,2,3), (4,5,6)).
Turns out that the problem was that I had multiple VALUES groups (VALUES (1,2,3), (4,5,6)).
允许使用一条语句插入多行的功能 (INSERT INTO foobar VALUES (1,2), (3,4);) 而不是 INSERT INTO foobar VALUES (1,2);INSERT INTO foobar VALUES (3,4); 已在 SQLite 3.7.11 中添加:
The feature enabling multiple rows to be inserted with one statement (INSERT INTO foobar VALUES (1,2), (3,4);) instead of INSERT INTO foobar VALUES (1,2); INSERT INTO foobar VALUES (3,4); was added in SQLite 3.7.11:
2012-03-20 (3.7.11)
2012-03-20 (3.7.11)
Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause.
http://www.sqlite.org/changes.html#version_3_7_11
但是，我的 SQLite 版本较旧.根据我的 phpinfo() 输出:
However, my SQLite version is older. According to my phpinfo() output:
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.7.7.1
因此我无法使用它.
当我改变这最后一部分时，
When I changed this last part,
CREATE TABLE IF NOT EXISTS teamTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO teamTypes(id, name) VALUES (0, 'Red'), (1, 'Blue'), (2, 'Spectator');
CREATE TABLE IF NOT EXISTS classTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO classTypes(id, name) VALUES (0, 'Runner'), (1, 'Rocketman'), (2, 'Rifleman'), (3, 'Detonator'), (4, 'Healer'), (5, 'Constructor'), (6, 'Overweight'), (7, 'Infiltrator'), (8, 'Firebug'), (9, 'Querly');
CREATE TABLE IF NOT EXISTS statTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO statTypes(id, name) VALUES (0, 'Kills'), (1, 'Deaths'), (2, 'Caps'), (3, 'Assists'), (4, 'Destruction'), (5, 'Stabs'), (6, 'Healing'), (7, 'Defenses'), (8, 'Invulns'), (9, 'Bonus'), (10, 'Dominations'), (11, 'Revenge'), (12, 'Points');
为此，
CREATE TABLE IF NOT EXISTS teamTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO teamTypes(id, name) VALUES (0, 'Red');
INSERT OR IGNORE INTO teamTypes(id, name) VALUES (1, 'Blue');
INSERT OR IGNORE INTO teamTypes(id, name) VALUES (2, 'Spectator');
CREATE TABLE IF NOT EXISTS classTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO classTypes(id, name) VALUES (0, 'Runner');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (1, 'Rocketman');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (2, 'Rifleman');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (3, 'Detonator');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (4, 'Healer');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (5, 'Constructor');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (6, 'Overweight');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (7, 'Infiltrator');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (8, 'Firebug');
INSERT OR IGNORE INTO classTypes(id, name) VALUES (9, 'Querly');
CREATE TABLE IF NOT EXISTS statTypes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING NOT NULL
);
INSERT OR IGNORE INTO statTypes(id, name) VALUES (0, 'Kills');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (1, 'Deaths');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (2, 'Caps');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (3, 'Assists');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (4, 'Destruction');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (5, 'Stabs');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (6, 'Healing');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (7, 'Defenses');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (8, 'Invulns');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (9, 'Bonus');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (10, 'Dominations');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (11, 'Revenge');
INSERT OR IGNORE INTO statTypes(id, name) VALUES (12, 'Points');
问题解决了.
这篇关于多个插入不适用于 SQLite 3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
