Dapper MySQL 返回值

Question

我在ASP.net Identity项目中使用Dapper和MySql时遇到了问题。我想将用户插入到一个名为“users”的表中，并希望从此插入返回自动生成的ID。但是我得到了语法错误，不知道为什么。

这是我的插入方法代码：

public void Insert(TUser member)
        {
            var id = db.Connection.ExecuteScalar<int>(@"Insert into users
                                    (UserName,  PasswordHash, SecurityStamp,Email,EmailConfirmed,PhoneNumber,PhoneNumberConfirmed, AccessFailedCount,LockoutEnabled,LockoutEndDateUtc,TwoFactorEnabled)
                            values  (@name, @pwdHash, @SecStamp,@email,@emailconfirmed,@phonenumber,@phonenumberconfirmed,@accesscount,@lockoutenabled,@lockoutenddate,@twofactorenabled)
                            SELECT Cast(LAST_INSERT_ID() as AS UNSIGNED INTEGER)",
                              new
                              {
                                  name = member.UserName,
                                  pwdHash = member.PasswordHash,
                                  SecStamp = member.SecurityStamp,
                                  email = member.Email,
                                  emailconfirmed = member.EmailConfirmed,
                                  phonenumber = member.PhoneNumber,
                                  phonenumberconfirmed = member.PhoneNumberConfirmed,
                                  accesscount = member.AccessFailedCount,
                                  lockoutenabled = member.LockoutEnabled,
                                  lockoutenddate = member.LockoutEndDateUtc,
                                  twofactorenabled = member.TwoFactorEnabled
                              });
            // we need to set the id to the returned identity generated from the db
            member.Id = id;
        }

这是我的用户表：

CREATE TABLE `users` (
  `Id` int(36) NOT NULL,
  `Email` varchar(256) DEFAULT NULL,
  `EmailConfirmed` tinyint(1) NOT NULL,
  `PasswordHash` longtext,
  `SecurityStamp` longtext,
  `PhoneNumber` longtext,
  `PhoneNumberConfirmed` tinyint(1) NOT NULL,
  `TwoFactorEnabled` tinyint(1) NOT NULL,
  `LockoutEndDateUtc` datetime DEFAULT NULL,
  `LockoutEnabled` tinyint(1) NOT NULL,
  `AccessFailedCount` int(11) NOT NULL,
  `UserName` varchar(256) NOT NULL,
  `FirstName` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`Id`);

ALTER TABLE `users`
  MODIFY `Id` int(36) NOT NULL AUTO_INCREMENT;

错误信息：您的SQL语法有误，请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法使用方式。位于第4行附近的SELECT Cast(LAST_INSERT_ID() as AS UNSIGNED INTEGER)。

我认为LAST_INSERT_ID应该给我最后一个自动增量用户ID？

谢谢帮助。

- eldios1981

Answer 1

LAST_INSERT_ID会按照你所描述的方式工作，但是你在之前的插入语句中出现了不相关的错误，这是由于没有在语句结尾处加上分号导致的。当诊断MySQL中的语法错误时，通常在语法错误后面的错误消息中包含SQL字符串。

考虑重新组织你的语句以提高可读性，这样你就可以更容易地识别像这样的语法错误。例如：

public void Insert(TUser member)
{
    string sql = @"
        Insert into users
          (UserName, PasswordHash, SecurityStamp, Email, EmailConfirmed, PhoneNumber, PhoneNumberConfirmed, AccessFailedCount, LockoutEnabled, LockoutEndDateUtc, TwoFactorEnabled)
        values 
          (@name, @pwdHash, @SecStamp, @email, @emailconfirmed, @phonenumber, @phonenumberconfirmed, @accesscount, @lockoutenabled, @lockoutenddate, @twofactorenabled);

        select LAST_INSERT_ID();
    ";

    member.Id = db.Connection.ExecuteScalar<int>(sql, new
    {
      name = member.UserName,
      pwdHash = member.PasswordHash,
      SecStamp = member.SecurityStamp,
      email = member.Email,
      emailconfirmed = member.EmailConfirmed,
      phonenumber = member.PhoneNumber,
      phonenumberconfirmed = member.PhoneNumberConfirmed,
      accesscount = member.AccessFailedCount,
      lockoutenabled = member.LockoutEnabled,
      lockoutenddate = member.LockoutEndDateUtc,
      twofactorenabled = member.TwoFactorEnabled
    });
}

根据这个答案，如果你运行的是较新版本的MySQL，可能也可以去掉对LAST_INSERT_ID的强制转换。

- Anthony Neace