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

第一个需求，新表导入大量数据

然后再用这些 json 生成 sql

internal class Program
{
    static void Main(string[] args)
    {

        Directory.CreateDirectory("sql");
        /*
        -- sci_lib.library_dir definition
        DROP TABLE IF EXISTS `library_dir`;
        CREATE TABLE `library_dir` (
          `id` bigint NOT NULL AUTO_INCREMENT,
          `doi` varchar(100) NOT NULL,
          `dir` varchar(300) NOT NULL,
          PRIMARY KEY (`id`),
          UNIQUE KEY `library_dir_doi_IDX` (`doi`) USING BTREE
        ) ENGINE=MyISAM AUTO_INCREMENT=199999 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
        LOCK TABLES `library_dir` WRITE;
        INSERT INTO `library_dir`
        (),
        ();
        UNLOCK TABLES;
        */
        StreamWriter insertDbSql = new(new FileStream("sql/insert_db.sql", FileMode.Create, FileAccess.Write));
        try
        {
            insertDbSql.Write(@"DROP TABLE IF EXISTS `library_dir`;\n");
            insertDbSql.Write("CREATE TABLE `library_dir` (\n" +
                    "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                    "  `doi` varchar(200) NOT NULL,\n" +
                    "  `dir` varchar(600) NOT NULL,\n" +
                    "  PRIMARY KEY (`id`),\n" +
                    "  KEY `library_dir_doi_IDX` (`doi`) USING BTREE\n" +
                    ") ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;\n");
            insertDbSql.Write("LOCK TABLES `library_dir` WRITE;\n");
            insertDbSql.Flush();
            insertDbSql.Write("INSERT INTO `library_dir` VALUES");
            var dataFiles = new DirectoryInfo("data").GetFiles();
            List<string>? pdfList = null;
            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($"({id},'{doi}','{dir}')");
                    id++;
                    if (id % 1000 == 0)
                    {
                        insertDbSql.Write(";\n");

                        insertDbSql.Write("INSERT INTO `library_dir` VALUES");
                        insertDbSql.Flush();
                    }
                    else if (!(i == (dataFiles.Length - 1) && j == (pdfList.Count - 1)))
                        insertDbSql.Write(",");
                }
                pdfList = null;
                insertDbSql.Flush();
                Console.WriteLine("已处理" + (i + 1) + "个文件,sql 中已有" + (id - 1) + "条数据");
            }
            insertDbSql.Write(";\n");
            insertDbSql.Write("UNLOCK TABLES;\n");
            insertDbSql.Flush();
        }
        finally
        {
            insertDbSql.Close();
        }
    }