关于重复键更新的php也说1062：键“主”的重复条目

Question

+-------+---------+---------------+---------------------+---------------------+------------+
| id    | name    | description   | added_on            | last_updated        | department |
+-------+---------+---------------+---------------------+---------------------+------------+
| 1     | A       | Bill          | 2018-02-18 21:48:28 | 2018-02-18 15:08:34 | 1          |
| 2     | B       | SAKTHY        | 2018-06-21 12:49:30 | 2018-02-18 12:49:40 | 1          |
| 3     | C       |               | 2018-02-18 21:48:28 | 2018-02-18 21:48:28 | 1          |
+-------+---------+---------------+---------------------+---------------------+------------+

[
  {
    "tableName": "bank_accounts",
    "rows": []
  },
  {
    "tableName": "counters",
    "rows": [
      {
        "id": "2",
        "name": "B",
        "description": "SAKTHY",
        "added_on": "2018-06-21T12:49:30",
        "last_updated": "2018-02-18T12:49:40",
        "department": "1"
      },
      {
        "id": "5",
        "name": "E",
        "description": "SAKTHY2",
        "added_on": "2018-06-21T12:50:21",
        "last_updated": "2018-06-21T14:52:18",
        "department": "1"
      },
      {
        "id": "6",
        "name": "SAKTHY3",
        "description": "Sample Friday",
        "added_on": "2018-06-22T10:47:18",
        "last_updated": "2018-06-22T10:47:18",
        "department": "1"
      }
    ]
  },
  {
    "tableName": "customers",
    "rows": []
  }
]

要将记录插入或更新到联机数据库，需要使用这个php脚本（谢谢@Sloan Thrasher,@lovepreet-singh)。

<?php
    try
    {
        $connect = mysqli_connect("localhost", "username", "password", "database"); 
        $query = '';
        $table_data = '';
        $filename = "sample.json";

        $data = file_get_contents($filename);
        $array = json_decode($data, true); 

        foreach($array as $set) 
        {
            $tblName = $set['tableName'];
            if(sizeof($set['rows']) > 0) 
            {
                $query = '';
                $colList = array();
                $valList = array();
                //  Get list of column names
                foreach($set['rows'][0] as $colName => $dataval) 
                {
                    $colList[] = "`".$colName."`";
                }
                $query .= "INSERT INTO `".$tblName."` \n";
                $query .= "(".implode(",",$colList).")\nVALUES\n";
                //  Go through the rows for this table.
                foreach($set['rows'] as $idx => $row) 
                {
                    $colDataA = array();
                    //  Get the data values for this row.
                    foreach($row as $colName => $colData) 
                    {
                        $colDataA[] = "'".$colData."'";
                    }
                    $valList[] = "(".implode(",",$colDataA).")";
                }
                //  Add values to the query.
                $query .= implode(",\n",$valList)."\n";

                //  If id column present, add ON DUPLICATE KEY UPDATE clause
                if(in_array("id", $colList)) 
                {
                    $query .= "ON DUPLICATE KEY UPDATE\n\t SET ";
                    $tmp = array();
                    foreach($colList as $idx => $colName) 
                    {
                        //$tmp[] = $colName." = new.".$colName." ";
                        $tmp[] = $colName." = VALUE(".$colName.") ";    //  Changed this line to get value from current insert row data
                    }
                    $query .= implode(",",$tmp)."\n";
                } 
                else 
                {
                    echo "<p><b>`id`</b> column not found. <i>ON DUPLICATE KEY UPDATE</i> clause <b>NOT</b> added.</p>\n";
                    echo "<p>Columns Found:<pre>".print_r($colList, true)."</pre></p>\n";
                }
                echo "<p>Insert query:<pre>$query</pre></p>";
                $r = mysqli_query($connect, $query);  

                echo mysqli_errno($connect) . ": " . mysqli_error($connect) . "\n";

                echo "<h1>".mysqli_affected_rows($connect). " Rows appended in .$tblName.</h1>";
            } 
            else 
            {
                echo "<p>No rows to insert for .$tblName.</p>";
            }
        }
    } 

    catch(Exception $e)
    {   
        echo $e->getMessage();  
    }
?>

但我在浏览器中得到了以下SQL Echo。在这种情况下，联机数据库不会更新或插入新记录。

No rows to insert for .bank_accounts.

`id` column not found. ON DUPLICATE KEY UPDATE clause NOT added.

Columns Found:

Array
(
    [0] => `id`
    [1] => `name`
    [2] => `description`
    [3] => `added_on`
    [4] => `last_updated`
    [5] => `department`
)
Insert query:

INSERT INTO `counters` 
(`id`,`name`,`description`,`added_on`,`last_updated`,`department`)
VALUES
('2','B','SAKTHY','2018-06-21T12:49:30','2018-02-18T12:49:40','1'),
('5','E','SAKTHY2','2018-06-21T12:50:21','2018-06-21T14:52:18','1'),
('6','SAKTHY3','Sample Friday','2018-06-22T10:47:18','2018-06-22T10:47:18','1')

1062: Duplicate entry '2' for key 'PRIMARY'

-1 Rows appended in .counters.

No rows to insert for .customers.

Answer 1

您的问题是，在您的代码中，您有以下内容：

$colList[] = "`".$colName."`";

然后您将检查：

if(in_array("id", $colList))

当添加到数组时，在列名周围打勾，在in_array部分中不搜索列名。

$colList[] = $colName;

if(in_array("`id`", $colList))