Mysql字典 插入或更新,多条件只会新增,不会更新 返回

SqlSugar
7 94

Mysql在使用 字典 插入或更新,多条件只会新增,不会更新。希望作者大大帮忙看看,感谢

Mysql 版本号8.0.27

SqlSugar包名  <PackageReference Include="SqlSugarCore" Version="5.1.3.32" />



            var temp = new Dictionary<string, object>();

             temp.Add("remark", "test");

            temp.Add("money", "11");

            myList.Add(temp);


            var temp2 = new Dictionary<string, object>();

            temp2.Add("remark", "test");

            temp.Add("money", "11");

            myList.Add(temp2);

            //指定多列会新增

            //var x = myDb.Db.Storageable(myList, $"bill_record").WhereColumns(new string[] { "remark", "money" }).ToStorage();//id作为主键

            //指定单列正确

            var x = myDb.Db.Storageable(myList, $"bill_record").WhereColumns("remark").ToStorage();//id作为主键


热忱回答7

  • WhereColumns(new string[] { "remark", "money" })  你要指定2个列作为条件,你字典要有3个列  

    0 回复
  • 条件列是不会更新的

    0 回复
  • @fate sta:作者大大您好,我这边写了个完整demo,麻烦您本地试一下。我这边还是不可以

    数据库脚本:

    -- sql 创建脚本


    CREATE TABLE `bill_record2`  (

      `Id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',

      `typeParentId` bigint NULL DEFAULT NULL COMMENT '父级ID',

      `typeId` bigint NULL DEFAULT NULL COMMENT '分类id',

      `money` decimal(10, 3) NOT NULL COMMENT '金额',

      `billTime` datetime(0) NULL DEFAULT NULL COMMENT '账单时间',

      `delFlag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '删除标志(0代表存在 2代表删除)',

      `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',

      `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

      `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',

      `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',

      `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',


      PRIMARY KEY (`Id`) USING BTREE

    ) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '账单记录表' ROW_FORMAT = DYNAMIC;


    -- 插入测试数据


    INSERT INTO `bill_record2`(`Id`, `typeParentId`, `typeId`, `money`, `billTime`, `delFlag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (1, 8, 15, 18.000, '2022-07-13 18:47:30', '0', 'alex', '2022-11-18 14:47:22', '', NULL, '午饭');


    -- 查询数据  

    SELECT * from bill_record2 WHERE remark='午饭' and money='18'




    -----------------------------------------------------------------------------------------------------------------------

    程序查询脚本:

             var myCon = "Data Source=192.168.31.101;port=3306;User ID=root;Password=123456;Database=test;sslmode=none;CharSet=utf8mb4;";

                //获取枚举值名称

                var db = new SqlSugarClient(new ConnectionConfig()

                {

                    ConnectionString = myCon,

                    DbType = DbType.MySql,

                    IsAutoCloseConnection = true,

                    InitKeyType = InitKeyType.Attribute,//从特性读取主键自增信息

                });



                //调式代码 用来打印SQL 

                db.Aop.OnLogExecuting = (sql, pars) =>

                {

                    //Console.WriteLine($" 执行语句:{ sql} \n 参数:{ db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))} \n");

                    //组合完整sql语句

                    var parsDic = pars.ToList();

                    foreach (var par in pars)

                    {

                        sql = sql.Replace(par.ParameterName, par.Value.ToSqlValue());

                    }

                    Console.WriteLine($" 执行语句:{sql} \n \n");

                };

                var myList = new List<Dictionary<string, object>>();

                var temp = new Dictionary<string, object>();

                temp.Add("typeId", "1");

                temp.Add("billTime", DateTime.Now);

                temp.Add("remark", "午饭");

                temp.Add("money", "18");

                temp.Add("beLongUserId", 1);

                temp.Add("create_time", DateTime.Now);

                temp.Add("create_by", "alex");

                myList.Add(temp);

                //数据库存在 这条数据 SELECT * from bill_record2 WHERE remark='午饭' and money='18'

                var x = db.Storageable(myList, $"bill_record2").WhereColumns(new string[] { "remark", "money" }).ToStorage();//id作为主键

                //此时还是insert 语句

                Console.WriteLine(x.DataTableGroups.First().Type);

                //var x = myDb.Db.Storageable(myList, $"bill_record").WhereColumns("remark").ToStorage();//id作为主键 


    0 回复
  • 好的 我一会对一下

    0 回复
  • 5.1.3.33-preview12

     已修复

    0 回复
  • 升级到最新预览版本 

     

    0 回复
  • @fate sta:感谢大佬!

    0 回复