English
English
When connecting and manipulating MySQL databases using PHP, the mysqli extension provides rich methods such as mysqli::query , mysqli::prepare , etc. to execute SQL. However, in actual development, in addition to paying attention to whether SQL is successful, we sometimes need to obtain the execution process. These warnings may not cause SQL execution to fail, but they hide potential problems.
PHP's mysqli::get_warnings method can get all warnings generated by the last operation in the connection. However, its use is often fragmented, and if we can wrap it into a module, it can be easily reused in the project and keep the code neat.
This article will describe how to encapsulate mysqli::get_warnings into a reusable MySQL warning capture module.
The mysqli::get_warnings() method returns a reference to the linked list of mysqli_warning objects, through which all warning information can be accessed. Each mysqli_warning object contains the following information:
message : warning message
sqlstate : SQLSTATE error code
errno : MySQL error code
Our goal is to implement a WarningCollector class that is able to:
Bind to a mysqli instance;
Execute SQL statements and automatically collect warning information;
Format the warning information and return it to the log;
Provide warning cleaning methods to avoid warning information remaining to affect the next operation.
The following is the complete implementation code:
<?php
class WarningCollector
{
private mysqli $conn;
private array $warnings = [];
public function __construct(mysqli $connection)
{
$this->conn = $connection;
}
public function execute(string $sql): bool|mysqli_result
{
$result = $this->conn->query($sql);
$this->collectWarnings();
return $result;
}
private function collectWarnings(): void
{
$this->warnings = []; // Clear old data
$warning = $this->conn->get_warnings();
while ($warning) {
$this->warnings[] = [
'errno' => $warning->errno,
'sqlstate' => $warning->sqlstate,
'message' => $warning->message,
];
$warning = $warning->next();
}
}
public function getWarnings(): array
{
return $this->warnings;
}
public function hasWarnings(): bool
{
return !empty($this->warnings);
}
public function logWarnings(string $logPath = '/tmp/mysql_warnings.log'): void
{
if ($this->hasWarnings()) {
foreach ($this->warnings as $w) {
$entry = sprintf(
"[%s] MySQL Warning - Errno: %d, SQLSTATE: %s, Message: %s\n",
date('Y-m-d H:i:s'),
$w['errno'],
$w['sqlstate'],
$w['message']
);
file_put_contents($logPath, $entry, FILE_APPEND);
}
}
}
}
Using this module is very simple. Just use it to encapsulate the connection before executing SQL:
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'database');
$collector = new WarningCollector($mysqli);
// implement SQL
$sql = "INSERT INTO demo (name) VALUES ('duplicate-key')";
$collector->execute($sql);
// Check warning
if ($collector->hasWarnings()) {
$collector->logWarnings();
print_r($collector->getWarnings());
}
This encapsulation is particularly useful in the following categories of projects:
Data migration script: Warning may reveal field truncation or type mismatch;
Batch import tool: Constraint-related warnings are easily triggered when inserting large batches of data;
Data quality monitoring: centralized collection of warnings through the log system for later analysis;
If you use frameworks such as Laravel, Symfony, etc., you can also encapsulate them into middleware or service classes based on this idea for dependency injection.
mysqli::get_warnings() can only get warnings generated by the last operation, so be sure to call them immediately after each SQL execution;
If you are using prepared statements , it is recommended to ensure that you execute before getting a warning;
Different MySQL versions have slightly different levels of support for warnings, and it is recommended that the development and production environment versions are consistent;
By encapsulating mysqli::get_warnings into modules, we not only capture and handle SQL warnings more easily, but also help improve the maintainability and robustness of the system. You can further expand according to your project structure, such as adding log channel support, warning level filtering, etc.
For complete project examples, please refer to:
https://gitbox.net/example/mysql-warning-collector
