我在 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?
感谢帮助