Migration Guide from MySQL to MySQLi: Core Concepts and Practical Methods

In PHP development, database operations are a core task. With the evolution of MySQL versions and updates to the PHP language, the traditional MySQL extension (mysql_* functions) has been deprecated in favor of the MySQLi extension (mysqli_* functions). This article delves into how to migrate existing code from MySQL to MySQLi, covering core concepts, migration steps, and best practices.

Advantages of the MySQLi Extension

The MySQLi extension not only supports new features in MySQL version 4.1.3 and above but also introduces several improvements. First, it supports both object-oriented and procedural programming styles, making code structure more flexible. Second, MySQLi natively supports prepared statements, which greatly enhances security by effectively preventing SQL injection attacks. Additionally, MySQLi provides more robust error handling mechanisms, including exception reporting, simplifying the debugging process.

Core Steps for Migration

The migration process primarily involves function replacement and connection management. Below is a typical MySQL code example and its corresponding MySQLi version:

// MySQL version
$link = mysql_connect($host, $user, $pass);
mysql_select_db($dbName);
$result = mysql_query($sql);
if (mysql_error()) {
    die(mysql_error());
}

// MySQLi procedural version
$link = mysqli_connect($host, $user, $pass, $dbName);
$result = mysqli_query($link, $sql);
if (mysqli_error($link)) {
    die(mysqli_error($link));
}

Key changes include: replacing mysql_connect with mysqli_connect, where the database name can be specified as the fourth parameter; replacing mysql_query with mysqli_query, which requires the connection object as the first parameter. Error handling functions are also updated to mysqli_error accordingly.

Using the Object-Oriented Interface

The object-oriented interface of MySQLi offers a clearer code structure. The following example demonstrates how to create a connection and execute a query:

$mysqli = new mysqli($host, $user, $pass, $dbName);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
$result = $mysqli->query($sql);
if (!$result) {
    die("Query error: " . $mysqli->error);
}

This approach avoids frequent passing of connection parameters, making the code easier to maintain.

Application of Prepared Statements

Prepared statements are a significant feature of MySQLi, effectively enhancing security. Here is an example using prepared statements:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Process data
}

Through parameter binding, prepared statements automatically handle data escaping, reducing the risk of SQL injection.

Error Handling and Debugging

MySQLi provides enhanced error reporting capabilities. Enabling exception mode can simplify error handling:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $mysqli = new mysqli($host, $user, $pass, $dbName);
    $result = $mysqli->query($sql);
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    // Handle exception
}

This method avoids manual checks for each database operation, improving code robustness.

Migration Tools and Considerations

For large projects, manual migration can be time-consuming and error-prone. Tools like Rector can automate parts of the migration process, handling tasks such as function renaming and parameter adjustments through configuration files. However, developers should still pay attention to details: for example, mysql_client_encoding should be replaced with mysqli_character_set_name, while some functions like mysql_list_tables are no longer supported and should be replaced with SQL statements such as SHOW TABLES.

Summary and Recommendations

Migrating to MySQLi is not only a technical upgrade but also an opportunity to improve code quality and security. Developers are advised to prioritize the object-oriented interface and prepared statements to fully utilize MySQLi's new features. During migration, code should be tested incrementally to ensure compatibility and performance. With the guidance provided in this article, developers can complete the migration more efficiently and prepare for future PHP version updates.