单个查询中的条件驱动INSERT和UPDATE

I have a MySQL table with the following fields:

我有一个包含以下字段的MySQL表:

ID
PHONE
NAME
CITY
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,我正在从文本文档中读取逗号分隔的值转储,解析值并将记录插入表中。供参考,这是代码:

 <?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?

如果逻辑不是非常清楚,我很抱歉,请随时问我是否有任何方面让您感到困惑。所以,我理解这个逻辑会涉及疯狂的SELECT,UPDATE和INSERT查询,具体取决于字段数量(我打算将来添加更多字段)和记录。有没有办法以某种方式将它们变形为单个查询或通过最小化查询数量来最小化优化代码?

1 个解决方案

#1

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

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

MySQL INSERT else if exists UPDATE

MySQL INSERT else如果存在UPDATE

#1