MySQL导入含特殊字符的酒店XML数据失败，如何替换特殊引号？

Hey there! Let's work through that frustrating XML-to-MySQL import problem you're dealing with—those tricky single quotes (both the straight ' and curly ’) are definitely messing with your LOAD DATA statement. Here are a couple of solid solutions to get your hotel data into the database smoothly:

Option 1: Handle Quotes Directly in the LOAD DATA Query #

You can use MySQL's REPLACE function nested twice to escape both types of quotes right in the SET clause. MySQL uses two single quotes ('') to escape a single quote in string values, so we'll convert both quote variants to this format:

LOAD DATA LOCAL INFILE '" . $rs_file_path . "' 
INTO TABLE hotels 
CHARACTER SET utf8 
LINES STARTING BY '<hotel>' TERMINATED BY '</hotel>' (@tmp) 
SET 
  hotelcode = ExtractValue(@tmp, 'hotelcode'),
  description = REPLACE(REPLACE(ExtractValue(@tmp, 'description'), '''', ''''''), '’', '''''');

Breakdown: #

• The first REPLACE takes straight single quotes (') and turns them into escaped double single quotes ('').
• The second REPLACE does the same for curly single quotes (’), making sure both variants don't break your SQL syntax.

Option 2: Preprocess the XML File with PHP #

If you prefer handling the data before hitting MySQL, you can use PHP to clean the XML file first. This is great if you have other potential formatting issues to fix too:

// Read the original XML content
$xmlContent = file_get_contents($rs_file_path);

// Replace both quote types with MySQL-compatible escaped quotes
$processedContent = str_replace(["'", "’"], ["''", "''"], $xmlContent);

// Create a temporary file for the cleaned content
$tmpFile = tempnam(sys_get_temp_dir(), 'hotel_import_');
file_put_contents($tmpFile, $processedContent);

// Run the LOAD DATA query on the cleaned temp file
$conn_1->query("LOAD DATA LOCAL INFILE '" . $tmpFile . "' 
INTO TABLE hotels 
CHARACTER SET utf8 
LINES STARTING BY '<hotel>' TERMINATED BY '</hotel>' (@tmp) 
SET 
  hotelcode = ExtractValue(@tmp, 'hotelcode'),
  description = ExtractValue(@tmp, 'description');");

// Clean up the temporary file
unlink($tmpFile);

Quick Notes: #

• Make sure your hotels table's description field is set to TEXT or a sufficiently long VARCHAR to avoid truncating the hotel details.
• Double-check that your MySQL server allows local file imports (enable local_infile=ON in your my.cnf config and ensure your connection uses this setting).

内容的提问来源于stack exchange，提问作者Note