将sql脚本转换为可重复执行语句

我们存在如下一条sql语句

INSERT INTO `jcyy_xzqh`(`int_id`, `xzqhdm`, `xzqhmc`, `xzqhjb`, `sfyx`, `pym`, `sjxzqhdm`, `sfsc`, `scsj`, `xzqhqc`, `tenant_id`) VALUES ('0000000000000110000', '110000', '北京市', '1', '1', null, null, '0', null, null, '0015707090602987520');

这条语句如果执行多次就会插入多次，如果想要每次执行前判断存在就跳过，不存在就新增，需要将脚本修改为如下格式

INSERT INTO  `jcyy_xzqh`(`int_id`, `xzqhdm`, `xzqhmc`, `xzqhjb`, `sfyx`, `pym`, `sjxzqhdm`, `sfsc`, `scsj`, `xzqhqc`, `tenant_id`) SELECT '0000000000000110000', '110000', '北京市', '1', '1', null, null, '0', null, null, '0015707090602987520' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM  `jcyy_xzqh` WHERE `int_id`='0000000000000110000' AND  `xzqhdm`= '110000');

一条语句还比较好修改，如果存在几千条数据则可以通过如下C#语句快速修改：

OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "txt,sql|*.txt;*.sql";
if (DialogResult.OK == dialog.ShowDialog())
{
    string fName = Path.Combine(
        Path.GetDirectoryName(dialog.FileName),
        Path.GetFileNameWithoutExtension(dialog.FileName) + "_temp" +
        Path.GetExtension(dialog.FileName)
    );
    using (FileStream fileStream = new FileStream(fName, FileMode.Create))
    {
        int primaryKeyCount = (int)numericUpDown1.Value;
        string columns;
        string values;
        string primaryKeys;
        string tableName;
        foreach (var line in File.ReadLines(dialog.FileName))
        {
            string txt = string.Empty;
            if (string.IsNullOrEmpty(line))
            {
                txt = "\r\n";
            }
            else
            {
                try
                {
                    var regex = Regex.Match(line, "INSERT.*INTO(.*)\\((.*)\\).*VALUES.*\\((.*)\\)", RegexOptions.IgnoreCase);
                    if (regex.Success && regex.Groups.Count == 4)
                    {
                        tableName = regex.Groups[1].Value;
                        columns = regex.Groups[2].Value;
                        values = regex.Groups[3].Value;

                        List<string> p = new List<string>();
                        for (int i = 0; i < primaryKeyCount; i++)
                        {
                            p.Add(columns.Split(',')[i] + "=" + values.Split(',')[i]);
                        }
                        primaryKeys = string.Join(" AND ", p);

                        txt = $"INSERT INTO {tableName}({columns}) SELECT {values} FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM {tableName} WHERE {primaryKeys});\r\n";
                    }
                }
                catch (Exception)
                {
                    txt = line;
                }
            }

            var bs = Encoding.UTF8.GetBytes(txt);
            fileStream.Write(bs, 0, bs.Length);
        }
    }
    MessageBox.Show("转换完成,路径:" + fName);
}