通过读取另一个插入数据库 [英] Inserting into database by reading another

问题描述

我有一个包含表 Y 和 Z 的数据库 X.在 Y 中，我有一个名为 W 的列.它包含用逗号分隔的数据.我想要做的是通过 Y，将 W 中的数据拆分，如果它不存在，则将其插入到 Z 中.

I have a database X which containst tables Y and Z. In Y, I have a column called W. It contains data seperated by a comma. What I want to do, is go trough Y, split data in W and insert it into Z if it doesn't exists.

我正在使用 MySQL 数据库.帮助?

I'm using MySQL database. Help?

编辑.更具体地说，我给你更多的信息.W 可以包含用逗号分隔的从零到数百的项目.我希望代码检查它们是否存在于 Z 中.如果不存在，它会将它们插入到 Z 中，每个记录在自己的记录中用逗号分隔.所以，W 中的数据 "a,b,c" 看起来就像 Z 中的 "a"、"b"、"c"，都在它们自己的记录中.

Edit. To be more specific I give you a bit more information. W can contain items seperated by a comma from zero to hundreds. I want the code to check if them exists in Z. If they doesn't it will insert them in Z, every one splitted by a comma in their own records. So, data "a,b,c" in W would look like "a", "b", "c" in Z, all in their own records.

推荐答案

这应该在你把正确的主机、用户、密码、数据库、表...

this should do it after you put the proper host,user,pass,db,tables...

<?php

$conn = new mysqli('some_host', 'some_user', 'some_pass', 'some_db');

if ($conn->connect_error) die('Connect Error (' . $mysqli->connect_errno . ') '. $mysqli->connect_error);

$zrows = array();

$result = $conn->query("SELECT W FROM Y");

if($result) while ($row = $result->fetch_object()){
    if(!empty($row->W)) foreach(explode(',',$row->W) as $v) $zrows[] = "('".$conn->real_escape_string($v)."')";

}
if(!empty($zrows)) {
    $conn->query("INSERT IGNORE INTO Z (`value`) VALUES ".implode(',',$zrows));
}

如果行太多，您可能会达到 PHP 的内存限制或 mysql 的 max_packet_size，在这种情况下，您可以一次插入几条记录

if there are too many rows you may hit PHP's memory limit or mysql's max_packet_size in which case you can insert records a few 1000's at a time

还忘了你需要在 Z

这篇关于通过读取另一个插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！