8GB ZLib图书库SQL转Excel方案求助（零基础）

方案1：优化SQL导入+批量导出CSV #

先解决数据库导入慢的问题，再导出核心字段到CSV：

1. 命令行批量导入拆分的SQL文件（比GUI快数倍）

   # 替换为你的数据库账号、密码、库名
   for file in ./split_sql/*.sql; do
     mysql -u your_username -p'your_password' your_db_name < "$file"
   done
   

2. 导出核心检索字段到CSV（避免冗余数据）

   SELECT zlibrary_id, title, author, publisher, language
   INTO OUTFILE '/path/to/books_core.csv'
   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   FROM books;
   

3. 拆分CSV为Excel可处理的小文件（单文件控制在100万条以内）

   split -l 1000000 /path/to/books_core.csv ./books_part_
   

方案2：直接用Bash/JS解析SQL文件生成CSV（跳过数据库） #

利用你熟悉的Bash/JS直接处理拆分的SQL文件，无需导入数据库，效率最高：

Bash快速处理（适合无特殊字符的字段）

# 遍历拆分的SQL文件，提取zlibrary_id、title、author到CSV
# 字段位置对应建表顺序：title是第9个字段，author是第10个
for file in ./split_sql/*.sql; do
  awk -F'[(),]' '/INSERT INTO `books` VALUES/ {
    id = gensub(/["'\'']/, "", "g", $2)
    title = gensub(/["'\'']/, "", "g", $10)
    author = gensub(/["'\'']/, "", "g", $11)
    print id "," title "," author
  }' "$file" >> books_export.csv
done

Node.js严谨处理（支持带逗号/引号的字段）

const fs = require('fs');
const path = require('path');

// 创建输出流并写入表头
const output = fs.createWriteStream('./books_export.csv');
output.write('zlibrary_id,title,author\n');

// 遍历拆分的SQL文件目录
const sqlDir = './split_sql';
fs.readdirSync(sqlDir).forEach(file => {
  if (!file.endsWith('.sql')) return;
  const content = fs.readFileSync(path.join(sqlDir, file), 'utf8');
  // 匹配所有INSERT语句
  const insertRegex = /INSERT INTO `books` VALUES \((.*?)\);/g;
  let match;
  while ((match = insertRegex.exec(content)) !== null) {
    // 按逗号拆分，忽略引号内的逗号
    const fields = match[1].split(/,(?=(?:[^"]*"[^"]*")*[^"]*$)/);
    // 清理字段引号并处理CSV转义
    const cleanField = (str) => str.replace(/^["']|["']$/g, '').replace(/"/g, '""');
    const id = cleanField(fields[0]);
    const title = cleanField(fields[8]);
    const author = cleanField(fields[9]);
    // 写入CSV行
    output.write(`${id},"${title}","${author}"\n`);
  }
});

output.end(() => console.log('CSV生成完成'));

方案3：用轻量检索工具替代Excel（更实用） #

2250万条数据用Excel检索效率极低，推荐用SQLite或Node.js全文索引：

SQLite本地检索（快速搭建）

# 创建SQLite数据库并导入CSV
sqlite3 books.db "CREATE TABLE books (zlibrary_id INTEGER, title TEXT, author TEXT);"
sqlite3 books.db ".mode csv" ".import books_export.csv books"
# 创建索引加速检索
sqlite3 books.db "CREATE INDEX idx_author ON books(author);"
sqlite3 books.db "CREATE INDEX idx_title ON books(title);"

# 检索示例：查找作者包含"Orwell"的图书
sqlite3 books.db "SELECT zlibrary_id, title FROM books WHERE author LIKE '%Orwell%';"

内容的提问来源于stack exchange，提问作者Nirvahnah