【My0vvn】数据库与数据处理部分问题一览和解决

2024-12-28

字数统计: 2.1k | 阅读时长≈ 9 分钟

¶写在前面 / 链接存档

在数据库创建和数据导入过程中遇到的问题和解决的过程记录

¶问题1：字符集不适配中文的情况下修改数据库的字符集

¶问题描述

成功连接到MySQL数据库，版本： 5.7.26
连接或插入数据失败： 1366 (HY000): Incorrect string value: '\xF0\x9F\x8C\x83\xEF\xBC...' for column 'answer' at row 1
MySQL连接已关闭。

怎么办？

¶解决方法：（AI提供的）

遇到“Incorrect string value”错误通常是因为MySQL数据库的字符集不支持某些特定的字符，比如一些特殊的表情符号或者四字节的UTF-8字符。根据您提供的错误信息和搜索结果，以下是一些解决方案：

修改数据库和表的字符集：
您需要将数据库和表的字符集从utf8修改为utf8mb4，因为utf8mb4支持存储四字节的UTF-8字符。以下是修改数据库和表字符集的SQL语句：

-- 修改数据库字符集
ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 修改表字符集
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

请将your_database_name和your_table_name替换为您的实际数据库名和表名。

修改列的字符集：
如果只需要修改特定列的字符集，可以使用以下SQL语句：
1
ALTER TABLE your_table_name CHANGE your_column_name your_column_name VARCHAR(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
请将your_table_name和your_column_name替换为您的实际表名和列名，并将VARCHAR(4000)设置为您需要的长度。
设置客户端连接的字符集：
在您的MySQL客户端连接字符串中，确保指定了utf8mb4字符集。例如：
1
SET NAMES 'utf8mb4';
这将告诉服务器使用utf8mb4字符集来处理客户端发送的SQL语句和服务器发送回客户端的结果。
检查并修改配置文件：
如果您有权限访问MySQL的配置文件（通常是my.ini或my.cnf），您可以在其中添加或修改以下行：
1
2
3
4
5
6
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[mysql]
default-character-set=utf8mb4
修改后，您需要重启MySQL服务以使更改生效。

请根据您的实际情况选择合适的解决方案，并在执行任何修改之前备份您的数据。这些步骤应该能够解决您遇到的“Incorrect string value”错误。

¶这种方法存在的问题

要修改的表与好几个其他表存在外键链接，通过ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;语句会被外键约束
就算暂时取消外键检查或者暂时删除外键约束，也会因为有好几个外键需要操作而导致操作变得非常复杂无法预测

¶最后实际的解决方法

在Django项目的数据库定义处加上charset: utf8mb4

然后数据导入不用source+操作的sql文件路径，而是直接打开sql文件，使用pycharm的功能直接对sql插入语句进行操作

# settings.py

DATABASES = {
    'default': {
        # 'ENGINE': 'mysql',  # 使用 MySQL 数据库
        'ENGINE': 'django.db.backends.mysql',  # 使用 MySQL 数据库
        'NAME': 'bcmp2',  # 数据库名称
        'USER': 'root',  # 数据库用户名
        'PASSWORD': 'root',  # 数据库密码
        'HOST': 'localhost',  # 数据库主机地址
        # 'PORT': '3306',  # 数据库端口
        'OPTIONS': {
            'init_command': 'SET NAMES utf8mb4',
            'charset': 'utf8mb4',
        },
    }
}

sql文件操作截图

¶问题2：从json文件中提取数据，组合成插入数据库的代码

¶情况描述

首先手头上有一个json文件，存储着所有需要存入的问答对信息，json字段格式如下：

[
    {
        "instruction": "问题1xxx",
        "input": ""，
        "output": "回答1xxx"
    },
    {
        "instruction": "问题2xxx",
        "input": ""，
        "output": "回答2xxx"
    },
    ......
]

¶解决方法描述

首先肯定不能手动cv，于是用AI写了一段提取数据然后直接插入数据库对应库表的Python代码，略改一下用起来

import json
import mysql
from mysql.connector import Error

# 从文件中读取JSON数据
def load_json_data(file_path):
    with open(file_path, 'r', encoding='utf-8') as file:
        return json.load(file)

    
    
# 连接到MySQL数据库
try:
    connection = mysql.connector.connect(
        host='localhost',       # 替换为你的数据库主机地址
        database='database_name',     # 替换为你的数据库名
        user='root',   # 替换为你的数据库用户名
        password='root' # 替换为你的数据库密码
    )
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("成功连接到MySQL数据库，版本：", db_Info)
        cursor = connection.cursor()

        
        
        # 从文件中加载JSON数据
        qa_pairs = load_json_data('scenicspot_5a.json')

        
        
        # 构建SQL插入语句
        for pair in qa_pairs:
            jq_id = ''  # 由于jq_id在JSON中没有提供，这里默认为空字符串
            question = pair['instruction']
            answer = pair['output']
            insert_stmt = """
                INSERT INTO `qa_pair` (`jq_id`, `question`, `answer`)
                VALUES (%s, %s, %s)
            """
            # 执行插入操作
            cursor.execute(insert_stmt, (jq_id, question, answer))
            connection.commit()

        print("数据插入完成。")

except Error as e:
    print("连接或插入数据失败：", e)

    
    
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL连接已关闭。")

¶运行结果

问答对插入后的数据库截图

¶存在问题

全部景点问答对插入成功，除了jq_id，还需要跟景区信息表scenic_spot进行一个对应

¶问题3：获取景点名称与`jq_id`匹配后加入`jq_id`

import mysql.connector
from mysql.connector import Error


# 连接到MySQL数据库
def connect_to_db():
    try:
        connection = mysql.connector.connect(
            host='localhost',  # 替换为你的数据库主机地址
            database='scenic_qa_management',  # 替换为你的数据库名
            user='root',  # 替换为你的数据库用户名
            password='root'  # 替换为你的数据库密码
        )
        if connection.is_connected():
            print("成功连接到MySQL数据库")
            return connection
    except Error as e:
        print("连接数据库失败：", e)
        return None

    

# 从scenic_spot表中获取jq_id
def get_jq_id(connection, scenic_name):
    cursor = connection.cursor()
    query = "SELECT jq_id FROM scenic_spot WHERE jq_name = %s"
    cursor.execute(query, (scenic_name,))
    result = cursor.fetchone()
    cursor.close()
    if result:
        return result[0]
    else:
        return None

    

# 更新qa_pair表中的jq_id
def update_qa_pair(connection, jq_id, question_id, question):
    cursor = connection.cursor()
    update_stmt = "UPDATE qa_pair SET jq_id = %s WHERE qa_id = %s"
    cursor.execute(update_stmt, (jq_id, question_id))
    connection.commit()
    cursor.close()

    

# 主函数
def main():
    connection = connect_to_db()
    if connection:
        try:
            # 景点名称列表
            scenic_names = [
                "宝塔山", "大慈恩寺（大雁塔）", "大明宫国家遗址公园", "大唐芙蓉园",
                "法门寺", "华清宫", "华山风景名胜区", "黄帝陵", "黄河壶口瀑布旅游区",
                "金丝峡景区", "赳赳大秦", "秦始皇陵博物院（兵马俑）", "太白山国家森林公园",
                "西安碑林博物馆", "西安城墙", "延安革命纪念馆", "杨家岭革命旧址",
                "枣园革命旧址", "长安十二时辰主题街区", "中共中央西北局旧址", "黄河乾坤湾景区"
            ]
            # 从qa_pair表中获取所有question
            cursor = connection.cursor()
            select_stmt = "SELECT qa_id, question FROM qa_pair"
            cursor.execute(select_stmt)
            qa_pairs = cursor.fetchall()
            cursor.close()

            for qa_pair in qa_pairs:
                question_id, question = qa_pair
                for scenic_name in scenic_names:
                    if scenic_name in question:
                        jq_id = get_jq_id(connection, scenic_name)
                        if jq_id:
                            update_qa_pair(connection, jq_id, question_id, question)
                            print(f"已更新qa_id: {question_id}, jq_id: {jq_id}, question: {question}")
                        break  # 假设每个问题只匹配一个景点
        finally:
            connection.close()
            print("MySQL连接已关闭。")


if __name__ == "__main__":
    main()

¶问题4：根据`question`内容模糊匹配景点

¶当前想法

用正则表达式，代码实现

¶具体实现

还没找到，还没实现这个功能

打算放到：问答对管理系统的搜索问答对功能以及主系统的历史记录查找

¶问题5：找到可能有用的一键导出数据insert语句进行数据备份的方法——mysqldump

¶参考链接存档：

MySQL之mysqldump的使用 - MarkLogZhu - 博客园

¶解决方案（AI提供的）

如果你想要导出整个数据库的创建语句，你可以使用mysqldump工具，它不仅可以导出表结构的创建语句，还可以导出表中的数据。以下是使用mysqldump导出整个数据库的命令：

1	mysqldump -u your_username -p your_database > database_dump.sql

请将your_username替换为你的MySQL用户名，your_database替换为你的数据库名。这个命令会将数据库的创建语句和数据导出到一个名为database_dump.sql的文件中。

请注意，使用mysqldump时，你可能需要提供密码（-p参数），或者在命令中直接包含密码（不推荐，因为这可能会在命令历史中留下密码）。如果你的数据库很大，导出过程可能需要一些时间。