由于某种原因，PDO将id插入为0 - PDO inserts an id as 0 for some reason

所以我写了这个方法，为我的chrome插件(它做一个ajax请求来运行这个方法)，当它运行时，file_put_contents显示了插入的东西的id，但是当它到达insert ignore into songs时，它把0用于artist_id。我不知道为什么……有人能帮我找到我出错的地方吗?

<?php
public function saveLyrics($artist, $title, $lyric){
    $this->db->query("insert ignore into artists (artist_name) value (:artist)", array("artist"   => $artist));
    $artist_id = (int)$this->db->insertID();
    file_put_contents(__DIR__ . "/../process/page", "artist id: $artist_id");
    //return;
    if($artist_id == 0){
        $artist_id = (int)$this->db->getOne("select artist_id from artists where artist_name = :artist", array("artist" => $artist));
    }
    if($artist_id == 0){
        return false;
    }
    $this->db->query("insert ignore into songs (artist_id, song_name) values (:aid, :title)", array("aid"    => $artist_id, "title"  => $title));
    $song_id = (int)$this->db->insertID();
    if($song_id == 0){
        $song_id = (int)$this->db->getOne("select song_id from songs where artist_id = aid and song_name = :title", array("aid"   => $artist_id, "title" => $title));
    }
}

PDO包装:

<?php
/** 
 * @property PDO $pdo Description
 * @property PDOStatement $sql Description
 */
class DB{
    protected $sql = null;
    protected $pdo = null;
    public function connect(){
        $this->pdo = new PDO("mysql:dbname=envne;host=xxx", "xxx", "xxx");
    }
    public function query($query, $params = array()){
        if($this->pdo === null){
            $this->connect();
        }
        $this->sql = $this->pdo->prepare($query);
        foreach($params as $key => $value){
            $this->sql->bindParam($key, $value);
        }
        $this->sql->execute();
        if(!$this->sql)
            return false;
        return true;
    }
    public function insertID(){
        return (int)$this->pdo->lastInsertId();
    }
    public function getAll($query, $params = array()){
        $this->query($query, $params);
        return $this->sql->fetchAll();
    }
    public function getOne($query, $params = array()){
        $this->query($query, $params);
        return $this->sql->fetchColumn();
    }
}

艺术家:

mysql> describe artists;
+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| artist_id   | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| artist_name | char(50)         | YES  | UNI | NULL              |                |
| add_date    | timestamp        | YES  |     | CURRENT_TIMESTAMP |                |
+-------------+------------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)

歌曲

mysql> describe songs;
+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| song_id    | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| artist_id  | int(11) unsigned | YES  | MUL | NULL              |                |
| album_id   | int(11)          | YES  | MUL | NULL              |                |
| song_name  | char(50)         | YES  |     | NULL              |                |
| track_id   | int(11)          | YES  |     | NULL              |                |
| date_added | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+------------+------------------+------+-----+-------------------+----------------+
6 rows in set (0.01 sec)

我决定将Id直接放入查询中，这样就可以了。

$artist_id = (int)$this->db->insertID();
$this->db->query("insert ignore into songs (artist_id, song_name) values ($artist_id, :title)", array("title"  => $title));

另一种方法是用问号代替

$artist_id = (int)$this->db->insertID();
$this->db->query("insert ignore into songs (artist_id, song_name) values (?, ?)", array($artist_id, $title));

我刚刚遇到了同样的问题:即使ID字段设置为AUTO_INCRIMENT，新插入的项目也获得0的id。

我找到的解决方法和你的很相似。使用你的代码，这是我们得到的:

$this->db->query("insert ignore into songs (artist_id, song_name) values (LAST_INSERT_ID(), :title)", array("title"  => $title));

如您所见，我用SQL函数LAST_INSERT_ID()替换了$artist_id = (int)$this->db->insertID();和$artist_id。

我希望这能帮助到某个人。

您的占位符定义不正确:(您缺少冒号)我会这样做:

public function saveLyrics($artist, $title, $lyric){
    $this->db->query("insert ignore into artists (artist_name) value (:artist)", array(":artist"   => $artist));
    $artist_id = (int)$this->db->insertID();
    file_put_contents(__DIR__ . "/../process/page", "artist id: $artist_id");
    //return;
    if($artist_id == 0){
        $artist_id = (int)$this->db->getOne("select artist_id from artists where artist_name = :artist", array(":artist" => $artist));
        return false;
    }
    $this->db->query("insert ignore into songs (artist_id, song_name) values (:aid, :title)", array(":aid"=>$artist_id, ":title"=>$title));
    $song_id = (int)$this->db->insertID();
    if($song_id == 0){
        $song_id = (int)$this->db->getOne("select song_id from songs where artist_id = :aid and song_name = :title", array(":aid"=>$artist_id, ":title"=>$title));
    }
}

查看您的PDO-wrapper，您有以下代码:

if(!$this->sql)
    return false;

因此，您永远不会注意到实际的错误。我猜错误是关于占位符在这种情况下。(如果$this->db->query("insert ignore into songs (...失败，如果执行查询时出现错误，$song_id将为false)。

使用异常来捕获错误，这样会更好。

我还注意到:

$song_id = (int)$this->db->insertID();

将对值进行两次强制转换，首先在上面的代码中，然后在do - wrapper中的实际函数insertID()中。也许这也是一个值得考虑的问题。