English
English
When importing CSV files into MySQL databases using PHP, you often encounter some data format problems, duplicate keys, data truncation and other warnings. These warnings do not directly cause import failures, but will affect the integrity and correctness of the data. In order to accurately grasp the potential problems that arise during the import process, the mysqli::get_warnings function has become an important tool for troubleshooting and optimizing the import process.
mysqli::get_warnings is a method in the mysqli class in PHP that is used to obtain warning information generated by the latest database operation. It returns a chain of warning objects through which we can read and analyze warnings generated during database execution, such as data truncation, foreign key constraint failure, etc.
In the scenario of importing CSV, these warning messages can help us locate problem rows, adjust the CSV file or database structure in time, and avoid data exceptions.
Read the contents of the CSV file.
Parses each row of data.
Use INSERT or LOAD DATA INFILE to write data to the database.
Call mysqli::get_warnings to get warnings during execution.
Do the corresponding processing or logs based on the warning results.
The following example shows how to import CSV with PHP and use get_warnings to get warnings using PHP.
<?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");
if ($mysqli->connect_errno) {
die("Connection failed: " . $mysqli->connect_error);
}
$csvFile = fopen("data.csv", "r");
if (!$csvFile) {
die("Unable to open CSV document");
}
$lineNumber = 0;
while (($data = fgetcsv($csvFile)) !== false) {
$lineNumber++;
// Assumptions CSV There are three columns: id, name, email
$id = $mysqli->real_escape_string($data[0]);
$name = $mysqli->real_escape_string($data[1]);
$email = $mysqli->real_escape_string($data[2]);
$sql = "INSERT INTO users (id, name, email) VALUES ('$id', '$name', '$email')";
if (!$mysqli->query($sql)) {
echo "1. $lineNumber Line insertion failed: " . $mysqli->error . "\n";
} else {
// Get warning information
if ($mysqli->warning_count > 0) {
$warning = $mysqli->get_warnings();
while ($warning) {
echo "1. $lineNumber Warning: ({$warning->errno}) {$warning->message}\n";
$warning = $warning->next();
}
}
}
}
fclose($csvFile);
$mysqli->close();
?>
Data truncated for column
This indicates that the imported data length exceeds the field length, which may cause the data to be truncated. It is recommended to check whether the corresponding field length of CSV or the database field type is reasonable.
Duplicate entry
The imported data violates uniqueness constraints and may be primary key or unique index conflicts. Duplicate data needs to be checked and processed.
Incorrect datetime value
The date time format is incorrect, resulting in the insertion failure or exception. Check the corresponding field format in CSV to ensure that it complies with MySQL requirements.
With mysqli::get_warnings , we can capture and output potential warning information in real time during the import of CSV, thus achieving more detailed error diagnosis and data quality control. This not only improves the stability of data import, but also facilitates subsequent data cleaning and correction.
By properly handling these warnings, ensuring the integrity and accuracy of imported data is a key step in improving the reliability of system data.
