TenantSqlScriptExecutor.cs
3.18 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
using System.Text;
using System.Text.RegularExpressions;
using MySqlConnector;
using SqlSugar;
namespace FoodLabeling.Application.MultiTenancy;
/// <summary>
/// 在 MySQL 租户库上执行幂等 DDL/DML 脚本（支持 PREPARE / 用户变量等多语句批次）。
/// </summary>
public static class TenantSqlScriptExecutor
{
private static readonly Regex PrepareBlockEndRegex =
new(@"DEALLOCATE\s+PREPARE\s+\w+\s*;\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline);
private static readonly Regex CreateTableEndRegex =
new(@"\)\s*ENGINE\s*=.+;\s*$", RegexOptions.IgnoreCase | RegexOptions.Compiled);
/// <summary>
/// 执行完整 SQL 脚本；仅支持 MySQL。
/// </summary>
public static async Task ExecuteMySqlScriptAsync(
string connectionString,
string script,
CancellationToken cancellationToken = default)
{
if (string.IsNullOrWhiteSpace(script))
{
return;
}
var normalizedConnectionString = EnsureMySqlScriptConnectionOptions(connectionString);
await using var connection = new MySqlConnection(normalizedConnectionString);
await connection.OpenAsync(cancellationToken);
foreach (var batch in SplitIntoBatches(script))
{
await using var command = new MySqlCommand(batch, connection);
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
/// <summary>
/// 非 MySQL 租户库跳过脚本（当前脚本均为 MySQL 方言）。
/// </summary>
public static bool Supports(DbType dbType) => dbType == DbType.MySql;
/// <summary>
/// 将脚本拆成可独立提交的批次：PREPARE 块、CREATE TABLE、UPDATE 等。
/// </summary>
internal static IReadOnlyList<string> SplitIntoBatches(string script)
{
var lines = script.Replace("\r\n", "\n").Split('\n');
var batches = new List<string>();
var current = new StringBuilder();
foreach (var rawLine in lines)
{
var line = rawLine.TrimEnd();
if (string.IsNullOrWhiteSpace(line) || line.TrimStart().StartsWith("--", StringComparison.Ordinal))
{
continue;
}
current.AppendLine(line);
var trimmed = line.Trim();
if (PrepareBlockEndRegex.IsMatch(trimmed)
|| CreateTableEndRegex.IsMatch(trimmed)
|| (trimmed.StartsWith("UPDATE ", StringComparison.OrdinalIgnoreCase) && trimmed.EndsWith(';')))
{
var batch = current.ToString().Trim();
if (!string.IsNullOrEmpty(batch))
{
batches.Add(batch);
}
current.Clear();
}
}
var tail = current.ToString().Trim();
if (!string.IsNullOrEmpty(tail))
{
batches.Add(tail);
}
return batches;
}
private static string EnsureMySqlScriptConnectionOptions(string connectionString)
{
var builder = new MySqlConnectionStringBuilder(connectionString)
{
AllowUserVariables = true
};
return builder.ConnectionString;
}
}