pbootcms sqlite database converted to mysql database

Why convert to a MySQL database?

When the amount of data in a database reaches the tens of thousands level, using SQLite versus MySQL presents the following differences:

1. Performance: SQLite is an embedded database suitable for lightweight applications; it stores data on disk files and supports only a single connection. In contrast, MySQL is a full-featured client/server relational database management system that supports multiple connections and offers higher performance and scalability.

2. Concurrent access: When multiple users access the database simultaneously, SQLite may encounter locking issues, which can lead to performance degradation. MySQL uses row-level locking and read/write locking to handle concurrent access, thereby offering better performance and scalability.

3. Data security: SQLite poses relatively low security risks, as it does not support multi-user access; all access is handled by a single process. MySQL offers more robust security features, including user authentication, access control, and data encryption.

4. Database capacity: SQLite has a limited database capacity and is typically suitable for small-scale applications. In contrast, MySQL can handle large datasets, making it more suitable for large-scale applications.

5. Scalability: MySQL is a database with strong scalability, enabling the use of technologies such as sharding to handle large-scale datasets; SQLite does not possess such scalability.

In conclusion, when the data volume reaches the tens of thousands level, MySQL is typically the better choice, as it offers superior performance, enhanced concurrent access capability, robust data security, and excellent scalability.

How to choose between the two databases?

SQLite and MySQL are two different types of database management systems, each suited for specific use cases.

SQLite：

1. Lightweight applications: SQLite is an embedded database ideal for lightweight applications; it can be easily integrated into applications without requiring a separate database server.

2. Single-user applications: SQLite does not support multi-user access; all database operations are performed by a single process, making it suitable for single-user applications.

3. Local storage: SQLite uses disk files to store data, making it suitable for local storage and standalone applications.

4. Small data volume: SQLite has a limited database capacity, making it suitable for applications handling small amounts of data.

MySQL：

1. Internet applications: MySQL is a client/server relational database management system ideal for Internet applications, capable of handling large-scale datasets and high-concurrency access.

2. Multi-user access: MySQL supports multi-user access, featuring concurrent access capability and high performance, making it ideal for applications that require multiple users to access the database simultaneously.

3. Large data volumes: MySQL can handle large-scale datasets, making it ideal for applications dealing with substantial amounts of data.

4. High data security requirements: MySQL offers robust security features—including user authentication, access control, and data encryption—making it ideal for applications with stringent data security requirements.

In conclusion, SQLite is suitable for lightweight applications, local storage, and single-user applications, whereas MySQL is ideal for web-based applications, multi-user environments, and applications handling large volumes of data.

How to convert the database for pbootcms?

pbootcms is an open-source platform used by many people—including me—yet I initially adopted it with an SQLite database. However, as my data volume grew, I realized that MySQL offered better performance, so I decided to switch my database. Unfortunately, I found that the tools available for migrating from SQLite to MySQL were rather inadequate!

I looked into several tools recommended by many online sources – such as the SQLiteStudio application or Navicat Premium – but they didn't work out well!

The primary reason is that the exported database is in poor condition; you may need to manually delete certain SQL statements from the converted SQL file, or the table structures may not have been properly converted!

Considering all the various reasons, I have decided to abandon the two methods mentioned above!

I've chosen the PHP conversion method!

The idea is very simple. Import the sql file from the official website into the database, and then clear all tables. This solves the problem of table structure when converting sqlite to mysql!

Next, I used PHP to connect the mysql and sqlite databases at the same time, convert the data in the same table, obtain the sqlite data, generate an insert statement, and insert it into mysql!

In this way, sqlite data can be converted to mysql very well, and the data is complete, without data omission or error reporting. It is faster than software conversion, and the conversion effect is better!

The converted PHP code is:

<?php

// 配置 MySQL 数据库连接信息
$mysql_host = "localhost";
$mysql_username = "24qiutv24qiutv";
$mysql_password = "cnGekNxCka8AW4d";
$mysql_database = "24qiutv24qiutv";

// 配置 SQLite 数据库连接信息
$sqlite_file = "./iq470_cn_20d3e7c4a2911eca376c399217db598e.db";

// 配置 SQL 文件路径
$sql_file = "pbootcms_v324.sql";

// 连接 MySQL 数据库
$mysqli = new mysqli($mysql_host, $mysql_username, $mysql_password, $mysql_database);

// 检查连接是否成功
if ($mysqli->connect_error) {
    die("连接 MySQL 数据库失败：" . $mysqli->connect_error);
}

// 删除 MySQL 数据库的所有表
if ($mysqli->query("SHOW TABLES") !== false) {
    $tables = $mysqli->query("SHOW TABLES")->fetch_all();
    foreach ($tables as $table) {
        $table_name = $table[0];
        $mysqli->query("DROP TABLE $table_name");
    }
}

// 导入指定的 SQL 文件到 MySQL 数据库
$sql_content = file_get_contents($sql_file);
if ($mysqli->multi_query($sql_content) !== false) {
    do {
        $mysqli->store_result();
    } while ($mysqli->more_results() && $mysqli->next_result());
}

// 清空 MySQL 数据库的所有表数据
if ($mysqli->query("SHOW TABLES") !== false) {
    $tables = $mysqli->query("SHOW TABLES")->fetch_all();
    foreach ($tables as $table) {
        $table_name = $table[0];
        $mysqli->query("DELETE FROM $table_name");
    }
}

// 连接 SQLite 数据库
try {
    $pdo = new PDO("sqlite:" . $sqlite_file);
} catch (PDOException $e) {
    die("连接 SQLite 数据库失败：" . $e->getMessage());
}

// 检查连接是否成功
if (!$pdo) {
    die("连接 SQLite 数据库失败");
}

// 检查 SQLite 和 MySQL 是否有相同的表
if ($pdo->query("SELECT name FROM sqlite_master WHERE type='table'") !== false) {
    
    $tables = $pdo->query("SELECT name FROM sqlite_master WHERE type='table'")->fetchAll(PDO::FETCH_NUM);
    print_r($tables);
    
    foreach ($tables as $table) {
        $table_name = $table[0];
        if ($mysqli->query("SHOW TABLES LIKE '$table_name'")->num_rows > 0) {
            // 如果 MySQL 和 SQLite 存在相同的表，则将 SQLite 数据插入到 MySQL 对应表中
            $rows = $pdo->query("SELECT * FROM $table_name");
            while ($row = $rows->fetch(PDO::FETCH_ASSOC)) {
                $keys = implode(",", array_keys($row));
                $values = implode(",", array_map(function ($value) use ($mysqli) {
                    return "'" . $mysqli->real_escape_string($value) . "'";
                }, array_values($row)));
                $mysqli->query("INSERT INTO $table_name ($keys) VALUES ($values)");
                echo "INSERT INTO $table_name ($keys) VALUES ($values)";
            }
        }
    }
}

// 关闭数据库连接
$mysqli->close();
$pdo = null;

If you don’t understand PHP code, it is not recommended to convert. Please add the webmaster QQ for paid help!