php - 如何在 mysql 中使用带有 OR 子句的 'INSERT INTO IGNORE' 或 'NOT EXISTS'

Question

我有一个名为equipment 的表，表架构如下

+----+-----------------+-------------------+--+----------------------+
| id |    equipment    | cat_id(Default:1) |  | is_active(Default:1) |
+----+-----------------+-------------------+--+----------------------+
|  1 | Air Compressor  |                 1 |  |                    1 |
|  2 | Plate Compactor |                 1 |  |                    1 |
|  3 | Hammer          |                 1 |  |                    1 |
|  4 | TNT             |                 1 |  |                    1 |
+----+-----------------+-------------------+--+----------------------+

我想“如果 id 和 equipment 在表中不存在，则将记录插入表中”。我已经使用以下代码完成了

        /* equipment is an associative array 
            Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [name] => Air Compressor
                    )

                [1] => Array
                    (
                        [id] => 2
                        [name] => Plate Compactor
                    )

                [2] => Array
                    (
                        [id] => 3
                        [name] => Hammer
                    )
            )
        */
        foreach ($equipment as $key)
        {
            $name = $key['name'];
            $id = $key['id'];
            $equipment_query = $this->conn->prepare("INSERT IGNORE INTO equipment_master (id,equipment) VALUES (:equipment_id,:equipment_name)");
            $equipment_query->bindParam('equipment_name', $name);
            $equipment_query->bindParam('equipment_id', $id);
            $equipment_query->execute();
        }

但是插入行时的问题是，我不想在查询中使用 id 字段，因为它是 AUTO INCREMENTAL 和 Primary Key.

如果我尝试插入 id=>4 equipment=>Helmet，就会出现问题。这条记录将不会被插入，因为 id=>4 已经在表中。

有没有办法检查 id 是否已经存在于表中，如果不存在则插入该行。我尝试使用 NOT EXISTS 查询，但我的代码似乎重复插入每一行(准确地说是 4 行)

        foreach ($equipment as $key)
        {
            $name = $key['name'];
            $id = $key['id'];

            $equipment_query = $this->conn->prepare("INSERT INTO equipment_master (equipment) SELECT :equipment_name FROM equipment_master AS tmp WHERE NOT EXISTS (SELECT id FROM equipment_master WHERE id =:equipment_id OR equipment=:equipment_name)");
            $equipment_query->bindParam('equipment_name', $name);
            $equipment_query->bindParam('equipment_id', $id);
            $equipment_query->execute();
        }

非常感谢任何帮助。谢谢:)

Answer 1

1. 在字段:设备上创建唯一索引

   ALTER TABLE equipment ADD UNIQUE equipment_unique (equipment);

2. 忽略错误插入:

   foreach ($equipment as $key)
   {
       $name = $key['name'];
       $equipment_query = $this->conn->prepare("INSERT IGNORE INTO equipment_master (equipment) VALUES (:equipment_name)");
       $equipment_query->bindParam('equipment_name', $name);
       $equipment_query->execute();
   }
   

但是如果你想替换具有相同 id 的记录，那么你可以使用:

foreach ($equipment as $key)
{
    $name = $key['name'];
    $id = $key['id'];

    $equipment_query = $this->conn->prepare("REPLACE INTO equipment_master (id, equipment) VALUES (:equipment_id, :equipment_name)");
    $equipment_query->bindParam('equipment_name', $name);
    $equipment_query->bindParam('equipment_id', $id);
    $equipment_query->execute();
}