代码之家 › 专栏 › 技术社区 › piojo

如何在mariadb中使用分隔符。NET MySql连接器?

mariadb .net asp.net mysql

piojo · 技术社区 · 7 年前

我正在使用MariaDB 10.2.12,并使用。NET MySQL连接器。以下触发器在MySQL Workbench中工作正常:

DELIMITER //
CREATE TRIGGER update_last_modified
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    DECLARE miscdataWithDate JSON;
    IF JSON_CONTAINS_PATH(NEW.miscdata, 'all', '$.v1.lastModified2') THEN
        SET NEW.miscdata = JSON_REPLACE(NEW.miscdata, '$.v1.lastModified2', UTC_TIMESTAMP());
    ELSE
        SET miscdataWithDate = JSON_SET('{"v1": {}}', '$.v1.lastModified2', UTC_TIMESTAMP());
        SET NEW.miscdata = JSON_MERGE(NEW.miscdata, miscdataWithDate);
    END IF;
END; //
DELIMITER ;

要从C#/。NET,我使用了以下方法。我尝试了使用和不使用最后的分号,以防库添加分号:

using (var cmd = new MySqlCommand(@"CREATE TRIGGER update_last_modified
    BEFORE INSERT ON users
    FOR EACH ROW
    BEGIN
        DECLARE miscdataWithDate JSON;
        IF JSON_CONTAINS_PATH(NEW.miscdata, 'all', '$.v1.lastModified') THEN
            SET NEW.miscdata = JSON_REPLACE(NEW.miscdata, '$.v1.lastModified', UTC_TIMESTAMP());
        ELSE
            SET miscdataWithDate = JSON_SET('{""v1"": {}}', '$.v1.lastModified', UTC_TIMESTAMP());
            SET NEW.miscdata = JSON_MERGE(NEW.miscdata, miscdataWithDate);
        END IF;
    END; //
    DELIMITER ;", connection))
{
    await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}

定义(未调用)触发器时,错误为:

Unhandled Exception: System.AggregateException: One or more errors occurred. (You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '//
    DELIMITER' at line 1) ---> MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '//
    DELIMITER' at line 1

如果我简化查询,使其不需要 DELIMITER 设置,它工作。但即使是带有自定义分隔符的非常简单的触发器也会失败。

1 回复 | 直到 7 年前

piojo 7 年前

当搜索其他人如何成功使用MySQL/MariaDB中的分隔符时。NET,我发现了以下文章: https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-mysqlscript-delimiter.html

给出的例子使用 MySqlScript 而不是 MySqlCommand 所以我相信 MySqlCommand 根本不支持分隔符。以下是更新后的代码,运行良好:

MySqlScript script = new MySqlScript(connection, @"CREATE TRIGGER update_last_modified
    BEFORE INSERT ON users
    FOR EACH ROW
    BEGIN
        DECLARE miscdataWithDate JSON;
        IF JSON_CONTAINS_PATH(NEW.miscdata, 'all', '$.v1.lastModified') THEN
            SET NEW.miscdata = JSON_REPLACE(NEW.miscdata, '$.v1.lastModified', UTC_TIMESTAMP());
        ELSE
            SET miscdataWithDate = JSON_SET('{""v1"": {}}', '$.v1.lastModified', UTC_TIMESTAMP());
            SET NEW.miscdata = JSON_MERGE(NEW.miscdata, miscdataWithDate);
        END IF;
    END; //");
script.Delimiter = "//";
await script.ExecuteAsync().ConfigureAwait(false);