pbootcms数据库SQLite转MySQL迁移报错

在将pbootcms的数据库从SQLite迁移到MySQL时，可能会遇到各种报错问题。解决方案，并提供详细的代码和多种解决思路，帮助您顺利完成迁移。

解决方案：
1. 检查并修改字段类型以适配MySQL。
2. 转换SQLite语法到MySQL兼容的语法。
3. 使用工具或脚本批量处理数据迁移。
4. 确保字符集和编码一致。

1. 字段类型不兼容问题

SQLite和MySQL的字段类型存在差异。例如，SQLite中的INTEGER PRIMARY KEY AUTOINCREMENT在MySQL中需要改为INT AUTO_INCREMENT PRIMARY KEY。

解决方法：
- 手动调整表结构。
- 编写脚本来自动化调整。

以下是一个示例SQL脚本，用于将SQLite的表结构调整为MySQL兼容：

sql
-- SQLite原表结构
CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    content TEXT,
    created<em>at TIMESTAMP DEFAULT CURRENT</em>TIMESTAMP
);</p>

<p>-- 转换为MySQL表结构
CREATE TABLE articles (
    id INT AUTO<em>INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content LONGTEXT,
    created</em>at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. 语法转换问题

SQLite和MySQL的SQL语法有所不同，例如日期函数、字符串拼接等。

常见问题及解决方法：

日期函数：
- SQLite使用strftime('%Y-%m-%d %H:%M:%S', created_at)。
- MySQL使用DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')。
字符串拼接：
- SQLite使用||。
- MySQL使用CONCAT()。

示例代码：

sql
-- SQLite查询
SELECT id, title || ' - ' || content AS full_content FROM articles;</p>

<p>-- 转换为MySQL查询
SELECT id, CONCAT(title, ' - ', content) AS full_content FROM articles;

3. 使用工具进行数据迁移

手动调整SQL语句可能耗时且容易出错，可以借助工具来简化迁移过程。

推荐工具：
- phpMyAdmin： 支持导入SQLite文件并导出为MySQL格式。
- DBConvert： 专业的数据库迁移工具，支持SQLite到MySQL的直接转换。
- Python脚本： 自定义脚本实现数据提取和插入。

Python脚本示例：

python
import sqlite3
import mysql.connector</p>

<h1>连接SQLite数据库</h1>

<p>sqlite<em>conn = sqlite3.connect('pbootcms.db')
sqlite</em>cursor = sqlite_conn.cursor()</p>

<h1>连接MySQL数据库</h1>

<p>mysql<em>conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="pbootcms"
)
mysql</em>cursor = mysql_conn.cursor()</p>

<h1>获取SQLite表名</h1>

<p>sqlite<em>cursor.execute("SELECT name FROM sqlite</em>master WHERE type='table';")
tables = sqlite_cursor.fetchall()</p>

<p>for table in tables:
    table_name = table[0]</p>

<pre><code># 获取表结构
sqlite_cursor.execute(f"PRAGMA table_info({table_name});")
columns = [col[1] for col in sqlite_cursor.fetchall()]

# 构造INSERT语句
placeholders = ', '.join(['%s'] * len(columns))
insert_sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"

# 获取数据并插入MySQL
sqlite_cursor.execute(f"SELECT * FROM {table_name}")
rows = sqlite_cursor.fetchall()
for row in rows:
    mysql_cursor.execute(insert_sql, row)

提交事务并关闭连接

mysqlconn.commit()
sqliteconn.close()
mysql_conn.close()

4. 字符集和编码问题

SQLite默认使用UTF-8编码，而MySQL可能使用不同的字符集（如latin1）。如果字符集不一致，可能导致乱码问题。

解决方法：
- 在创建MySQL数据库时指定字符集为UTF-8。
- 使用ALTER TABLE命令修改现有表的字符集。

示例代码：

sql
-- 创建MySQL数据库时指定字符集
CREATE DATABASE pbootcms CHARACTER SET utf8mb4 COLLATE utf8mb4<em>unicode</em>ci;</p>

<p>-- 修改现有表的字符集
ALTER TABLE articles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4<em>unicode</em>ci;