PHP, MYSQl将一个数据库表复制到另一个数据库表，并将空值替换为空。

I am copying one complete database to other database. Every thing is working except one problem. Say one of my table has following structure

我正在将一个完整的数据库复制到另一个数据库。除了一个问题，所有的事情都在起作用。假设我的一个表具有以下结构

 id -> Integer PK AI
 state_id -> Integer (Default NULL)
 sell_id  -> Integer (Default NULL)

Now this table can have NULL in state_id and sell_id. So I query SELECT to this table, an empty string is return against all NULL values. But when I try to insert these values to other database table, it generates error as empty string cannot be given in integer column. Like select query return data some thing like following array

现在这个表在state_id和sell_id中可以有NULL。所以我查询选择这个表，一个空字符串返回所有空值。但是当我尝试将这些值插入到其他数据库表时，它会产生错误，因为不能在integer列中给出空字符串。比如select query返回一些数据比如跟随数组

array(
  'id' => 1,
  'state_id' => '',
  'sell_id' => ''
)

So I will try to insert this array into new table, it will generate error. So I want to replace all '' with NULL. I have tried following by setting NULL to all empty values

我将尝试将这个数组插入到新表中，它会产生错误。所以我想用NULL替换所有的。我尝试将NULL设置为所有空值

  $array = array_map(function($v){
                    return $v == '' ? NULL : $v;
                },$array);

But this also prints '' to all NULL values. This is my complete Code

但这也会打印“到所有空值”。这是我的完整代码

$result = mysqli_query($dbConnection1, "SELECT * FROM $table ORDER BY $sortedBy DESC limit $noOfRecords");

$columns = '';
$values = '';
if($result && mysqli_num_rows($result)) {
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) ) {
        $array = array_values($row);
        $array = array_map(function($v){
            return $v == '' ? NULL : $v;
        },$array);

        $columns = " (".implode(", ",array_keys($row)).") ";
        $values .= " ('".implode("', '",$array)."'),";
    }
    $values = rtrim($values, ",");
    mysqli_query($dbConnection2, "INSERT INTO $table $columns VALUES $values");
}

But this code isn't working for above stated scenario. The Insert query is printed as

但是这段代码不适用于上述场景。插入查询打印为

 INSERT INTO table (id, state_id, sell_id) VALUES (1, '', '') , (2, '', '')

But I want it to be like

但我希望它是这样的

 INSERT INTO table (id, state_id, sell_id) VALUES (1, NULL, NULL) , (2, NULL, NULL)

Can any body let me know how can I achieve that in my scenario.

有没有人能告诉我如何在我的场景中实现这一点?

2 个解决方案

#1

This could easily be done with a single query:

这很容易通过一个查询完成:

INSERT INTO newtable (newfield1, newfield2, etc...)
    SELECT oldfield1, IF(oldfield2="", NULL, oldfield2), etc...
    FROM oldtable

The select can correct/adjust any of the field values on-the-fly.

选择可以即时修改/调整任何字段值。

And note that your version is vulnerable to sql injection attacks. Doesn't matter that the data you're dealing with came out of the database just milliseconds before - it's still unquoted/unescaped data and can/will blow up your insert query.

注意，您的版本容易受到sql注入攻击。不管你处理的数据是在几毫秒之前从数据库中出来的——它仍然是未被引用的/未转义的数据，并且可以/将会把你的插入查询打开。

#2

First of all if i need to move some data i do this over only SQL like @MarcB mentioned his answer but if you insist on your method i think you need to change the following line

首先，如果我需要移动一些数据，我只对SQL进行移动，比如@MarcB提到了他的答案，但是如果您坚持使用您的方法，我认为您需要更改下面的行

return $v == '' ? NULL : $v;

来

return $v == '' ? 'NULL' : $v;

With this change your array's null elements becomes php's string 'NULL' so when they implode they wont convert to '' empty string they will still remains as a string NULL so they will appear as NULL in your insert SQL

这样，数组的null元素就变成了php的字符串' null '，所以当它们内爆时，它们不会转换成"空字符串"它们仍然保持为字符串null，因此它们在insert SQL中会显示为null

#1