首先说这么做应该是不对的,业务代码还是应该用Java或python写,存储过程里面应该封装一些粒度度比较细的代码块。
Sql语句仍然可以模拟一些接口的 Talk is cheap, show me the code…
一、注册/绑定账号
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_signUpOrBind`(
in p_userId bigint,
in p_username text,
in p_identityType int,
in p_identifier text,
in p_credential text)
label:begin
/*** 注册/绑定账号** @param p_userId 用户id, 0 表示注册, 非0 表示绑定* @param p_username 用户名* @param p_identityType 方式:1、phone;2、email;3、wechat;4、qq;5、weibo;* @param p_identifier 唯一标识:手机号、邮箱、第三方的oauth_id* @param p_credential 密码、第三方access_token** @use proc_signUpOrBind(@user_id, @username, @identity_type, @identifier, @credential)** 先查看先判断是否被使用,如果被使用就直接返回。* 对于新用户注册,需要操作到两个表。首先往user表里边插入user_id和username,然后再通过新增的user_id在user_auths表里边新增一条记录。* 如果是绑定账号的操作,就看用户之前是否有绑定,如果绑定了就替换,没有绑定就新增。** 暂不考虑解绑需求。** @return 结果:成功** |-------------:-----------:-----------|* | status_code | msg | option_id |* |-------------:-----------:-----------|* | 0 | 注册成功 | 100004 |* |-------------:-----------:-----------|* | 0 | 绑定成功 | 100004 |* |-------------:-----------:-----------|** @return 结果:失败** |-------------:----------------------|* | status_code | msg |* |-------------:----------------------|* | 1 | 131****1232 已经被使用 |* |-------------:----------------------|* | 2 | 用户不存在, 绑定失败 |* |-------------:----------------------|*/
declare isExisting boolean default false; -- 用户是否已存在 declare identifierWasUsed boolean default false; -- identifier 已经被使用 declare newUserId bigint; -- 新注册的用户ID
-- 看identifier 已经被使用 select count(1) >= 1 into identifierWasUsed
from user_auths
where identifier = p_identifier;
-- 如果被使用,返回错误信息并退出 if identifierWasUsed = 1 then
select 1 as status_code, concat(p_identifier,'已经被使用') as msg;
leave label; -- 退出 end if;
-- ********************************************* -- 绑定 -- ********************************************* if p_userId != 0 then
-- 如果之前已经绑定,就更换。 -- mysql bug , 如果没有找到记录,@auth_id的值是1; 那么问题来了,我正好就想更新@auth_id为1的人的信息该怎么办 ……… select @auth_id := ifnull(id, 0)
from user_auths
where user_id = p_userId and identity_type = p_identityType;
if @auth_id > 1 then
-- 替换 update user_auths
set `identifier` = p_identifier, `was_verified` = 0
where `id` = @auth_id;
-- 返回值 select 0 as status_code, '绑定成功' as msg, p_userId as option_id;
-- 退出 leave label;
end if;
-- 在数据库中查询一次,确保用户已经存在,避免出现脏数据。 select count(1) >= 1 into isExisting
from users
where id = p_userId;
if isExisting = 1 then
insert into user_auths(user_id, identity_type, identifier, credential)
values (p_userId, p_identityType, p_identifier, p_credential);
-- 返回值 select 0 as status_code, '绑定成功' as msg, p_userId as option_id;
else
-- 返回值 不要绑定一个不存在的用户 select 2 as status_code, '用户不存在, 绑定失败' as msg;
end if;
-- 退出 leave label;
end if;
-- ********************************************* -- 注册 -- *********************************************
start transaction;
insert into users(username)
values(p_username);
set newUserId = @@identity; -- 暂用
insert into user_auths(user_id, identity_type, identifier, credential)
values (newUserId, p_identityType, p_identifier, p_credential);
-- 返回值 select 0 as 'status_code', '注册成功' as msg, newUserId as option_id;
commit;
end
二、登录
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_signIn`(
in p_identityType int,
in p_identifier text,
in p_credential text
)
label:BEGIN
/*** 登录** @param p_identityType 方式:1、phone;2、email;3、wechat;4、qq;5、weibo;* @param p_identifier 唯一标识:手机号、邮箱、第三方的oauth_id* @param p_credential 密码、第三方access_token** @use proc_signIn(@identity_type, @identifier, @credential)** @return 成功** |-------------:-----------:-----------|----------|--------|---------------------|* | status_code | msg | id | username | ... | create_at |* |-------------:-----------:-----------|----------|--------|---------------------|* | 0 | 登录成功 | 100004 | 李连杰 | ... | 2020-04-23 10:34:48 |* |-------------:-----------:-----------|----------|--------|---------------------|** @return 失败** |-------------:----------------------|* | status_code | msg |* |-------------:----------------------|* | 1 | 用户名或密码错误 |* |-------------:----------------------|*/
declare userId int default 0; -- 用户ID
select user_id into userId
from user_auths
where
identity_type = p_identityType and
identifier = p_identifier and
credential = p_credential;
if userId = 0 then
select 1 as status_code, '用户名或密码错误' as msg;
leave label;
end if;
select
0 as status_code,
'登录成功' as msg,
users.*
from users
where id = userId;
END ;;
DELIMITER ;
三、获取用户拓展字段
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_getUserExtends`(
in p_userId bigint)
BEGIN
/**
* 获取用户拓展字段
*
* @param p_userId 用户id
*
* @use proc_getUserExtends(@user_id)
*
* @return 拓展字段列表
*
* |-------:------------:---------------:---------------------:------------------------:------------------------|
* | id | user_id | meta_field | meta_value | update_at | create_at |
* |-------:------------:---------------:---------------------:------------------------:------------------------|
* | 1 | 100001 | 偶像 | 孙文 | 2020-04-23 22:47:32 | 2020-04-23 22:47:32 |
* |-------:------------:---------------:---------------------:------------------------:------------------------|
* | 2 | 100001 | 最喜欢的话 | 朕就是这样的汉子 | 2020-04-23 22:47:32 | 2020-04-23 22:47:32 |
* |-------:------------:---------------:---------------------:------------------------:------------------------|
*/
select *
from user_extends
where user_extends.user_id = p_userId;
END ;;
DELIMITER ;
四、获取账号绑定信息
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_getAccountBindInfo`()
BEGIN
/**
* 获取账号绑定信息
*
* @param p_userId 用户id
*
* @use call proc_getAccountBindInfo(@user_id)
*
* @result 账号绑定情况
* |-----------:----------:----------:-----------:-------:----------|
* | user_id | phone | email | wechat | qq | weibo |
* |-----------:----------:----------:-----------:-------:----------|
* | 100002 | 1 | 1 | 0 | 0 | 0 |
* |-----------:----------:----------:-----------:-------:----------|
*
*/
select
user_id,
sum(if(identity_type = 1, 1, 0)) as phone,
sum(if(identity_type = 2, 1, 0)) as email,
sum(if(identity_type = 3, 1, 0)) as wechat,
sum(if(identity_type = 4, 1, 0)) as qq,
sum(if(identity_type = 5, 1, 0)) as weibo
from user_auths as ua
inner join users on ua.user_id = users.id and ua.user_id = p_userId
group by ua.user_id;
END ;;
DELIMITER ;
五、添加用户拓展字段
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_addUserExtends`(
in p_userId bigint,
in p_field text,
in p_value text)
BEGIN
/**
* 添加用户拓展字段
*
* @param p_userId 用户id
* @param p_field 键
* @param p_value 值
*
* @use call proc_addUserExtends(@user_id, @meta_field, @meta_value)
*
* @return 成功
*
* |----------------:-------------|
* | status_code | msg |
* |----------------:-------------|
* | 0 | 添加成功 |
* |----------------:-------------|
*
* @return 失败
*
* |---------------:---------------|
* | status_code | msg |
* |---------------:---------------|
* | 1 | 用户不存在 |
* |---------------:---------------|
*/
declare isExisting boolean default false; -- 用户是否已存在
select count(1) >= 1 into isExisting
from users
where id = p_userId;
if isExisting = 1 then
insert into user_extends (user_id, meta_field, meta_value)
values(p_userId, p_field, p_value);
-- 返回值
select 0 as status_code, '添加成功' as msg;
else
-- 返回值
select 1 as status_code, '用户不存在' as msg;
end if;
END ;;
DELIMITER ;