Dapper MySQL return value

Question

我在 ASP.net Identity项目中使用dapper和MySql时遇到了问题。我想将用户插入到表中用户，并希望从此插入中恢复自动生成的 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？

感谢您的帮助

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上的强制转换。