找到你要的答案

Q:Condition-driven INSERT and UPDATE in a single query

Q:条件驱动在单个查询中插入和更新

I have a MySQL table with the following fields:

  1. ID
  2. PHONE
  3. NAME
  4. CITY
  5. COUNTRY

Using PHP, I am reading a comma separated dump of values off a text document, parsing the values and inserting records to the table. For reference, here's the code:

 <?php

    // Includes
        require_once 'PROJdbconn.php';

    // Read comma-separated text file
        $arrindx = 0;
        $i = 0;

        $filehandle = fopen(PROJCDUMPPATH.PROJCDUMPNAME,"rb");
        while (!feof($filehandle)){
            $parts = explode(',', fgets($filehandle));
            $contnames[$arrindx] = $parts['0'];
            $contnumbers[$arrindx] = preg_replace('/[^0-9]/','',$parts['1']);
            $arrindx += 1;
        }
        fclose($filehandle);
        $arrindx -= 1;
        $filehandle = NULL;
        $parts = NULL;

    // Build SQL query
        $sql = "INSERT INTO Contact_table (PHONE, NAME) VALUES ";
        for ($i = 0; $i < $arrindx; ++$i){
            $sql .= "('".$contnumbers[$i]."', '".$contnames[$i]."'),";
        }
        $i = NULL;
        $arrindx = NULL;
        $contnames = NULL;
        $contnumbers = NULL;
        $sql = substr($sql,0,strlen($sql)-1).";";

    // Connect to MySQL database
        $connect = dbconn(PROJHOST,PROJDB,PROJDBUSER,PROJDBPWD);

    // Execute SQL query
        $query = $connect->query($sql);
        $sql = NULL;
        $query = NULL;

    // Close connection to MySQL database
        $connect = NULL;



    ?>

Now, this code, as you can see, blindly dumps all records into the table. However, I need to modify the code logic as such:

Read text file and parse records into arrays (already doing)
For each record in text file
    Check if PHONE exists in the table
    If yes,
        For each field in the text file record
            If text file field != NULL
                Update corresponding field in table
            Else
                Skip
    If no,
        INSERT record (already doing)

I apologize if the logic isn't terribly clear, feel free to ask me if any aspect confuses you. So, I understand this logic would involve an insane number of SELECT, UPDATE, and INSERT queries, depending on the number of fields (I intend to add more fields in future) and records. Is there any way to either somehow morph them into a single query or leastwise optimize the code by minimizing the number of queries?

我用下面的字段有一个MySQL表:

  1. ID
  2. PHONE
  3. NAME
  4. CITY
  5. COUNTRY

使用PHP,我阅读一个逗号分隔的值从一堆文本文件,解析值和插入记录的表。仅供参考,这里是代码:

 <?php

    // Includes
        require_once 'PROJdbconn.php';

    // Read comma-separated text file
        $arrindx = 0;
        $i = 0;

        $filehandle = fopen(PROJCDUMPPATH.PROJCDUMPNAME,"rb");
        while (!feof($filehandle)){
            $parts = explode(',', fgets($filehandle));
            $contnames[$arrindx] = $parts['0'];
            $contnumbers[$arrindx] = preg_replace('/[^0-9]/','',$parts['1']);
            $arrindx += 1;
        }
        fclose($filehandle);
        $arrindx -= 1;
        $filehandle = NULL;
        $parts = NULL;

    // Build SQL query
        $sql = "INSERT INTO Contact_table (PHONE, NAME) VALUES ";
        for ($i = 0; $i < $arrindx; ++$i){
            $sql .= "('".$contnumbers[$i]."', '".$contnames[$i]."'),";
        }
        $i = NULL;
        $arrindx = NULL;
        $contnames = NULL;
        $contnumbers = NULL;
        $sql = substr($sql,0,strlen($sql)-1).";";

    // Connect to MySQL database
        $connect = dbconn(PROJHOST,PROJDB,PROJDBUSER,PROJDBPWD);

    // Execute SQL query
        $query = $connect->query($sql);
        $sql = NULL;
        $query = NULL;

    // Close connection to MySQL database
        $connect = NULL;



    ?>

现在,您可以看到,这个代码盲目地将所有记录转储到表中。但是,我需要修改代码逻辑:

Read text file and parse records into arrays (already doing)
For each record in text file
    Check if PHONE exists in the table
    If yes,
        For each field in the text file record
            If text file field != NULL
                Update corresponding field in table
            Else
                Skip
    If no,
        INSERT record (already doing)

如果逻辑并不十分清楚我道歉,随便问我任何方面的迷惑你。所以,我理解这个逻辑将涉及疯狂的选择,更新和插入查询,根据字段的数量(我打算在未来添加更多的字段)和记录。有什么办法可以在某种程度上变成一个查询或至少减少查询次数优化代码?

answer1: 回答1:

What you're trying to do is called an "upsert" (update/insert).

MySQL INSERT else if exists UPDATE

你要做的是被称为“UPSERT”(更新/插入)。

MySQL插入如果存在更新

php  mysql  database  performance