如何安全地将文件全路径、名称及属性导入 MariaDB 数据库-Python教程-PHP中文网

如何安全地将文件全路径、名称及属性导入 MariaDB 数据库

本文详解在 mariadb 中安全存储含 unicode 路径（如中文、emoji、空格、特殊符号）的文件元数据的最佳实践，涵盖字符集配置、字段类型选择、python 3 安全插入要点，并提供可直接落地的建表语句与代码示例。

本文详解在 mariadb 中安全存储含 unicode 路径（如中文、emoji、空格、特殊符号）的文件元数据的最佳实践，涵盖字符集配置、字段类型选择、python 3 安全插入要点，并提供可直接落地的建表语句与代码示例。

在处理海量异构文件（如 /home/test/Music/RÜFÜS DU SOL ●● Solace (Lastlings Remix) [Official Audio] -.webm 或 /home/test/Music/超好聽的中國古典音樂笛子名曲古箏 - Hermosa Música de Flauta- Música Para Meditación.mp4）时，将完整路径、文件哈希、大小、修改时间等元数据持久化到 MariaDB，必须兼顾字符安全性、存储效率与查询可靠性。核心挑战在于：Linux 文件路径支持 UTF-8 编码的任意 Unicode 字符（包括 emoji、多语言文字、空格、括号、点号、破折号等），而默认数据库配置极易导致乱码、截断或插入失败。

✅ 正确的 MariaDB 表结构设计

关键在于 字符集（charset） + 排序规则（collation） + 字段长度 的协同配置：

CREATE TABLE file_inventory (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  full_path VARCHAR(4096) COLLATE utf8mb4_unicode_ci NOT NULL,
  base_name VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  file_hash CHAR(64) NOT NULL,  -- SHA-256
  file_size BIGINT UNSIGNED NOT NULL,
  mtime DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_path (full_path(255)),     -- 前缀索引提升查询性能
  INDEX idx_hash (file_hash),          -- 支持去重与查重
  INDEX idx_mtime (mtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

VARCHAR(4096)：严格对应 Linux PATH_MAX（通常为 4096 字节），足以容纳最长绝对路径；
COLLATE utf8mb4_unicode_ci：支持完整 UTF-8（含 emoji 和四字节 Unicode），且大小写不敏感、带国际化排序逻辑（适用于常规搜索）；
若需区分大小写匹配（例如精确比对 File.txt vs file.txt），请改用 COLLATE utf8mb4_bin；
如极端追求空间节省（且应用层能保证 UTF-8 解码），可用 VARBINARY(4096) 替代 —— 此时数据库仅做二进制存储，不进行字符校验，但 Python 插入时仍需以 bytes 形式传递，开发成本略高，一般场景不推荐；
base_name VARCHAR(255) 使用 NAME_MAX（Linux 通常为 255 字节）作为上限，专用于快速提取文件名（如 os.path.basename() 结果），便于按名称聚合或模糊搜索。

⚠️ 注意：务必确认 MariaDB 服务端已启用 utf8mb4：检查 my.cnf 中包含
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
并重启服务。连接时也需显式指定字符集（见下文 Python 示例）。

✅ Python 3 安全导入实践（使用 `mysql-connector-python`）

避免 SQL 注入与编码错误的核心是：参数化查询 + 显式字符集声明 + 路径标准化：

import os
import hashlib
import mysql.connector
from pathlib import Path
from datetime import datetime

def get_file_hash(filepath: Path, algo='sha256') -> str:
    h = hashlib.new(algo)
    with filepath.open('rb') as f:
        for chunk in iter(lambda: f.read(8192), b''):
            h.update(chunk)
    return h.hexdigest()

# 安全连接（强制 utf8mb4）
conn = mysql.connector.connect(
    host='localhost',
    user='your_user',
    password='your_pass',
    database='your_db',
    charset='utf8mb4',  # ← 关键！确保客户端通信使用 utf8mb4
    autocommit=False
)
cursor = conn.cursor()

# 批量插入示例（建议每 1000 条 commit 一次）
files_to_insert = []
for root, _, files in os.walk("/home/test/Music", topdown=True):
    for fname in files:
        fp = Path(root) / fname
        try:
            stat = fp.stat()
            files_to_insert.append((
                str(fp.resolve()),                    # full_path (abs, normalized)
                fname,                                # base_name
                get_file_hash(fp),                    # SHA-256 hash
                stat.st_size,                         # size in bytes
                datetime.fromtimestamp(stat.st_mtime) # mtime
            ))
        except (OSError, FileNotFoundError):
            continue  # 跳过权限不足或已删除文件

# 参数化插入（自动转义，杜绝注入）
cursor.executemany(
    "INSERT INTO file_inventory (full_path, base_name, file_hash, file_size, mtime) "
    "VALUES (%s, %s, %s, %s, %s)",
    files_to_insert
)
conn.commit()
cursor.close()
conn.close()

str(fp.resolve()) 确保路径为规范绝对路径，消除 .. 或软链接歧义；
charset='utf8mb4' 在连接中声明，使 connector 自动处理 Python str → MySQL utf8mb4 的编码映射；
executemany() 配合参数化占位符 %s，彻底规避字符串拼接风险；
对于 400 万+ 文件，务必分批提交（如 commit() 每 1000 行），防止事务日志过大或锁表。

✅ 性能与运维建议

索引策略：full_path 字段过长，不宜全字段索引，INDEX idx_path (full_path(255)) 前缀索引可支撑路径前缀搜索（如 WHERE full_path LIKE '/home/test/Music/%'）；
去重逻辑：利用 file_hash 建唯一索引或 INSERT IGNORE 实现“存在则跳过”，天然支持重复文件识别；
增量扫描：首次全量后，后续可用 find /path -newer /tmp/last_scan_time -print0 快速获取变更文件，大幅降低 I/O 压力；
备份考虑：utf8mb4 表导出时需加 --default-character-set=utf8mb4 参数，否则 mysqldump 可能降级为 utf8 导致恢复异常。