mysql存储过程 验证登录,添加登录记录 返回主键id

 

 

建表语句

create table `t_user` (
    `id` bigint (50),
    `user_name` varchar (60),
    `pass_word` varchar (60)
); 
insert into `t_user` (`id`, `user_name`, `pass_word`) values('1','张三','abcdef');
insert into `t_user` (`id`, `user_name`, `pass_word`) values('2','李四','654321');

create table `t_login` (
    `id` bigint (20),
    `uid` bigint (20),
    `login_time` datetime 
); 
insert into `t_login` (`id`, `uid`, `login_time`) values('1','1','2019-03-15 15:03:41');
insert into `t_login` (`id`, `uid`, `login_time`) values('2','1','2019-03-11 15:03:44');
insert into `t_login` (`id`, `uid`, `login_time`) values('3','2','2019-03-12 15:03:47');
insert into `t_login` (`id`, `uid`, `login_time`) values('4','2','2019-03-10 15:03:48');
insert into `t_login` (`id`, `uid`, `login_time`) values('5','2','2019-03-16 15:03:50');
insert into `t_login` (`id`, `uid`, `login_time`) values('6','1','2019-03-13 17:38:17');
insert into `t_login` (`id`, `uid`, `login_time`) values('7','1','2019-03-13 17:47:38');
insert into `t_login` (`id`, `uid`, `login_time`) values('8','1','2019-03-13 17:48:58');
insert into `t_login` (`id`, `uid`, `login_time`) values('9','1','2019-03-13 17:48:59');
insert into `t_login` (`id`, `uid`, `login_time`) values('10','1','2019-03-13 17:49:06');
insert into `t_login` (`id`, `uid`, `login_time`) values('11','1','2019-03-13 17:53:32');
insert into `t_login` (`id`, `uid`, `login_time`) values('12','1','2019-03-13 17:56:52');
insert into `t_login` (`id`, `uid`, `login_time`) values('13','1','2019-03-13 17:57:08');
insert into `t_login` (`id`, `uid`, `login_time`) values('14','1','2019-03-14 12:55:02');
insert into `t_login` (`id`, `uid`, `login_time`) values('15','1','2019-03-14 12:56:38');

存储过程

 

USE `demo`$$

DROP PROCEDURE IF EXISTS `student_03`$$

CREATE DEFINER=`root`@`%` PROCEDURE `student_03`(IN sn VARCHAR(20),IN sc VARCHAR(20),OUT st INT)
BEGIN
DECLARE state INT DEFAULT 0; 
DECLARE sid INT DEFAULT 0;

        /*验证账号密码是否存在记录>0 into赋值给 state 需要提前定义state参数*/
     SELECT id , COUNT(1) INTO sid,  state FROM t_user WHERE user_name = sn AND pass_word =sc;
     /*if else 判断*/
     IF state > 0 THEN
     /*存在记录登录成功添加登录记录 userid 和 时间*/
     INSERT INTO t_login (uid,login_time) VALUES(sid,NOW());
     /*查询记录id赋值st 返回 ,SET st = (SELECT LAST_INSERT_ID())效果一样使用set赋值*/
      SELECT LAST_INSERT_ID() INTO st;
     ELSE
     /*<0 返回0*/
    SET st=0;
    END IF;
    END$$

DELIMITER ;

 多字段赋值遇到问题:

只能用一个into 不能下面这样写 错误

SELECT id into sid, COUNT(1) INTO  state FROM t_user WHERE user_name = sn AND pass_word =sc;

存储过程赋值方式:https://www.cnblogs.com/vincentvoid/p/6433085.html

执行结果: