将大量（大约180mb）的XML数据上传到MySQL的最佳方式是什么？
问题描述：
我的XML格式是该 `将大量（大约180mb）的XML数据上传到MySQL的最佳方式是什么？
<?xml version='1.0' encoding='UTF-8'?>
<XMLResponse>
<ResponseType>HotelListResponse</ResponseType>
<RequestInfo>
<AffiliateCode>JD3622</AffiliateCode>
<AffRequestId>6</AffRequestId>
<AffRequestTime>2014-11-04T10:57:31</AffRequestTime>
</RequestInfo>
<TotalNumber>148703</TotalNumber>
<Hotels>
<Hotel>
<HotelCode>AD06PQ</HotelCode>
<OldHotelId>0</OldHotelId>
<DestinationId>AF6Z</DestinationId>
<Destination>Andorra La Vella</Destination>
<Country>Andorra</Country>
<HotelName>Domus Selecta Hotel Niunit</HotelName>
<StarRating>4</StarRating>
<HotelAddress>Ctra General Del Serrat S/n ANDORRA</HotelAddress>
<HotelPostalCode>AD300</HotelPostalCode>
<HotelPhoneNumber>+376 735735</HotelPhoneNumber>
<HotelArea></HotelArea>
<Chain></Chain>
<Coordinates>
<Latitude>42.616516</Latitude>
<Longitude>1.538348</Longitude>
</Coordinates>
<HotelImages>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229360_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229381_0x0.JPG</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229348_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229349_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229351_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229352_0x0.JPG</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229353_0x0.JPG</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229354_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229355_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD06PQ/6229356_0x0.JPG</ImageURL>
</HotelImages>
</Hotel>
<Hotel>
<HotelCode>AD0AT3</HotelCode>
<OldHotelId>0</OldHotelId>
<DestinationId>AF6Z</DestinationId>
<Destination>Andorra La Vella</Destination>
<Country>Andorra</Country>
<HotelName>HUSA MOLA PARK</HotelName>
<StarRating>3</StarRating>
<HotelAddress>Josep Viladomat, 22 - AD700 Andorra</HotelAddress>
<HotelPostalCode></HotelPostalCode>
<HotelPhoneNumber>00376 882000</HotelPhoneNumber>
<HotelArea></HotelArea>
<Chain></Chain>
<Coordinates>
<Latitude>42.511145</Latitude>
<Longitude>1.539706</Longitude>
</Coordinates>
<HotelImages>
<ImageURL>http://image.metglobal.com/hotelimages/AD0AT3/5864892_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD0AT3/5864893_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD0AT3/5864894_0x0.jpg</ImageURL>
<ImageURL>http://image.metglobal.com/hotelimages/AD0AT3/5864895_0x0.jpg</ImageURL>
</HotelImages>
</Hotel>
而且目前我使用下面的方法来做到这一点笨
function _getXML_Hotellist($fname) {
$filename = $fname.'.xml';
$xmlfile='xml/'.$filename;
$xmlRaw = file_get_contents($xmlfile);
$xmlData = $this->simplexml->xml_parse($xmlRaw);
/* database for XMLResponse */
$result0['ResponseType'] = $xmlData['ResponseType'];
$result0['AffiliateCode'] = $xmlData['RequestInfo']['AffiliateCode'];
$result0['AffRequestId'] = $xmlData['RequestInfo']['AffRequestId'];
$result0['AffRequestTime'] = $xmlData['RequestInfo']['AffRequestTime'];
$result0['TotalNumber'] = $xmlData['TotalNumber'];
/* database for Hotel List */
foreach($xmlData['Hotel'] as $row) {
$result1['HotelCode']=$this->check_empty($row['HotelCode']);
$result1['OldHotelId']= $this->check_empty($row['OldHotelId']);
if(is_array($result1['OldHotelId'])) {
$result1['OldHotelId']= $result1['OldHotelId'][0];;
} else {
$result1['OldHotelId']= $result1['OldHotelId'];
}
$result1['DestinationId']= $this->check_empty($row['DestinationId']);
$result1['Destination']= $this->check_empty($row['Destination']);
$result1['Country']= $this->check_empty($row['Country']);
$result1['HotelName']= $this->check_empty($row['HotelName']);
$result1['StarRating']= $this->check_empty($row['StarRating']);
$result1['HotelAddress']= $this->check_empty($row['HotelAddress']);
$result1['HotelPostalCode']= $this->check_empty($row['HotelPostalCode']);
$result1['HotelPhoneNumber']= $this->check_empty($row['HotelPhoneNumber']);
$result1['HotelArea']=$this->check_empty($row['HotelArea']);
$result1['Chain']=$this->check_empty($row['Chain']);
/* database for Hotels Location */
$result2['Latitude']=$this->check_empty($row['Coordinates']['Latitude']);
$result2['Longitude']=$this->check_empty($row['Coordinates']['Longitude']);
$result2['HotelCode']= $this->check_empty($row['HotelCode']);
/* database for Hotels Images */
$result4['HotelCode']= $this->check_empty($row['HotelCode']);
$result3['ImageURL']=$row['HotelImages']['ImageURL'];
if(empty($result3['ImageURL'])) {
$result4['ImageURL']="";
$this->db->insert('cf_hotel_images',$result4);
} else {
if(is_array($result3['ImageURL'])) {
foreach($result3['ImageURL'] as $row) {
$result4['ImageURL']=$row;
$this->db->insert('cf_hotel_images',$result4);
}
} else {
$result4['ImageURL']= $result3['ImageURL'];
$this->db->insert('cf_hotel_images',$result4);
}
}
/* database Queries */
$this->db->insert('cf_hotel_list',$result1);
$this->db->insert('cf_coordinates',$result2);
}
//$this->db->insert('cf_xml_response',$result0);
}
这是工作，但是，这是非常缓慢的，期满每次和也花费太多时间。所以请给我一些其他解决方案。
答
插入乘行是这样的：
function _getXML_Hotellist($fname) {
$resToInsert1 = $resToInsert2 = array();
$rowsCounter = 0;
foreach($xmlData['Hotel'] as $row) {
// fill your $result1 and $result2 arrays
$resToInsert1[] = $result1;
$resToInsert2[] = $result2;
$rowsCounter++;
if ($rowsCounter == 1000) { // for example, you can choose another number
// insert to db like a
// INSERT INTO table (col1, col2)
// VALUES (...),
// VALUES (...),
// VALUES (...);
// all data from $resToInsert1 and $resToInsert2
// and refresh vars
$resToInsert1 = $resToInsert2 = array();
$rowsCounter = 0;
}
}
}
UPD。此外，如果您不害怕丢失数据，您可以使用INSERT DELAYED
答
您应该将数据转换为文件并将其上传到某个位置。然后，不要将数据插入数据库，而是将url插入到文件中。
上传大文件不是一个好习惯。它可能会导致您的MySQL数据库中的流量很大
在CLI中执行它，而不是通过您的浏览器。你不需要codeigniter，只需PHP和PDO/mysqli以及XML simplexml（或者在这种情况下也许是XMLReader，尽管180MB并不是很大，但也许它适合这样的大小）。 – hakre 2014-11-05 23:36:01