sql脚本导入 MySQL手动生成sql脚本一次性导入并更新超大量数据

生成 sql 后直接在 MySQL 里用 source 执行就行了，时间还挺快的，用了一两个小时左右

新需求，单表大量更新

本来像之前那样搞好像相安无事了，但是这两天新需求就出了问题

这个项目一个主要功能是搜索后或者根据一些条件选择一些文件打包成 zip 然后让用户下载，

然后经常出现用户选择的文件没有被打包的情况，原因是数据库原表里的文件在本地并不是全都有，而用户搜索则是在原表中搜索。这个问题其实也好解决，两个表连接然后让第二个表里没有的数据排除掉就好了，但是问题出在了第二个表上，因为本地文件有些混乱，第二张表里文件统一标识的那一列每个标识并不一定唯一，总之就是太乱了，sql不好写，性能也差，八千条数据的表的多表连接还是挺恐怖的，所以打算把表改成把路径直接存到第一个表里的方法。

其实在当时（需求1）也考虑过这样的方法，但是这样作存在一些问题，因为这一个有八千万条数据的大表，修改表结构很耗费时间，而且批量的更新数据比批量插入数据要更难实现卡盟，所以选择了新建表存路径，现在就得想办法改成这个方案了。

首先是更改表结构，这个简单

打上这个命令

mysql> ALTER TABLE database.table ADD dir varchar(600) NULL;

然后等一晚上就有新字段了。

接下来的重点是如何比较效率的去批量更新这个字段了。经过本地测试，source 命令是支持执行 update 语句的，正好之前扫描文件用的 json 文件还在，那就直接生成一个 sql 里面放一行行的 update 语句就好了。

于是初步用脚本生成了一个下面结构的 sql

LOCK TABLES table WRITE;
update scimag set xxx='xxx' where xx='xx';
update scimag set xxx='xxx' where xx='xx';
update scimag set xxx='xxx' where xx='xx';
update scimag set xxx='xxx' where xx='xx';
...
update scimag set xxx='xxx' where xx='xx';
update scimag set xxx='xxx' where xx='xx';
update scimag set xxx='xxx' where xx='xx';
UNLOCK TABLES;

本来信心满满，在本地用数据量十万条的测试数据库进行了测试，但是用时让我傻眼了

一共十万条数据，居然用了小三分钟才弄完？太慢了。

于是开始研究 MySQL 怎么批量更新数据，

replace into 和 insert into…on duplicate key update 都不合适，没法用，但是找到了一个鬼才写法

update users 
	set job = case id
		when 1 then 'value1'
		when 2 then 'value2'
	end
where id in(1, 2);

这样就实现了批量的更新，试着生成用这个语法的 sql

using Newtonsoft.Json;
using System.Web;
Directory.CreateDirectory("sql");

const int updateNum = 1000;
/*
LOCK TABLES `table` WRITE;
update users 
	set job = case id
		when 1 then 'value1'
		when 2 then 'value2'
	end
where id in(1, 2);
UNLOCK TABLES;
*/
StreamWriter insertDbSql = new(new FileStream("sql/insert_db3.sql", FileMode.Create, FileAccess.Write));
try
{
    insertDbSql.Write("LOCK TABLES `scimag` WRITE;\n");
    insertDbSql.Flush();
    insertDbSql.Write("UPDATE scimag SET\n");
    insertDbSql.Write("    dir = CASE DOI\n");
    var dataFiles = new DirectoryInfo("data").GetFiles();
    List<string>? pdfList = null;
    List<string> add2 = new();
    long id = 1;
    // 文件层的循环
    for (int i = 0; i < dataFiles.Length; i++)
    {
        pdfList = JsonConvert.DeserializeObject<List<string>>(File.ReadAllText(dataFiles[i].FullName))!;
        for (int j = 0; j < pdfList.Count; j++)
        {
            var decodePDF = Path.GetFileNameWithoutExtension(pdfList[j]);
            var pdf = new FileInfo(pdfList[j]);
            var doi = pdf.Directory!.Name + "/" + HttpUtility.UrlDecode(decodePDF);
            var dir = pdfList[j];
            dir = dir.Replace(@"\", @"\\");
            insertDbSql.Write($"WHEN '{doi}' THEN '{dir}'\n");
            add2.Add(doi);
            id++;
            if (id % updateNum == 0)
            {
                insertDbSql.Write("END\n" +
                    "WHERE DOI IN (\n");
                for (int a = 0; a < add2.Count; a++)
                {
                    insertDbSql.Write($"'{add2[a]}'");
                    if (a < add2.Count - 1)
                        insertDbSql.Write($",\n");
                }
                add2.Clear();
                insertDbSql.Write(");\n");

                insertDbSql.Write("UPDATE scimag SET\n");
                insertDbSql.Write("    dir = CASE DOI\n");
                insertDbSql.Flush();
            }
            else if (i == dataFiles.Length - 1 && pdfList.Count - j - 1 < updateNum)
            {
                for (; j < pdfList.Count; j++)
                {
                    decodePDF = Path.GetFileNameWithoutExtension(pdfList[j]);
                    pdf = new FileInfo(pdfList[j]);
                    doi = pdf.Directory!.Name + "/" + HttpUtility.UrlDecode(decodePDF);
                    dir = pdfList[j];
                    dir = dir.Replace(@"\", @"\\");
                    insertDbSql.Write($"WHEN '{doi}' THEN '{dir}'\n");
                    add2.Add(doi);
                    id++;
                }
                insertDbSql.Write("END\n" +
                    "WHERE DOI IN (\n");
                for (int a = 0; a < add2.Count; a++)
                {
                    insertDbSql.Write($"'{add2[a]}'");
                    if(a < add2.Count - 1)
                        insertDbSql.Write($",\n");
                }
                
                
                add2.Clear();
                insertDbSql.Write(")");
                insertDbSql.Flush();
            }
        }
        pdfList = null;
        insertDbSql.Flush();
        Console.WriteLine("已处理" + (i + 1) + "个文件,sql 中已有" + (id - 1) + "条数据");
    }
    insertDbSql.Write(";\n");
    insertDbSql.Write("UNLOCK TABLES;\n");
    insertDbSql.Flush();
}
finally
{
    insertDbSql.Close();
}