云数据库接口地址:https://www.dbihelper.cn/linkdb | |
create table mysql.user_info( userid varchar(30) not null comment '用户主键' , usname varchar(255) null comment '用户匿名', mobile varchar(255) null comment '手机号', loginpwd varchar(255) null comment '登录密码', deviceid varchar(255) null comment '设备号', devicename varchar(255) null comment '设备名称', logintime datetime null comment '登录时间', loginnum int null comment '登录次数', loginip varchar(255) null comment '登录IP', primary key(userid) ) charset=gbk comment='用户表' | |
查询用户手机号接口,sqlid:usermobile SQL语句:select mobile from mysql.user_info where mobile='[mobile]' | |
插入短信接口(正式环境不需要),sqlid:smsinsert SQL语句:insert into mysql.sys_sms_log(logid,mobile,smscode,smstype,createtime,usems,flag,ip) values('[sys.id]','[mobile]','[smscode]','[smstype]','[sys.time]','1','y','[sys.ip]') | |
查询短信验证码接口,sqlid:smsfind SQL语句:select smscode='[smscode]' smscodeok,flag='y' smsflagok from mysql.sys_sms_log where mobile='[mobile]' and smstype='[smstype]' | |
用户短信登录接口,sqlid:usersmslogin SQL语句:select userid,mobile,deviceid,devicename,logintime from mysql.user_info where mobile='[mobile]' and 1<=(select count(1) from mysql.sys_sms_log where smstype='[smstype]' and mobile='[mobile]' and smscode='[smscode]' and flag='y'); update mysql.user_info set logintime=now(),loginnum=loginnum+1,loginip='[sys.ip]' where mobile='[mobile]' and 1<=(select count(1) from mysql.sys_sms_log where smstype='[smstype]' and mobile='[mobile]' and smscode='[smscode]' and flag='y'); update mysql.sys_sms_log set flag='n' where smstype='[smstype]' and mobile='[mobile]' and smscode='[smscode]' and flag='y'; | |
用户信息查询接口,sqlid:userselect SQL语句:select userid,usname,mobile,deviceid,devicename,logintime,loginnum,loginip from mysql.user_info where usname like? '%[usname]%' and mobilelike? '%[mobile]%' order by [orderby] | |
用户短信注册接口,sqlid:userselect insert into mysql.user_info(userid,mobile,deviceid,devicename,logintime) select * from ( select '[sys.id]','[mobile]' mobile,'[deviceid]','[devicename]','[sys.time]' ) s where mobile not in (select mobile from mysql.user_info ) and 1=(select count(1) from mysql.sys_sms_log where smstype='[smstype]' and mobile='[mobile]' and smscode='[smscode]' and flag='y'); update mysql.sys_sms_log set flag='n' where smstype='[smstype]' and mobile='[mobile]' and smscode='[smscode]' and flag='y'; | |
用户删除接口(正式环境不需要),sqlid:userdelete SQL语句:delete from mysql.user_info where mobile='[mobile]' |